Reputation: 181
I am using SQL developer. I have a stored procedure that is running a long time. I need to trace what happens during that run time.
I tried with DBMS_output.put_line('trace');
. But it print after the stored procedure has completed. Also I can't use DBMS_trace
(might be older version I am using).
So I came up with idea. I would like to insert into table during stored procedure run time. How can I Commit only that insert query in stored procedure?
Upvotes: 1
Views: 2192
Reputation: 59652
I see two possibilities.
Either you write a procedure which inserts log messages into a table. Declare this procedure with PRAGMA AUTONOMOUS_TRANSACTION
:
CREATE OR REPLACE PROCEDURE WriteLogMsg(LogMessage IN VARCHAR2) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO T_LOG_ENTRIES (LOG_DATE, LOG_MESSAGE)
VALUES (CURRENT_TIMESTAMP, LogMessage);
COMMIT;
END WriteLogMsg;
Or use the DBMS_APPLICATION_INFO
package:
PROCEDURE LONG_RUNNING_PROCEDURE IS
BEGIN
DBMS_APPLICATION_INFO.SET_MODULE('LONG_RUNNING_PROCEDURE', 'Starting');
...
DBMS_APPLICATION_INFO.SET_ACTION('Still working, please be patient');
...
DBMS_APPLICATION_INFO.SET_ACTION('Finished');
-- DBMS_APPLICATION_INFO.SET_MODULE(NULL, NULL); -> removes entries for MODULE and ACTION
END;
While the procedure is running you can query information with
SELECT USERNAME, MODULE, ACTION
from V$SESSION
where USERNAME = ...;
Upvotes: 4
Reputation: 409
In this case you have to use the Pragma Autonomous transactions.
Create a new procedure with Pragma Autonomous.
PROCEDURE test_autonomous
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
insert ....
commit;
END test_autonomous;
now call this procedure in your code. this will do your trick. You can all use the parameters to pas some data you want to insert.
Upvotes: 1