Home » SQL & PL/SQL » SQL & PL/SQL » debugging stored procedure
debugging stored procedure [message #35780] Mon, 15 October 2001 14:17 Go to next message
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 #35791 is a reply to message #35780] Tue, 16 October 2001 07:19 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
create debug table and insert values in to that table.

----------------------------------------------------------------------
Re: debugging stored procedure [message #35803 is a reply to message #35780] Tue, 16 October 2001 10:39 Go to previous messageGo to next message
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 #38273 is a reply to message #35791] Fri, 05 April 2002 23:18 Go to previous messageGo to next message
gg
Messages: 3
Registered: January 2002
Junior Member
Is there any other way to debug the stored procedure other than creating temp table ....
Re: debugging stored procedure [message #38276 is a reply to message #35791] Sat, 06 April 2002 09:19 Go to previous message
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
Previous Topic: getting ???? as output value from stored procedure.
Next Topic: outer join
Goto Forum:
  


Current Time: Mon Mar 18 22:58:05 CDT 2024