Custom Type Qualified By Wrong Schema Does Not Error Out (Doc ID 2002549.1)

Last updated on AUGUST 30, 2023

Applies to:

Goal

When using the JDBC driver 12.1.0.2, the driver does not complain about the user-defined type not found under the qualifying schema, but tries to find it via synonyms and query on all_synonyms is generated.

Consider the following example:
* USER1 creates a user-defined type <TYPE_NAME> and creates a public synonym for this type
* USER2 is granted execute privileges to this type

* A java program calls createDescriptor() for this type (the type is qualified with a schema where the object is not present; in this case <USER>>).
 
 

This results in the expensive query below being generated:

SELECT ELEM_TYPE_NAME, ELEM_TYPE_OWNER
FROM
ALL_COLL_TYPES WHERE OWNER = (SELECT DISTINCT TABLE_OWNER FROM ALL_SYNONYMS
WHERE SYNONYM_NAME=:1) AND TYPE_NAME = (SELECT DISTINCT TABLE_NAME FROM
ALL_SYNONYMS WHERE SYNONYM_NAME=:2)

Is this expected behavior?

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.