debugging stored procedure [message #35780] |
Mon, 15 October 2001 14:17 |
Rahul
Messages: 94 Registered: December 1998
|
Member |
|
|
Hi
I have a stored procedure which I am calling from a java program using JDBC. I need to debug the sp at run time. I am trying to use dbms_output.putline. But when i run the program, no output is seen. But if I execute the sp from sqlplus i can see the output. What can I do to see the output while I call the sp from my java program?? Or where is that output going??
thanx
rahul
----------------------------------------------------------------------
|
|
|
|
Re: debugging stored procedure [message #35803 is a reply to message #35780] |
Tue, 16 October 2001 10:39 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
you can use "pragma autonomous transaction" in 8.1.5 and above to commit debug info without effecting your application transactions.
create table DEBUG_SQL (seq number, timestamp date, comments varchar2(200));
CREATE OR REPLACE PROCEDURE Put_Debug_Sql (i_seq IN INTEGER, i_msg VARCHAR)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO DEBUG_SQL
VALUES (i_seq, SYSDATE, i_msg);
COMMIT;
END;
/
Put_Debug_Sql (0,'=== Start - parameters passed into PACKAGE ABC.main ===');
Put_Debug_Sql (1,'-> cust='||custid);
Put_Debug_Sql (2,'=== End - parameters passed into PACKAGE ABC.main ===');
----------------------------------------------------------------------
|
|
|
|
Re: debugging stored procedure [message #38276 is a reply to message #35791] |
Sat, 06 April 2002 09:19 |
andrew again
Messages: 2577 Registered: March 2000
|
Senior Member |
|
|
At the start of your JDBC connection you can issue the following statement:
execute immediate 'alter session set sql_trace=true';
This will generate a trace fiile on the server for each JDBC connection you open. Find the trace file in the "user_dump_dest" location specified in the init.ora (select value from v$parameter where name = 'user_dump_dest';). Once in that directory, find the latest trace file (on unix ==> ls -ltr *.trc).
Browse that file (without putting it through tkprof to format the output).
You can also add the execute immediate to one of you procedures/packages called through JDBC.
If you want to debug the procedure from a tool like TOAD, you can singlestep through the code. If there are calls to htp and owa family of web packages, you need to initialize the owa environment first. Use owa.init_cgi_env
|
|
|