5 Replies Latest reply on Sep 9, 2020 8:51 PM by thatJeffSmith-Oracle

    PlSql Navigation with Ctrl-Click in Package

    Jason McCleskey

      Trying to make the official switch from Toad to SqlDeveloper - 20.2.  If I open a package from the database, the options for navigating around the package are very limited.  Trying to see if there's something obvious in preferences or my expectations that I'm missing.  If I'm in a procedure within a package, I would expect to be able to do some keystroke (Ctrl-Click?) that would jump to whatever I'm pointing at.  Example in below pseudo-code I'd like to be able to easily jump to procedure 123 or cursor cur_test with a ctrl-click or some other shortcut.

       

      Note - I get the message at the bottom of the screen that says "Click on an identifier with the Control key down to perform the "Go to Declaration"" but it doesn't matter how I click or hover (no mouse - laptop touchpad only) I can't get it to do the jump.

       

      Example:

      PAKAGE ABC

      PROCEDURE 123

      PROCEDURE 456

          CURSOR cur_test IS

          select something from somewhere;

      BEGIN

        FOR rec IN cur_test  -- I'd like to be able to jump from cur_test here to the actual cursor

        LOOP

                 procedure 123;  -- I'd like to  be able to jump from 123 here to the actual procedure

        END LOOP;

      END;

       

      What am I missing?  SqlDeveloper 20.2 on Oracle 12c (EBS) using Java 8 right now. 

       

      Thanks,

      Jason M

        • 1. Re: PlSql Navigation with Ctrl-Click in Package
          thatJeffSmith-Oracle

          give me some actual, working code...but i got a stub working where ctrl-click will take me to the procedure declaration, say if you called proc123 from proc456 in the body.

           

          For the cursor/other nav, you might want to look at the Code Outline (avail on a right-click in the code editor)

          • 2. Re: PlSql Navigation with Ctrl-Click in Package
            Jason McCleskey

            Thanks.  So with a piece of stubbed/dummy code the procedure level navigation works.  Example code below at the bottom of this post.  If I open that package in SqlDev I can ctrl-click on child_process in the "child_process(rec.object_name);" line and it and it does take me to the procedure.  So it's working on simple level packages.

             

            However, if I'm in a real-life more complex package that often doesn't work. Just one example from our EBS 12.2 installation is package IBY_FNDCPT_EXTRACT_GEN_PVT.  I won't attach the code but I assume you could get access to it.  We're on version 120.48.12020000.23.  Example in this screenshot show line "Insert_Into_Gt(p_mbatchid);".  If I ctrl-click there I get error that it does not exist.  But you can see from the screenshot that procedure is defined both as forward-declaration and later in package.  But ctrl-click navigation not working.

            Sql Dev IBY Pkg Navigation.jpg

             

            So the Code Outline does help some but have to hunt through it to find procedure etc..  Other tools such as Toad or Pl/Sql Developer handle this navigation fine. 

             

            Also from Code Outline perspective, if I want to navigate to sub-procedure details - such as cursor definitions within a given subprocedure - those don't even show up.  In my example code, if I wanted to navigate to the cur_random_data cursor definition by ctrl-clicking on "FOR rec IN cur_random_data" that does not work.  It highlight the word cur_random_data but does not jump to it.  And if I look at the Code Outline in either the simple or the detailed setting (shown below) the cursor definition does not show up there either.  So Code Outline doesn't help.  Am I missing some sort of config or setting for that? 

              Sql Dev Code Outline Example.jpg

             

            Example Test Package

            CREATE OR REPLACE PACKAGE jm_sqldev_test AS

              PROCEDURE main_process;

              PROCEDURE child_process (pv_object VARCHAR2);

            END;

            /

             

            create or replace PACKAGE BODY jm_sqldev_test AS

             

            -- Child Process

            PROCEDURE child_process (pv_object VARCHAR2) IS

            BEGIN

              dbms_output.put_line('Object: '||pv_object);

            END;

             

            -- Main Process

            PROCEDURE main_process IS

              CURSOR cur_random_data IS

              SELECT *

              FROM all_objects

              WHERE rownum <= 10;

            BEGIN

              FOR rec IN cur_random_data

              LOOP

                  child_process(rec.object_name);

              END LOOP;

            END;

             

            END jm_sqldev_test;

             

            Thank you!

            • 3. Re: PlSql Navigation with Ctrl-Click in Package
              Vadim Tropashko-Oracle

              Full parse tree for large package bodies can be overwhelming. This is why some branches are hidden or collapsed. Also the goal was to make the simple and the detailed outlines to look similar in appearance. Here is how to expand the procedure declaration and body and, consequently, witness the cursor:

               

              cursor.png

               

              Unfortunately, even after expanding the tree, I'm unable to search for the "cur_random_data" which seems like a bug. The second bug is that ctrl-click on "cur_random_data" usage should jump to the definition.

               

              I admit that the idea of intelligent parse tree expansion needs to be reevaluated.

              1 person found this helpful
              • 4. Re: PlSql Navigation with Ctrl-Click in Package
                Jason McCleskey

                Thanks Vadim Tropasko.  I didn't realize the expansion that the hide/filter button let me do on the parse tree. My oversight.  That does help to some extent so score one for Sql Developer on flexibility/configurability there.  Although I would note that the amount of detail you end up with on the resulting parse tree is very difficult to work with (in my opinion) in comparison to the default trees that show in other tools (Toad and Pl/Sql Developer).  I'll have to play with the options to see if I can get it down to a manageable parse tree when dealing in packages with thousands of lines. 

                 

                So what I'm seeing here are 3 possible bugs...

                1)  Can't consistently Ctrl-click within a package body to jump to other sub procedures/functions (as noted in the IBY_FNDCPT_EXTRACT_GEN_PVT example).  This makes the Code Outline much more important since can't "easily" jump to right location.

                2)  Can't use the Ctrl-Click navigation to jump to items such as cursors that are defined in a sub-procedure. 

                3)  Can't search for some things when expanding the parsing tree as you noted

                 

                This discussion at least slightly alleviates some of the nuisance when dealing with large packages.  I'll have to play around with the parse tree to see what I can make work best of mr.

                 

                Thank,

                Jason

                • 5. Re: PlSql Navigation with Ctrl-Click in Package
                  thatJeffSmith-Oracle

                  we don't have access to the ebs packages, at least no easily , but Vadim may be able to manage per his point 1/bug