1 2 Previous Next 16 Replies Latest reply on Oct 29, 2016 2:48 AM by 1370989

    SQL Developer doesn't step into code

    user8009976

      I have a PL/SQL package in SchemaA.pkg_a, defining some global subtypes, types and modules (cursors, procedures, functions, etc.), and a package in SchemaB.pkg_b, that uses items from from SchemaA.pkg_a, and declares others of its own. Usual implementation.

      Now, package SchemaA.pkg_a calls one procedure from SchemaB.pkg_b, so they become "interdependent" (A references B, while B references A). I compiled them the usual way (for this, "interdependence" situation), first the specifications, then the bodies.

      I added debugging info for both packages (again, for specifications and bodies) and granted execute and debug privileges on SchemaB.pkg_b to SchemaA. I used to be able to debug SchemaA.pkg_a, and from it, stepping into SchemaB.pkg_b without any issues. After some extensive changes (lots of additional code) to both packages, SQL Developer stopped stepping into SchemaB.pkg_b body. It does step into its specification (some constants declaration).

       

      I debug the same packages using PL/SQL Developer (from Allroundautomations), which does step into SchemaB.pkg_b body, so I tend to isolate the issue to SQL Developer (not packages).

       

      I need to debug in SQL Developer, since the packages are using a ton of multi-dimensional collections, and no other tool (that I know) allows drilling deep down into variables of such types.

       

      Any ideas?

       

      Unfortunately, I have no code (that I could share) to reproduce the issue.

      Note: Both packages use declarations of subtypes, record types, PL/SQL tables (collections) indexed by binary_integer or by varchar2, cursors and subtypes as cursor%rowtype, some collections use those subtypes as their record types. Some functions/procedures use "in out nocopy" parameters.

        • 1. Re: SQL Developer doesn't step into code
          thatJeffSmith-Oracle

          so you can't reproduce the issue with dummy code, it's only a problem with this very specific package-combo?

           

          make sure EVERYTHING is compiled for debug

          • 2. Re: SQL Developer doesn't step into code
            user8009976

            Yes, I can't reproduce it with dummy code. And yes, everything's compiled with debug.

             

            In fact, I went ahead and removed debug info from (almost) all other PL/SQL objects in DB. I thought maybe SQL Developer is stepping through some lines from other objects, but can't display them (I've seen that happening in PL/SQL Developer, from Allroundautomations).

             

            Here's a bit more info: When I get to the line with the call to proc which SQL Developer doesn't step into, and press F7 ("Step Into"), the following buttons get grayed out (disabled): "Find Execution Point", "Step Over", "Step to End", and "Pause". The others (in debug menu, "Terminate", "Step Into", "Step Out", and "Resume") become enabled again.

            Also, the Stack (call stack) becomes empty, even though it had 2 lines in it right before I pressed F7 to "Step Into", and remains empty throughout the rest of the debugging session (if I may call it so, because I'm not debugging anymore, just keeping my finger on the F7, hoping to get somewhere ).

             

            If I hit Shift+F7 (Step Out) the code stops at the line immediately following the call to the proc, and everything's back to normal (all buttons enabled, stack has 2 lines again, etc.)

            • 3. Re: SQL Developer doesn't step into code

              Well since you can't reproduce it we can't really help you except to tell you to keep troubleshooting on your own.

               

              1. Create a SIMPLE proc/function/package in schema A and test if that can call and step into the same package proc in schema B

               

              2. Add code to the package in A to call some other proc in schema b package and see if you can step into that proc

               

              3. Try a different version that your 'unknown to us' version of sql developer and see if that makes a difference

               

              4. Try doing it on a different machine

               

              5. Check that you can step into ANY proc in ANY package in ANY schema

               

              You need to keep trying things yourself

              • 4. Re: SQL Developer doesn't step into code
                user8009976

                1 and 2. Everything works flawlessly with a simple example. Here's the sample code I've used:

                 

                create or replace

                package sch1.pkg_test_a is

                  subtype pk is number(10); -- primary key

                  procedure echo(piv_string varchar2);

                  procedure p (pin_id pk);

                end pkg_test_a;

                /

                create or replace

                package body sch1.pkg_test_a is

                  procedure echo(piv_string varchar2) is

                  begin

                    dbms_output.put_line(piv_string);

                  end;

                  procedure p(pin_id pk) is

                  begin

                    echo('proc sch1.pkg_test_a.p(' || pin_id || ')');

                  end p;

                end pkg_test_a;

                /

                 

                 

                create or replace

                package sch2.pkg_test_a is

                  subtype pk is sch1.pkg_test_a.pk; -- shortcut

                  subtype cd is varchar2(2);

                  --

                  procedure p(pin_id pk, piv_code cd);

                end pkg_test_a;

                /

                create or replace

                package body sch2.pkg_test_a is

                  procedure p(pin_id pk, piv_code cd) is

                  begin

                    -- dependency on package from other schema

                    sch1.pkg_test_a.echo('proc sch2.pkg_test_a.p(' || pin_id || ',''' || piv_code || ''')');

                    -- this call makes this package dependent on sch2.pkg_test_b, which is also dependent on this package

                    sch2.pkg_test_b.p(pin_id,piv_code);

                  end;

                end pkg_test_a;

                /

                 

                 

                create or replace

                package sch2.pkg_test_b is

                  cv_plan sch2.pkg_test_a.cd := '2A';

                  -- params of this proc make package dependent on sch2.pkg_test_a

                  procedure p(pin_id sch2.pkg_test_a.pk, piv_code sch2.pkg_test_a.cd);

                end pkg_test_b;

                /

                create or replace

                package body sch2.pkg_test_b is

                  procedure p(pin_id sch2.pkg_test_a.pk, piv_code sch2.pkg_test_a.cd) is

                  begin

                    sch1.pkg_test_a.echo('proc sch2.pkg_test_b.p(' || pin_id || ',''' || piv_code || ''')');

                  end p;

                end pkg_test_b;

                /

                 

                Here's the anonymous block (generated by SQL Developer) to debug the code:

                DECLARE

                  PIN_ID NUMBER;

                  PIV_CODE VARCHAR2(2);

                BEGIN

                  PIN_ID := 1;

                  PIV_CODE := 'A';

                  CLIENT.PKG_TEST_A.P(

                    PIN_ID => PIN_ID,

                    PIV_CODE => PIV_CODE

                  );

                --rollback;

                END;

                 

                The goal is to "step into" procedure sch2.pkg_test_b.p, which the sample code does, but our production code doesn't. In the product code I'm able to "step into" only in last package's specification, where we have declared some constants.

                 

                3. I'm using SQL Developer 4.1.3.20

                 

                4. Tried in SQL Developer 3.2.20.09 on a different machine and got the same exact behavior, the code didn't step into the desired procedure

                 

                5. Not needed, since I was able to "step into" all the way till one specific point

                • 5. Re: SQL Developer doesn't step into code
                  user8009976

                  I narrowed down the "no Step Into" reason (or what I think it is) to one particular procedure (called p_set_perm_break). There's nothing "special" about it.

                  It accepts two parameters, one is a scalar data type (or rather a subtype from another package, which is varchar2(4)), while the other one is an IN OUT NOCOPY. Its data type is a (very complicated, multi-dimensional) record type, also defined in another package. If I comment out the second (in out nocopy) parameter, Step Into starts working.

                   

                  There are 22 other procedures/functions in the same package with the same exact parameter declaration, plus one procedure with a variable of the same type, making a total of 23 references to that type. As soon as I uncomment the second parameter in procedure p_set_perm_break, Step Into stops working. Now, I have enough experience with PL/SQL to understand that such assumption is silly (to say the least), but the fact is that on the 24th reference to the record type, something breaks.

                   

                  I tried many different things:

                  - changed parameter's data type to a scalar. Step Into worked

                  - changed parameter's data type to another, less used record type (from another package). Step Into worked

                  - changed parameter's type to IN OUT (not NOCOPY). Step Into didn't work

                  - changed parameter type to IN. Step Into didn't work

                  - commented out one other procedure that uses the same type of parameter (leaving only 23 referenced left to the record type in question). Step Into worked

                   

                  I tried reproducing the problem with some "dummy" code, but couldn't. That however proved that the issue has nothing to do with 24th reference. Maybe it's the volume of references to embedded user defined types/subtypes, similar to "running out of DIANA nodes" in large PL/SQL code...

                   

                  BTW, this is happening on Oracle 10g (client's DB). I can't try it in 11g or 12c (unless they upgrade).

                   

                  • 6. Re: SQL Developer doesn't step into code
                    thatJeffSmith-Oracle

                    sounds like a potential database bug

                     

                    considering the age of your database, there's a great chance it's been fixed

                    • 7. Re: SQL Developer doesn't step into code

                      I narrowed down the "no Step Into" reason (or what I think it is) to one particular procedure (called p_set_perm_break).

                      Good progress troubleshooting. Unfortunately for problems like yours there isn't really an 'answer' we can give you.

                      There's nothing "special" about it.

                      It accepts two parameters, one is a scalar data type (or rather a subtype from another package, which is varchar2(4)), while the other one is an IN OUT NOCOPY. Its data type is a (very complicated, multi-dimensional) record type, also defined in another package. If I comment out the second (in out nocopy) parameter, Step Into starts working.

                      I would tend to disagree that there nothing 'special' about it.

                       

                      1. It uses NOCOPY - which is NOT a commonly used attribute

                       

                      2. It uses a complex, multi-dimensional record type - also NOT a commonly used datatype

                      - changed parameter's type to IN OUT (not NOCOPY). Step Into didn't work

                      Ok - but there are still more to try.

                       

                      1. Use only OUT - does that work?

                       

                      2. Remove that type as a parameter (you did that and it worked) but reference an instance from inside the code - does that work?

                       

                      3. When you use IN and it doesn't work what type of instance are you passing? Has the instance been instantiated?

                      There are 22 other procedures/functions in the same package with the same exact parameter declaration, plus one procedure with a variable of the same type, making a total of 23 references to that type. As soon as I uncomment the second parameter in procedure p_set_perm_break, Step Into stops working. Now, I have enough experience with PL/SQL to understand that such assumption is silly (to say the least), but the fact is that on the 24th reference to the record type, something breaks.

                      Silly? The only thing that would be 'silly' would be to draw conclusions before all the tests are done and the facts known.

                       

                      You are on the right track if you can reproduce it for some tests but not for others.

                       

                      The above suggests some other tests and an attempt to create a second, simpler example case.

                       

                      If it was a reference count = 24 issue then it shouldn't make any difference where the references are. But are those references only? Or are those other 23 instantiated? (that is has code run or statis declarations executed that would actually create 23 instances populated with data?).

                       

                      Are the references identical? What about the nested references?

                       

                      Create ONE new procedure and put 24 references in it and see if things work.

                       

                      The goal is to create a set of standalone code that reproduces the problem.

                       

                      It could be a privilege issue with something in that complex type. Can you post the DDL for the type?

                      • 8. Re: SQL Developer doesn't step into code
                        user8009976

                        First of all, thank you guys for staying with me during this struggle. I hope it bears fruit (otherwise the only other option for debugging would be dbms_output ). There's nothing else out there that drills down into content of variables (during debugging) like SQL Developer. My proverbial hat goes down towards the entire dev team... Truly great work!

                         

                        The type in question is a PL/SQL type defined in a package's spec, so there's no DDL (except for create or replace package...).

                        Another package (the one I'm trying to debug) declares a local variable of that type, inside a procedure, then passes it around as IN OUT NOCOPY to many other procedures (same package), where each "does things" to its content. So, technically, there's only one instance of that type (NOCOPY, at least in theory, guarantees that).

                         

                        I'll try to create a "skeleton" version of the code (declarations only, and NULL executions), as close as possible to the original. Maybe that'll reproduce the issue.

                        • 9. Re: SQL Developer doesn't step into code

                          The type in question is a PL/SQL type defined in a package's spec, so there's no DDL (except for create or replace package...).

                          Sure there is DDL. Every type MUST HAVE a declaration that specifies it in it's entirety.

                           

                          That 'complex, nested' thing you say you have is no exception.

                          Another package (the one I'm trying to debug) declares a local variable of that type, inside a procedure

                          So you say. But so far you haven't shown us the declaration of the type or the instance.

                           

                          But we can't help you if all you want to do is talk about it. We can only help you if you SHOW US:

                           

                          1. WHAT you do

                          2. HOW you do it

                          3. WHAT results you get

                          • 10. Re: SQL Developer doesn't step into code
                            user8009976

                            I finally (it only took 2 days ) created code to reproduce the issue.

                            I witnessed the same behavior (Step Into not working) in 10g (10.2.0.4.0) as well as in 12 c (12.1.0.1.0).

                             

                            I put the code at the following location (rather than pasting 100s of code lines here):

                            http://www.mediafire.com/download/z1au3blx49vuj2u/sql.developer.debug.issue.zip

                             

                            Steps to reproduce:

                            1. Run script schemas.sql to create schemas sch1 and sch2;

                            2. Run script types.sql to create some top-level types (nested tables);

                            3. Run scripts pkg_a.sql and pkg_b.sql from folder sch1;

                            4. Run script pkg_a.sql from folder sch2;

                            5. Run script pkg_b.sql from folder sch2. This will fail with compilation errors, because it depends on the package created in the next step (this could be avoided by splitting the script into spec and body, sorry)

                            6. Run script pkg_b_dtl.sql from folder sch2.

                            7. Recompile package sch2.pkg_b, created in step 5

                            8. Debug package sch2.pkg_b, procedure p_c_m_p. Accept the default anonymous block for it (1 for parameter pin_mb_id, the others are NULL) and try to step into procedure sch2.pkg_b_dtl.p_c_m_p_p.

                             

                            In my case I can step into procedure sch2.pkg_b.p_c_m_p all the way to line 54. Then Step Into doesn't work until execution exists from procedure sch2.pkg_b_dtl.p_c_m_p_p, at line 55.

                             

                            If I comment out internal procedure sch2.pkg_b_dtl.p_a (which is not even called from anywhere, but it has a reference, IN OUT NOCOPY, to that multi-dimensional type I was referring to), Step Into starts working properly all the way throughout the code.

                            • 11. Re: SQL Developer doesn't step into code

                              I finally (it only took 2 days ) created code to reproduce the issue.

                              I witnessed the same behavior (Step Into not working) in 10g (10.2.0.4.0) as well as in 12 c (12.1.0.1.0).

                               

                              I put the code at the following location (rather than pasting 100s of code lines here):

                              Ok - you're making good progress but there is still more to do.

                               

                              Now that you have a reproducible set of code reduce it to the BARE MINIMUM by removing code that can't possibly be part of the issue.

                               

                              Although possible it doesn't seem likely that you would need 100s of code lines.

                               

                              And then, since most people aren't going to download anything from an unknown site, post the code here.

                              • 12. Re: SQL Developer doesn't step into code
                                user8009976

                                Now that you have a reproducible set of code reduce it to the BARE MINIMUM by removing code that can't possibly be part of the issue.

                                ... and that's exactly what I did.

                                As I've mentioned, there have to be a certain number of referenced to the type in question, for the Step Into to stop working. Commenting out just one more procedure returns the Step Into to its normal behavior. The code IS the "bare minimum".

                                 

                                ... and here comes the code:

                                 

                                create user sch1 identified by sch1;

                                grant connect, resource, debug connect session to sch1;

                                 

                                create user sch2 identified by sch2;

                                grant connect, resource to sch2;

                                 

                                drop type sch1.schartable;

                                create type sch1.schartable as table of varchar2(4000);

                                /

                                grant execute, debug on sch1.schartable to sch2;

                                 

                                drop type sch1.snumtable;

                                create type sch1.snumtable as table of number;

                                /

                                grant execute, debug on sch1.snumtable to sch2;

                                 

                                drop type sch1.sdatetable;

                                create type sch1.sdatetable as table of date;

                                /

                                grant execute, debug on sch1.sdatetable to sch2;

                                 

                                create or replace

                                package sch1.pkg_a is

                                  subtype pk         is number(10);

                                  subtype cd         is varchar2(2);

                                  subtype yn_flag    is varchar2(1);

                                  subtype col_value  is varchar2(25);

                                  subtype be_cd      is varchar2(25);

                                  --

                                  cursor c_f is

                                    select cast(  1  as number(10))   as col1

                                         , cast('02' as varchar2(50)) as col2

                                         , cast('03' as varchar2(10)) as col3

                                      from dual;

                                  subtype r_f is c_f%rowtype;

                                end pkg_a;

                                /

                                grant execute,debug on sch1.pkg_a to sch2;

                                 

                                create or replace

                                package sch1.pkg_b is

                                  subtype pk      is sch1.pkg_a.pk;

                                  subtype yn_flag is sch1.pkg_a.yn_flag;

                                  --

                                  cursor c_mb_mc(pin_mb_id pk) is

                                    select cast(  1  as number(10))   as col1

                                         , cast(  2  as number(10))   as col2

                                         , cast(  3  as number(10))   as col3

                                         , cast(  4  as number(10))   as col4

                                         , cast('05' as varchar2(2))  as col5

                                         , cast('06' as varchar2(10)) as col6

                                         , sysdate                    as col7

                                         , sysdate                    as col8

                                         , cast('09' as varchar2(3))  as col9

                                         , cast('10' as varchar2(2))  as col10

                                         , cast('11' as varchar2(2))  as col11

                                         , sysdate                    as col12

                                         , sysdate                    as col13

                                         , cast('14' as varchar2(2))  as col14

                                         , cast('15' as varchar2(2))  as col15

                                         , cast('16' as varchar2(2))  as col16

                                         , sysdate                    as col17

                                         , sysdate                    as col18

                                         , sch1.schartable('19')       as col19

                                         , cast('20' as varchar2(25)) as col20

                                         , cast('21' as varchar2(10)) as col21

                                         , cast('22' as varchar2(25)) as col22

                                      from dual;

                                  subtype r_mb_mc is c_mb_mc%rowtype;

                                  type t_mb_mc is table of r_mb_mc index by binary_integer;

                                  --

                                  cursor c_mb_wh(pin_mb_id pk) is

                                    select cast(1 as number(10))      as col1

                                         , cast(2 as number(10))      as col2

                                         , cast(3 as number(10))      as col3

                                         , cast(4 as number(10))      as col4

                                         , cast(5 as number(10))      as col5

                                         , sysdate                    as col6

                                         , sysdate                    as col7

                                         , cast(8 as number(10))      as col8

                                         , cast('09' as varchar2(10)) as col9

                                         , cast('10' as varchar2(2))  as col10

                                         , sysdate                    as col11

                                         , sysdate                    as col12

                                         , cast('13' as varchar2(10)) as col13

                                         , cast('14' as varchar2(3))  as col14

                                         , sch1.snumtable(15)          as col15

                                         , sch1.snumtable(16)          as col16

                                         , sch1.snumtable(17)          as col17

                                         , sch1.snumtable(18)          as col18

                                         , sch1.schartable('19')       as col19

                                         , cast('20' as varchar2(25)) as col20

                                         , cast('21' as varchar2(10)) as col21

                                         , cast('22' as varchar2(25)) as col22

                                      from dual;

                                  subtype r_mb_wh is c_mb_wh%rowtype;

                                  type t_mb_wh is table of r_mb_wh index by binary_integer;

                                  --

                                  cursor c_mb_ph(pin_mb_id pk) is

                                    select cast(1 as number(10))        as col1

                                         , cast(2 as number(9))         as col2

                                         , cast(3 as number(10))        as col3

                                         , cast(4 as number(10))        as col4

                                         , cast(5 as number(10))        as col5

                                         , cast(6 as number(10))        as col6

                                         , cast('07' as varchar2(4000)) as col7

                                         , sch1.schartable('08')         as col8

                                         , cast('9' as char(1))         as col9

                                         , sch1.schartable('10')         as col10

                                      from dual;

                                  subtype r_mb_ph is c_mb_ph%rowtype;

                                  type t_mb_ph is table of r_mb_ph index by binary_integer;

                                  --

                                  cursor c_mb_p_p(pin_mb_id pk) is

                                    select cast(1 as number(10))        as col1

                                         , cast(2 as number(10))        as col2

                                         , cast(3 as number(10))        as col3

                                         , cast(4 as number(10))        as col4

                                         , cast(5 as number(10))        as col5

                                         , sysdate                      as col6

                                         , cast('7' as char(1))         as col7

                                         , sysdate                      as col8

                                         , cast('9' as char(1))         as col9

                                         , cast(10 as number(12,6))     as col10

                                         , cast('1' as char(1))         as col11

                                         , cast(12 as number(12,6))     as col12

                                         , cast('3' as char(1))         as col13

                                         , sysdate                      as col14

                                         , cast('5' as char(1))         as col15

                                         , sysdate                      as col16

                                         , cast('7' as char(1))         as col17

                                         , cast('8' as char(1))         as col18

                                         , cast(19 as number(10))       as col19

                                         , sch1.sdatetable(sysdate)      as col20

                                         , sch1.snumtable(21)            as col21

                                         , sch1.schartable('22')         as col22

                                      from dual;

                                  subtype r_mb_p_p is c_mb_p_p%rowtype;

                                  type t_mb_p_p is table of r_mb_p_p index by binary_integer;

                                  --

                                  cursor c_p_a_c(pin_mb_id pk) is

                                    select cast(1 as number(10))        as col1

                                         , cast(2 as number(10))        as col2

                                         , cast(3 as number(10))        as col3

                                         , cast(4 as number(5))         as col4

                                         , cast('5' as char(1))         as col5

                                         , cast('6' as varchar2(4000))  as col6

                                         , sch1.sdatetable(sysdate)      as col7

                                         , sch1.schartable('08')         as col8

                                         , sch1.schartable('09')         as col9

                                         , sch1.snumtable(10)            as col10

                                         , sch1.schartable('1')          as col11

                                      from dual;

                                  subtype r_p_a_c is c_p_a_c%rowtype;

                                  type t_p_a_c is table of r_p_a_c index by binary_integer;

                                  --

                                  cursor c_mb_pa(pin_mb_id pk) is

                                    select cast(1 as number(10))        as col1

                                         , cast('2' as varchar2(2))     as col2

                                         , cast(3 as number(10))        as col3

                                         , sysdate                      as col4

                                         , cast('5' as varchar2(25))    as col5

                                         , cast('6' as char(1))         as col6

                                         , cast('7' as varchar2(250))   as col7

                                         , sysdate                      as col8

                                         , cast('9' as char(1))         as col9

                                         , cast('10' as varchar2(25))   as col10

                                      from dual;

                                  subtype r_mb_pa is c_mb_pa%rowtype;

                                  type t_mb_pa is table of r_mb_pa index by binary_integer;

                                  --

                                  cursor c_mb_d(pin_mb_id pk) is

                                    select cast(1 as number(10))        as col1

                                         , cast(2 as number(10))        as col2

                                         , cast('3' as varchar2(10))    as col3

                                         , cast('4' as varchar2(50))    as col4

                                         , cast('5' as varchar2(10))    as col5

                                         , sysdate                      as col6

                                         , sysdate                      as col7

                                         , cast('8' as varchar2(2))     as col8

                                      from dual;

                                  subtype r_mb_d is c_mb_d%rowtype;

                                  --

                                  type r_mb_dtl is record

                                    (d   r_mb_d

                                    ,mc  t_mb_mc

                                    ,wh  t_mb_wh

                                    ,ph  t_mb_ph

                                    ,p_p t_mb_p_p

                                    ,a_c t_p_a_c

                                    ,pa  t_mb_pa

                                    );

                                end pkg_b;

                                /

                                grant execute,debug on sch1.pkg_b to sch2;

                                 

                                create or replace

                                package sch2.pkg_a is

                                  subtype pk is sch1.pkg_a.pk;

                                  subtype cd is sch1.pkg_a.cd;

                                  --

                                  cursor c_pd

                                         (pin_b_u_id pk

                                         ,pin_e_id   pk

                                         ,pin_b_id   pk

                                         )

                                         is

                                    select sysdate                    as col2

                                         , sysdate                    as col3

                                         , cast('4' as varchar2(100)) as col4

                                         , cast('5' as varchar2(100)) as col5

                                         , cast('6' as varchar2(100)) as col6

                                         , cast(1 as number(10))      as col1

                                       from dual;

                                  subtype r_pd is c_pd%rowtype;

                                  type t_pd is table of r_pd index by binary_integer;

                                end pkg_a;

                                /

                                grant execute,debug on sch2.pkg_a to sch1;

                                 

                                create or replace

                                package sch2.pkg_b is

                                  subtype pk       is sch2.pkg_a.pk;

                                  subtype cd       is sch2.pkg_a.cd;

                                  subtype be_cd    is sch1.pkg_a.be_cd;

                                  subtype w_g_t    is varchar2(10);

                                  subtype m_idx    is varchar2(6);

                                  subtype y_idx    is varchar2(4);

                                  subtype p_ph_idx is varchar2(32);

                                  subtype b_t      is cd;

                                  --

                                  type r_mc_p is record

                                    (start_dt date

                                    ,stop_dt  date

                                    ,pd       sch2.pkg_a.t_pd

                                    );

                                  type t_mc_p is table of r_mc_p index by sch2.pkg_a.cd;

                                  --

                                  type r_mc is record

                                    (mc  sch1.pkg_b.r_mb_mc

                                    ,p   t_mc_p

                                    ,v_m sch1.pkg_a.yn_flag

                                    );

                                  type t_mc is table of r_mc index by binary_integer;

                                  --

                                  type r_w_g is record

                                    (start_date date

                                    ,stop_date  date

                                    ,g_t        w_g_t

                                    );

                                  type t_w_g is table of r_w_g index by binary_integer;

                                  --

                                  type r_d is record

                                    (wc int

                                    ,d  int

                                    ,f  int

                                    ,m  int

                                    ,a  int

                                    ,n  int

                                    );

                                  type r_w is record

                                    (wc int

                                    ,d  int

                                    ,f  int

                                    ,m  int

                                    ,a  int

                                    ,n  int

                                    );

                                  type r_h is record

                                    (wc number

                                    ,d  number

                                    ,f  number

                                    ,m  number

                                    ,a  number

                                    ,n  number

                                    );

                                  --

                                  type r_u is record

                                    (hr_wh number

                                    ,d     r_d

                                    ,w     r_w

                                    ,hr_v  r_h

                                    ,hr_b  r_h

                                    );

                                  type r_ssa is record

                                    (amt  number

                                    ,w    int

                                    ,hr_v number

                                    ,hr_b number

                                    ,c_v  number

                                    ,c_b  number

                                    ,c_p  number

                                    ,c_f  number

                                    );

                                  --

                                  type r_m is record

                                    (u       r_u

                                    ,u_b_aed r_u

                                    ,u_a_aed r_u

                                    ,ssa     r_ssa

                                    );

                                  type t_m is table of r_m index by m_idx;

                                  --

                                  type r_y is record

                                    (u       r_u

                                    ,u_b_aed r_u

                                    ,u_a_aed r_u

                                    ,ssa     r_ssa

                                    ,hr_n    number

                                    ,hr_o    number

                                    ,hr_d    number

                                    ,hr_v    number

                                    ,hr_b    number

                                    ,hr_n_b  number

                                    ,c_v     number

                                    ,c_b     number

                                    ,c_b_p   number

                                    ,c_b_f   number

                                    ,b       b_t

                                    ,b_cd    be_cd

                                    );

                                  type t_y is table of r_y index by y_idx;

                                  --

                                  type t_ph_y is table of sch1.pkg_b.r_mb_ph index by y_idx;

                                  --

                                  type r_be is record

                                    (b_id            pk

                                    ,mc              t_mc

                                    ,wh              sch1.pkg_b.t_mb_wh

                                    ,ph_y            t_ph_y

                                    ,hd              date

                                    ,aed             date

                                    ,ldw             date

                                    ,c_ex            int

                                    ,a_as_of_aed     sch1.pkg_a.yn_flag

                                    ,h_mc_a_aed      sch1.pkg_a.yn_flag

                                    ,h_wh_b_aed_c_ex sch1.pkg_a.yn_flag

                                    ,g               t_w_g

                                    ,m               t_m

                                    ,y               t_y

                                    ,p_dt            date

                                    );

                                  type t_be is table of r_be index by be_cd;

                                  --

                                  type t_p_ph is table of sch1.pkg_b.r_mb_ph index by p_ph_idx;

                                  --

                                  type r_p is record

                                    (p_id      pk

                                    ,pa        sch1.pkg_b.t_mb_pa

                                    ,p_pa      sch1.pkg_b.r_mb_p_p

                                    ,hd        date

                                    ,ldw       date

                                    ,aed       date

                                    ,hd_be_id  pk

                                    ,hd_be_cd  be_cd

                                    ,ldw_be_id pk

                                    ,ldw_be_cd be_cd

                                    ,be        t_be

                                    ,m_h_m     sch1.pkg_a.yn_flag

                                    ,m         t_m

                                    ,y         t_y

                                    ,p_dt      date

                                    ,d_t_dt    date

                                    ,c_bn      number

                                    ,c_b_f     number

                                    ,c_v_c     number

                                    ,c_v_r     number

                                    ,c_b       int

                                    ,m_v       sch1.pkg_a.yn_flag

                                    ,v_r       sch1.pkg_a.col_value

                                    ,v_dt      date

                                    ,v_dt_15y  date

                                    ,v_dt_10y  date

                                    ,v_dt_05y  date

                                    ,ph        t_p_ph

                                    );

                                  type t_p is table of r_p index by cd;

                                  --

                                  type r_f is record

                                    (f sch1.pkg_a.r_f

                                    ,p t_p

                                    );

                                  type t_f is table of r_f index by binary_integer;

                                  --

                                  type r_mb is record

                                    (p_a_id pk

                                    ,d      sch1.pkg_b.r_mb_d

                                    ,f      t_f

                                    );

                                  --

                                  function f_i_d return r_d;

                                  --

                                  procedure p_c_p_hd

                                            (pior_p   in out nocopy sch2.pkg_b.r_p

                                            ,piv_p_cd               sch2.pkg_b.cd

                                            );

                                  procedure p_c_m_p

                                            (pin_mb_id  sch1.pkg_a.pk

                                            ,pid_r_dt   date default null

                                            ,pin_p_a_id sch1.pkg_a.pk default null

                                            );

                                end pkg_b;

                                /

                                grant execute,debug on sch2.pkg_b to sch1;

                                 

                                create or replace

                                package sch2.pkg_b_dtl is

                                  cv_p_cd constant sch2.pkg_a.cd := '2A';

                                  cn_f_id constant sch2.pkg_a.pk := 10;

                                  --

                                  procedure p_c_m_p_p

                                            (pior_mb    in out sch2.pkg_b.r_mb

                                            ,pid_r_dt          date default null

                                            ,pin_p_a_id        sch1.pkg_a.pk default null

                                            );

                                end pkg_b_dtl;

                                /

                                grant execute,debug on sch2.pkg_b_dtl to sch1;

                                 

                                create or replace

                                package body sch2.pkg_b is

                                  function f_i_d return r_d is

                                    lr_d r_d;

                                  begin

                                    return lr_d;

                                  end f_i_d;

                                  --

                                  function f_mb_i_d

                                           (pin_mb_id  sch1.pkg_a.pk

                                           ,pin_p_a_id sch1.pkg_a.pk default null

                                           )

                                           return r_mb is

                                    lr_mb_t sch1.pkg_b.r_mb_dtl;

                                    lt_mc   t_mc;

                                    lr_mb   r_mb;

                                    lr_f    r_f;

                                    lr_p    r_p;

                                  begin

                                    lr_p.p_id := 70;

                                    lr_f.f.col1 := sch2.pkg_b_dtl.cn_f_id;

                                    --

                                    lr_f.p(sch2.pkg_b_dtl.cv_p_cd) := lr_p;

                                    lr_mb.f(sch2.pkg_b_dtl.cn_f_id) := lr_f;

                                    --

                                    return lr_mb;

                                  end f_mb_i_d;

                                  --

                                  procedure p_c_p_hd

                                            (pior_p   in out nocopy sch2.pkg_b.r_p

                                            ,piv_p_cd               sch2.pkg_b.cd

                                            )

                                            is

                                    lv_be_cd sch2.pkg_b.be_cd;

                                    lt_mc    sch2.pkg_b.t_mc;

                                    lt_wh    sch1.pkg_b.t_mb_wh;

                                  begin

                                    null;

                                  end p_c_p_hd;

                                  --

                                  procedure p_c_m_p

                                            (pin_mb_id  sch1.pkg_a.pk

                                            ,pid_r_dt   date default null

                                            ,pin_p_a_id sch1.pkg_a.pk default null

                                            )

                                            is

                                    lr_mb r_mb;

                                  begin

                                    lr_mb :=

                                      f_mb_i_d

                                        (pin_mb_id  => pin_mb_id

                                        ,pin_p_a_id => pin_p_a_id

                                        );

                                    --

                                    sch2.pkg_b_dtl.p_c_m_p_p(lr_mb,null,pin_p_a_id);

                                  end p_c_m_p;

                                end pkg_b;

                                /

                                 

                                create or replace

                                package body sch2.pkg_b_dtl is

                                  subtype be_yr_idx is varchar2(30);

                                  type t_ph_be_yr is table of sch1.pkg_b.r_mb_ph index by be_yr_idx;

                                  --

                                  procedure p_a

                                            (pior_ph in out nocopy sch1.pkg_b.r_mb_ph

                                            ,pir_yr                sch2.pkg_b.r_y

                                            )

                                            is

                                  begin

                                    null;

                                  end p_a;

                                  --

                                  procedure p_disc(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_disc;

                                  --

                                  procedure p_asof_aed(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_asof_aed;

                                  --

                                  procedure p_l_mc(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_l_mc;

                                  --

                                  procedure p_l_wh(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_l_wh;

                                  --

                                  procedure p_l

                                            (pior_p      in out nocopy sch2.pkg_b.r_p

                                            ,pid_mb_d_dt               date

                                            )

                                            is

                                  begin

                                    null;

                                  end p_l;

                                  --

                                  procedure p_f(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_f;

                                  --

                                  procedure p_g

                                            (pior_p    in out nocopy sch2.pkg_b.r_p

                                            ,piv_be_cd               sch2.pkg_b.be_cd

                                            ,piv_g_t                 sch2.pkg_b.t_w_g

                                            )

                                            is

                                  begin

                                    null;

                                  end p_g;

                                  --

                                  procedure p_a_h(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_a_h;

                                  --

                                  procedure p_fl(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_fl;

                                  --

                                  procedure p_ml(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_ml;

                                  --

                                  procedure p_w_f_d(pior_units in out nocopy sch2.pkg_b.r_u) is

                                  begin

                                    null;

                                  end p_w_f_d;

                                  --

                                  procedure p_n_wh_h

                                            (pior_n_wh_h in out nocopy sch2.pkg_b.r_h

                                            ,pir_u                     sch2.pkg_b.r_u

                                            )

                                            is

                                  begin

                                    null;

                                  end p_n_wh_h;

                                  --

                                  procedure p_s

                                            (pior_y        in out nocopy sch2.pkg_b.r_y

                                            ,piv_y_idx                   sch2.pkg_b.y_idx

                                            )

                                            is

                                  begin

                                    null;

                                  end p_s;

                                  --

                                  procedure p_spr

                                            (pior_be    in out nocopy sch2.pkg_b.r_be

                                            ,piv_y_idx               sch2.pkg_b.y_idx

                                            )

                                            is

                                  begin

                                    null;

                                  end p_spr;

                                  --

                                  procedure p_a_m_y

                                            (pior_p in out nocopy sch2.pkg_b.r_p

                                            ,piv_be_cd               sch2.pkg_b.cd

                                            )

                                            is

                                  begin

                                    null;

                                  end p_a_m_y;

                                  --

                                  procedure p_a_m_i_y(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_a_m_i_y;

                                  --

                                  procedure p_a_m_i_p_y(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_a_m_i_p_y;

                                  --

                                  procedure p_c_4_y(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_c_4_y;

                                  --

                                  procedure p_a_p_d(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_a_p_d;

                                  --

                                  procedure p_c_p_d(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_c_p_d;

                                  --

                                  procedure p_c_v(pior_p in out nocopy sch2.pkg_b.r_p) is

                                  begin

                                    null;

                                  end p_c_v;

                                  --

                                  procedure p_s_p_b

                                            (pior_p    in out nocopy sch2.pkg_b.r_p

                                            ,piv_y_idx               sch2.pkg_b.y_idx

                                            )

                                            is

                                  begin

                                    null;

                                  end p_s_p_b;

                                  --

                                  procedure p_s_b

                                            (pior_p  in out nocopy sch2.pkg_b.r_p

                                            ,piv_y_idx               sch2.pkg_b.y_idx

                                            )

                                            is

                                  begin

                                    null;

                                  end p_s_b;

                                  --

                                  procedure p_c_p_y_b_c

                                            (pior_p   in out nocopy sch2.pkg_b.r_p

                                            ,piv_y_idx                sch2.pkg_b.y_idx

                                            )

                                            is

                                  begin

                                    null;

                                  end p_c_p_y_b_c;

                                  --

                                  procedure p_c_t_p_y

                                            (pior_p   in out nocopy sch2.pkg_b.r_p

                                            ,pid_mb_b               date

                                            ) is

                                    lv_y_idx sch2.pkg_b.y_idx;

                                    lv_be_cd sch2.pkg_b.be_cd;

                                    lr_be_y  sch2.pkg_b.r_y;

                                  begin

                                    null;

                                  end p_c_t_p_y;

                                  --

                                  procedure p_c_d_t_d(pior_p in out nocopy sch2.pkg_b.r_p) is

                                    lv_be_cd sch1.pkg_a.be_cd;

                                    lr_mc    sch2.pkg_b.r_mc;

                                  begin

                                    null;

                                  end p_c_d_t_d;

                                  --

                                  procedure p_p_p_d(pir_mb sch2.pkg_b.r_mb) is

                                    lr_p      sch2.pkg_b.r_p;

                                    lv_be_cd  sch2.pkg_b.be_cd;

                                    lv_y_idx sch2.pkg_b.y_idx;

                                    lt_ph     t_ph_be_yr;

                                    lv_ph_idx be_yr_idx;

                                  begin

                                    null;

                                  end p_p_p_d;

                                  --

                                  procedure p_c_m_p_p

                                            (pior_mb    in out sch2.pkg_b.r_mb

                                            ,pid_r_dt          date default null

                                            ,pin_p_a_id        sch1.pkg_a.pk default null

                                            )

                                            is

                                    lr_p sch2.pkg_b.r_p;

                                  begin

                                    lr_p := pior_mb.f(cn_f_id).p(cv_p_cd);

                                    --

                                    p_disc(pior_p => lr_p);

                                    sch2.pkg_b.p_c_p_hd(pior_p => lr_p, piv_p_cd => cv_p_cd);

                                    p_asof_aed(pior_p => lr_p);

                                    p_l(pior_p => lr_p, pid_mb_d_dt => pior_mb.d.col7);

                                    p_f(pior_p => lr_p);

                                    --

                                    p_a_h(pior_p => lr_p);

                                    p_fl(pior_p => lr_p);

                                    p_ml(pior_p => lr_p);

                                    p_a_m_i_y(pior_p => lr_p);

                                    p_a_m_i_p_y(pior_p => lr_p);

                                    --

                                    p_c_4_y(pior_p => lr_p);

                                    p_c_p_d(pior_p => lr_p);

                                    p_c_t_p_y

                                      (pior_p   => lr_p

                                      ,pid_mb_b => pior_mb.d.col6

                                      );

                                    --

                                    p_c_d_t_d(pior_p => lr_p);

                                    --

                                    pior_mb.f(cn_f_id).p(cv_p_cd) := lr_p;

                                    p_p_p_d(pir_mb => pior_mb);

                                  end p_c_m_p_p;

                                end pkg_b_dtl;

                                /

                                • 13. Re: SQL Developer doesn't step into code
                                  user8009976

                                  I was wondering if you had any updates on this.

                                  • 14. Re: SQL Developer doesn't step into code
                                    thatJeffSmith-Oracle

                                    it sounds like a bug in 10g, but 10 is ancient

                                     

                                    to report bugs, please use My Oracle Support

                                     

                                    You could grab our developer days VM which runs 12.1.0.2 and see if it happens there

                                    1 2 Previous Next