Oracle PL/SQL debug procedure - using $$plsql_line

Question

I'm looking to create a procedure that helps with debugging specific variables, and for the sake of re-usability I'd like to store it in it's own procedure and pass in the specific variable when needed such as

debugz(var_x);

and the debug procedure would do the following -

PROCEDURE debugz (var_x VARCHAR2(1000))
AS
BEGIN
DBMS_OUTPUT.put_line ('Variable value: '|| var_x || ' | Line number: ' || $$plsql_line ||' | Unit: '|| $$plsql_unit);
END;

The problem is, I want the PL/SQL line number and PL/SQL unit to be based off of where the procedure call originates, i.e. the line/unit of "debugz(var_x);". Is there anyway for debugz to output that line number without passing in additional information?

cagcowboy · Accepted Answer · 2012-03-14 22:07:01Z

5

Check out DBMS_UTILITY.FORMAT_CALL_STACK

You may need/want to parse the output depending on your exact requirements, but may be a good place to start.

Oracle 10g onwards.

Also note DBMS_UTILITY.FORMAT_ERROR_BACKTRACE and DBMS_UTILITY.FORMAT_ERROR_STACK for error handling.

answered Mar 14, 2012 at 22:07

cagcowboy

31.1k11 gold badges75 silver badges95 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kyle H Over a year ago

Thanks, I might end up using this if nothing easier shows up. It might be a bit of a pain to parse out just the line number from the brick it returns though.

llayland Over a year ago

It is actually very easy to parse. I do this frequently for debugging. You can even tie back to the source to do some nifty things - like facilitating lightweight test scripts. search for Tom Kyte's who_called_me function for an example to get you started.

Kyle H Over a year ago

@llayland thanks for the tip, I looked at that function and it's very robust. I'll probably just parse out a few smaller pieces of information using REGEXP but I'll be able to use that function as a reference if I run into issues.

psaraj12 · Accepted Answer · 2012-03-31 16:21:12Z

There is a very good debugging package unit called debugf which provides good functionality

The debug file contains information such as session id,date and time,packages being called and the line number of each debug message and the debug message itself

Example usage is given below

This is used to intialize the debug, the first parameter 'ALL' meaning all modules(can be function,procedure or package etc) and SYSTEM meaning the schema i want to debug

 debug.init(p_modules => 'ALL',p_file =>'C:\debugf123\temp\test.txt',p_user =>'SYSTEM',p_show_date => 'YES',p_date_format =>'DD/MM/YYYY  HH24:MI:SS',p_name_len => 30,p_show_sesid => 'YES');

This one works like printf in C and the maximum you can give is 10 parameters where v_word1 is a parameter

debug.f('the first is %s',v_word1);

This is same as debug.f but here you can give more than 10 parameters

debug.fa('the third is %s and %s',debug.argv(v_word1,v_amount));

The source code for this package is available at

http://gerardnico.com/wiki/database/oracle/debugf

Jokke Heikkilä · Accepted Answer · 2012-03-18 13:45:36Z

0

OWA_UTIL.WHO_CALLED_ME(
   owner          OUT      VARCHAR2,
   name           OUT      VARCHAR2,
   lineno         OUT      NUMBER,
   caller_t       OUT      VARCHAR2);

answered Mar 18, 2012 at 13:45

Jokke Heikkilä

9281 gold badge8 silver badges17 bronze badges

Comments

junaling · Accepted Answer · 2012-04-25 22:56:56Z

Have you tried remote debugging? Here's how you can do it in SQL Developer:

1) Reference: http://sueharper.blogspot.ca/2006/07/remote-debugging-with-sql-developer_13.html

2) User privileges: grant EXECUTE on DBMS_DEBUG_JDWP to USERXX; grant DEBUG CONNECT SESSION to USERXX; grant DEBUG ANY PROCEDURE to USERXX;

3) Set Remote Debug on USERXX connection in SQL Developer: Port: 80 (use 4000 if not blocked by firewall) Local Address: IP address of your local machine

4) Compile code (package, procedure or function) in USERXX in SQL Developer for debug

5) Set breakpoints in code

6) On the remote (this could be your application invoking the PLSQL code): before invoking the PLSQL code run/include the following: DBMS_DEBUG_JDWP.CONNECT_TCP( 'IP address in 3', port in 3 ) when this is run SQL developer will switch to debug mode.

7) Run application or invoke procedure from remote as USERXX

8) SQL developer stops at first breakpoint, step into, view/change values and etc.

Collectives™ on Stack Overflow

Oracle PL/SQL debug procedure - using $$plsql_line

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related