CODEWITHSUNDEEP
oracle-databaseplsql
user3410249
user3410249

Reputation: 181

How to Commit only one query during Oracle stored procedure runtime

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

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

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

kapil
kapil

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

Related Questions