4 Replies Latest reply: Jan 9, 2014 2:15 AM by Arpod RSS

    SQL Developer 4.0: 'Open Declaration' bugs

    Arpod

      There are several issues I have with this function:

       

      1) Sometimes, when used, it would just "hang" the UI, loading the CPU for 50% for a few seconds.

       

      2) It doesn't seem to work for functions/procedures outside packages, you have to use 'Popup describe' instead. Is that intended?

       

      3) I don't know if it's actually related to 'Open Declaration', but Logging Page sometimes throws a pair oracle.dbtools.parser.plsql.DictionaryQuery errors, supposedly around the same time hangs occur I may be wrong on this one, but still thought it's worth reporting:

       

      Error 1:

       

      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 <= 10 and obj.object_type != 'TABLE PARTITION' and obj.object_type != 'TABLE SUBPARTITION' and obj.object_type != 'JAVA CLASS' and object_name like :object_name and object_type = :object_type 
       union all 
      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 <= 10 and obj.object_type != 'TABLE PARTITION' and obj.object_type != 'TABLE SUBPARTITION' and obj.object_type != 'JAVA CLASS' and object_name like :object_name and object_type = :object_type 
       union all 
      select * from (select text name, LINE, instr(upper(text),upper(:text)) COL, null USAGE, TYPE, OWNER, type OBJECT_TYPE, name OBJECT_NAME 
       from sys.all_source where rownum <= 10 
       and upper(text) like upper(:text1) and name not like 'BIN$%' 
       and (instr(text,'--')<=0 or instr(text,'--')>instr(upper(text),upper(:text2))) 
       and (instr(text,'/*')<=0 or instr(text,'/*')>instr(upper(text),upper(:text3)))
       and type like 'PACKAGE%' and name = :name 
      ) where col = 1 or substr(name/*text*/,col-1,1)=' '
       order by type, line and owner = :owner
      

       

      Error 2:

       

      ORA-00907: missing right parenthesis
      

       

      The 'and owner = :owner' part is obviously misplaced, causing the error.

       

       

      P.S. A bit off-topic: SQLDev gives me quite a lot of various warnings both on logging page and in console. They don't seem to break anything, but should I report them anyway? And should I create a separate thread for each group of these, if I do?

        • 1. Re: SQL Developer 4.0: 'Open Declaration' bugs
          Jeff Smith Sqldev Pm-Oracle

          Only replying to point #2

           

          That's not the intention or desired behavior - it should work for standalone procs as well.

           

          My test in v4

           

          DECLARE

            P_DEPTNO NUMBER;

            P_EMP sys_refcursor;

            P_EMP_COUNT NUMBER;

          BEGIN

            P_DEPTNO := 30;

           

           

            GET_EMPLIST_AND_COUNT(

              P_DEPTNO => P_DEPTNO,

              P_EMP => P_EMP,

              P_EMP_COUNT => P_EMP_COUNT

            );

          END;

           

          Ctrl-Mouse hover over GET_EMPLIST_AND_COUNT - hyperlinks and successfully opens that stored procedure.

           

          Can you give us an example of it not working?

           

          In my example I'm logged in as HR and this procedure is also in the HR schema.

          • 2. Re: SQL Developer 4.0: 'Open Declaration' bugs
            Arpod

            Away form work now, will provide further details tomorrow, but I forgot to mention that I used it from the context menu AND while editing package bodies (this applies to all of the points). I noticed that package pl/sql editor behaves somewhat different from worksheet sometimes, so that may be the reason.

            • 3. Re: SQL Developer 4.0: 'Open Declaration' bugs
              Arpod

              Okay, I've done some testing. It's far from exhaustive, but shows that 'Open Declaration' works differently in worksheet and package pl/sql editor, and that it depends on the case of the procedure/function that is inspected!


              I also found out the cause of ORA-00907 message.

              The cause of hangs also became somewhat possible to determine.

               

              Sorry I couldn't test it more, but I already wasted about 2 hours on it, because I did it on our test (much slower) database, and each hang lasted about half a minute. I also didn't test inter-package 'Open Declaration' behavior, because I still have work to do. Hopefully what I found will be enough.

              Also, another note is that "hangs" didn't burn the CPU while I tested it, unlike on production database.

               

              We don't have HR schema anymore, so I unlocked SCOTT/TIGER forgotten by everyone, which had no functions/packages/whatsoever, only 4 tables.

              All tests were done under 'SCOTT' user. Here's the test suite:

               

              Creating the necessary function, procedure and package:

               

              create or replace function z_test_opendecl_f return number as
              begin
                return 1;
              end z_test_opendecl_f;
              
              /
              
              create or replace procedure z_test_opendecl_proc as
              begin
                null;
              end z_test_opendecl_proc;
              
              /
              
              create or replace package z_test_opendecl_pack is
                function call_z_func return number;
                procedure call_z_proc;
              end z_test_opendecl_pack;
              
              /
              
              create or replace package body z_test_opendecl_pack is
              
                procedure call_z_proc is
                begin
                --call lowercase external proc without schema
                --popup describe works
                --open declaration throws "PL/SQL unit Z_TEST_OPENDECL_PROC does not exist" message box
                --AND (nailed it!) writes ORA-00907 message I mentioned earlier in point (3) to logging page.
                Z_TEST_OPENDECL_PROC;
              
                --call lowercase external proc without schema
                --same behavior as above
                z_test_opendecl_proc;
                
                --call mixed case external proc without schema
                --same behavior as above
                Z_Test_Opendecl_Proc;
                
                --call lowercase external proc with schema
                --popup describe works
                --open declaration hangs, then opens procedure
                scott.z_test_opendecl_proc;
              
              --call mixed case external proc with schema
                --popup describe works
                --open declaration hangs, then gives messagebox
                scott.Z_Test_Opendecl_Proc;
              
                end call_z_proc;
              
              
                function call_z_func return number is
                vRet number;
                begin
                --Same behavior as with procedures above
                vRet := z_test_opendecl_f;
                vRet := scott.z_test_opendecl_f;
                
                --Calling the procedure inside the package
                
                --without schema, uppercase
                --popup describe opens spec
                --open declaration hangs, then opens spec
                CALL_Z_PROC;
                
                --without schema, uppercase
                --popup describe opens spec
                --open declaration hangs, then opens spec
                Z_TEST_OPENDECL_PACK.CALL_Z_PROC;
                
                --with schema, uppercase
                --popup describe opens 'SCOTT' user popup
                --open declaration hangs, then opens spec
                SCOTT.Z_TEST_OPENDECL_PACK.CALL_Z_PROC;
                
                --Inline SQL, 'Open Declaration' behavior:
                select Z_TEST_OPENDECL_F --gives a messagebox, whatever the case
                into vRet
                from dual d
                where z_test_opendecl_f = 1 -- messagebox
                or scott.z_test_opendecl_f = 1 --opens declaration
                or CALL_Z_FUNC = 1 -- scrolls to function declaration
                or Z_TEST_OPENDECL_PACK.CALL_Z_FUNC = 1 --hangs, opens declaration
                or SCOTT.Z_TEST_OPENDECL_PACK.CALL_Z_FUNC = 1; --does NOTHING
                
                end call_z_func;
              
              end z_test_opendecl_pack;
              
              /
              
              

               

               

              (Comments inside package body apply to Package Body Editor).

               

              Here are the tests for worksheet:

               

              --Worksheet tests
              
              declare
                vRet number;
              begin
              
                --NO SCHEMA SPECIFIED
              
                --call the procedure
                --both popup describe and open declaration open the procedure page
                Z_TEST_OPENDECL_PROC;
              
                --call the procedure - lowercase
                --both popup describe and open declaration open the procedure page
                z_test_opendecl_proc;
              
                --call the procedure - mixed case
                --popup describe opens the page
                --open declaration shows "PL/SQL unit Z_Test_Opendecl_Proc does not exist" messagebox
                Z_Test_Opendecl_Proc;
              
                --call the function
                --both popup describe and open declaration open the function page
                vRet := Z_TEST_OPENDECL_F;
                
                --call the function - lowercase
                --both popup describe and open declaration open the function page
                vRet := z_test_opendecl_f;
              
                --call the function - mixed case
                --popup describe opens the page
                --open declaration shows "PL/SQL unit Z_TEST_opendecl_f does not exist" messagebox
                vRet := Z_TEST_opendecl_f;
              
                -- call the package procedure
                -- popup describe opens package spec
                -- open declaration hangs, then opens package spec
                Z_TEST_OPENDECL_PACK.CALL_Z_PROC;
              
                -- call the package function
                -- popup describe opens package spec
                -- open declaration hangs, then opens package spec
                vRet := Z_TEST_OPENDECL_PACK.CALL_Z_FUNC;
              
                -- call the package function - mixed case
                -- popup describe opens package spec
                -- open declaration hangs, then shows "PL/SQL unit CALL_z_func does not exist" messagebox
                vRet := Z_TEST_OPENDECL_PACK.CALL_z_func;
              
                --inline SQL
                --Shows the same behavior
                select Z_TEST_OPENDECL_F --works
                into vRet
                from dual
                where Z_TEST_OPENDECL_F = 1 -- works
                or Z_TEST_OPENDECL_PACK.CALL_Z_FUNC = 1 -- hangs, but works
                or Z_TEST_opendecl_f = 1; -- messagebox
              
              
              
                --WITH SCHEMA SPECIFIED
                --Usual calls mostly exhibit the same behavior, except all of them hang on "Open Declaration" now
              
              
                -- call the package function
                -- popup describe opens 'SCOTT' user popup (!?)
                -- open declaration hangs, then opens package spec
                vRet := SCOTT.Z_TEST_OPENDECL_PACK.CALL_Z_FUNC;
              
              
              
                --inline SQL
                --Shows the same behavior
                select SCOTT.Z_TEST_OPENDECL_F --works
                into vRet
                from dual
                where SCOTT.Z_TEST_OPENDECL_F = 1 -- works
                or SCOTT.Z_TEST_OPENDECL_PACK.CALL_Z_FUNC = 1 -- hangs, but works
                or SCOTT.Z_TEST_opendecl_f = 1; -- messagebox
              
              
              end;
              
              

               

              There was also an error on production DB I wasn't able to reproduce while testing:

              while editing package body, calling 'Open Declaration' on function name inside inline SQL did nothing - neither the message box appeared nor declaration was opened, it also doesn't hang.

              I can't disclose the names, but it looks like this:

               

              select myFunction(t.id,vLocalVar) into vLocalVar2 from tablename t;
              
              

               

              We have a lot of objects on production, maybe it times out or something, I don't know. Autocomplete also doesn't work unless I call it explicitly with ctrl+space.

               

              An assumption regarding the hangs: we have a ton of public synonyms and a lot of java objects; the hangs are probably related to poor java object querying performance discussed in neighboring topic.

              • 4. Re: SQL Developer 4.0: 'Open Declaration' bugs
                Arpod

                So, did it get tracked or not? Again, the condensed version of the above:

                 

                - When using package editor:

                • calling "Open Declaration" on external functions/procedures without schema causes  "PL/SQL unit [PROCEDURE_NAME] does not exist" messagebox and ORA-00907 in the log;
                • calling "Open Declaration" on external functions/procedures with schema specified and written in mixed case causes hang and  "PL/SQL unit [PROCEDURE_NAME] does not exist" messagebox.
                • calling "Open Declaration" on inner package function/proc, with and without package and/or schema specified, and on external func/proc with schema, causes it to hang before opening decl. Basically, it hangs on anything complex(containing 1+ dot).
                • "Open Declaration" always opens package spec rather than body, except whan called from inline SQL inside the package.
                • calling "popup describe" on "schema.package.function_or_proc" causes it to open popup for "schema" user.
                • behavior  changes to different bugs when inside inline SQL.
                • Sometimes, when called for function in inline SQL, "Open Declaration" does nothing at all.

                 

                - When using worksheet:

                • "Open Declaration" can't handle mixed case func/proc calls: shows "PL/SQL unit [PROCEDURE_NAME] does not exist" messagebox.
                • "Open Declaration" hangs on any complex name (with schema and/or package name specified)
                • calling "popup describe" on "schema.package.function_or_proc" causes it to open popup for "schema" user.