FORMAT_CALL_STACK

This function formats the current call stack. 
This can be used on any stored procedure or trigger to access the call stack. 
This can be useful for debugging.

http://docs.oracle.com/database/121/ARPLS/d_util.htm#ARPLS73240

1.) Procedure to execute FORMAT_CALL_STACK:

CREATE OR REPLACE PROCEDURE PR_FORMAT_CALL_STACK AS
BEGIN
  DBMS_OUTPUT.put_line('---------------------------------------------------');
  DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
  DBMS_OUTPUT.put_line('---------------------------------------------------');
END;
/

2.) Procedure to test:


create or replace procedure pr_test_stack (p_stack in char) as

cursor c1 is select * from emp;

begin

for xx in c1
loop
    
    dbms_output.put_line(xx.emp_name || ' dep id:' || to_char(xx.id_dep));
    
end loop;

    if upper(p_stack) ='Y' then
        pr_format_call_stack;
    end if;

end;
/

3.) Testing

set serveroutput on;
SQL> exec pr_test_stack('y');
emp 1 dep id:1
emp 2 dep id:1
emp 3 dep id:1
emp 4 dep id:2
emp 5 dep id:2
emp 6 dep id:2
emp 7 dep id:3
emp 8 dep id:3
emp 9 dep id:3
---------------------------------------------------
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
00007FFAE1321688         4  procedure SYS.PR_FORMAT_CALL_STACK
00007FFADFB68D80        15  procedure SYS.PR_TEST_STACK
00007FFAE12B5118         1  anonymous block

---------------------------------------------------

PL/SQL procedure successfully completed.

SQL> exec pr_test_stack('n');
emp 1 dep id:1
emp 2 dep id:1
emp 3 dep id:1
emp 4 dep id:2
emp 5 dep id:2
emp 6 dep id:2
emp 7 dep id:3
emp 8 dep id:3
emp 9 dep id:3

PL/SQL procedure successfully completed.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s