Adding logs in stored procedure to display number of records inserted in DB

1
votes

I have a shell script which calls a stored procedure which is inserting data in the Oracle DB. I takes 4-5 hours to run the procedure. I want to add a log in the stored procedure which would display the no. of records inserted in the DB after a particular interval of time (say in evrey 20 minutes). Is there a way to add logs in the stored procedure?

1
oraclestored-procedures
Yes, its a common thing actually. See my answer [here][1]. [1]: stackoverflow.com/questions/4919437/dbms-output-put-line - tbone

1 Answers

0
votes

It depends how you're doing it. If you're just doing a straight insert only insert into ... select ... then no, there's no way. However, if you have some sort of looping in there you can use the dbms_application_info package to record your actions in the V$SESSION view.

I normally do something like this:

dbms_application_info.set_module('Updating Blah','Total: ' || <index var>);

The first parameter is the module_name, which you can view on the module column and the second parameter is action_name, which is the action column in V$SESSION.

Alternatively you can always insert or update a smaller table which tracks what you're doing and can therefore by asynchronously queried.