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

      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