4 Replies Latest reply on Aug 26, 2020 3:04 PM by Sanal Krishnan Narayanan

Popup describe or Open Declaration behaviour - "Definition not found. PL/SQL xxx unit does not exist"

Sanal Krishnan Narayanan May 19, 2020 5:50 PM

Hello,

The package & procedure is present in a different schema. There is a public synonym for the package. I am able to search for and open the package spec/body without any errors/issues, if I perform a "Find database object" or select in the connections tab.

When I am in a procedure of a different package , or when I type in the package.procedure on the worksheet, and I try the Popup describe or Open Declaration menu options, I always get the message "Definition not found. PL/SQL xxx unit does not exist".

I reviewed the log and I see this sql: I assume its trying to search for the object in my schema

select NULL ENTRY, inner.* from ( 
select null name, -1 LINE, -1 COL, null USAGE, 
case when obj.object_type like 'JAVA%' then 'JAVA' else obj.object_type end  TYPE, 
OWNER, case when obj.object_type like 'JAVA%' then 'JAVA' else obj.object_type end  OBJECT_TYPE, 
obj.OBJECT_NAME 
 from sys.Dba_OBJECTS obj where rownum <= 500 and obj.object_type != 'TABLE PARTITION' and obj.object_type != 'TABLE SUBPARTITION' and obj.object_type != 'JAVA CLASS' and object_name = :object_name  and object_type in ('PROCEDURE','FUNCTION','PACKAGE')  and owner = :owner 
 union all 
select procedure_name name, -1 LINE, -1 COL, null USAGE, object_type TYPE, OWNER, object_type OBJECT_TYPE, OBJECT_NAME 
 from sys.all_procedures where rownum < 500 and procedure_name = :procname  and object_name = :oname 
 ) inner 
where rownum <= 500
 order by case when OWNER = SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') then ' ' else OWNER end, type, CASE WHEN USAGE = 'DECLARATION' OR USAGE = 'DEFINITION' THEN 0 ELSE 1 END, usage, object_name, line, col, name

Could someone please tell me what preference I would have to change so that when I right click on a package.procedure, and choose Popup describe OR Open Declaration menu,(or even try single click) it would open the body of the procedure properly instead of throwing the error ?

Version : 19.4.0.354

Build 354.1759

1. Re: Popup describe or Open Declaration behaviour - "Definition not found. PL/SQL xxx unit does not exist"

thatJeffSmith-Oracle May 19, 2020 7:45 PM (in response to Sanal Krishnan Narayanan)

You're using the synonym instead of schema.pkg.procedure?
Like Show 0 Likes(0)

Actions

2. Re: Popup describe or Open Declaration behaviour - "Definition not found. PL/SQL xxx unit does not exist"

Sanal Krishnan Narayanan May 19, 2020 8:58 PM (in response to thatJeffSmith-Oracle)

Hello,

I tried schema.pkg.procedure, still did not help. The cursor was on the middle of the procedure name and I right click and choose these :

- Popup Describe - provides details about the Schema ( I guess details from DBA_USER)

select * from sys.Dba_users where username = :OBJECT_OWNER

- Open Declaration - Does not do anything. Below are seen in the logs:

 select 'SCHEMA' object_type , :OWNER owner , :OWNER object_name from all_users where username = :OWNER

 select object_type,owner,object_name,rank from (    select object_type,owner,object_name,0 rank    from all_objects    where object_name = :NAME    and UPPER(owner) = nvl(:OWNER,:SCHEMA_CONTEXT)    and object_type not in ( 'SYNONYM' )     union all    select ao.object_type,ao.owner,ao.object_name,decode(UPPER(syn.owner), nvl(:OWNER,:SCHEMA_CONTEXT),10,20)    from all_objects ao,all_synonyms syn    where ao.owner = syn.table_owner    and   ao.object_name = syn.table_name    and   syn.synonym_name = :NAME    and ( UPPER(syn.owner) = nvl(:OWNER,:SCHEMA_CONTEXT) 
        OR UPPER(syn.owner) = nvl(:OWNER,'PUBLIC') ) ) where ((:BODY_WHERE='Y') OR  (object_type not in ('PACKAGE BODY'))) AND rownum < 100 order by rank,  CASE object_type     WHEN 'TABLE'     THEN 0     WHEN 'VIEW'     THEN 1     WHEN 'INDEX'     THEN 2     WHEN 'PACKAGE'     THEN 3     WHEN 'PACKAGE BODY'     THEN 4     ELSE 5   END

 select UPPER(sys_context('USERENV', 'CURRENT_SCHEMA')) from dual

Thanks !

3. Re: Popup describe or Open Declaration behaviour - "Definition not found. PL/SQL xxx unit does not exist"

thatJeffSmith-Oracle May 20, 2020 2:29 AM (in response to Sanal Krishnan Narayanan)

public synonym, check
package, check

ctrl+click from a different schema, check

Does this scenario work for you?
Like Show 0 Likes(0)

Actions
4. Re: Popup describe or Open Declaration behaviour - "Definition not found. PL/SQL xxx unit does not exist"

Sanal Krishnan Narayanan Aug 26, 2020 3:04 PM (in response to Sanal Krishnan Narayanan)

Hello,

My understanding is the queries that SQL Developer executes uses USER_ or ALL_ views when attempting to open the target package.procedure (which is present in another schema). This possibly is the cause of the behaviour that I am seeing - "Definition not found. PL/SQL xxx unit does not exist".

Other tools such as SQL Navigator / and Toad provided the ability for the user to choose a setting in Features/Preferences to inform the tool to use DBA_ Views , and this would enable the queries to see the source code and "open" the package.procedure.

Would the developers of Oracle SQL Developer please consider implementing a similar feature ?
Like Show 0 Likes(0)

Actions

Go to original post