1

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?

Shadab Ali
  • 43
  • 1
  • 5
  • Yes, its a common thing actually. See my answer [here][1]. [1]: http://stackoverflow.com/questions/4919437/dbms-output-put-line – tbone Sep 13 '12 at 11:51

1 Answers1

0

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.

Ben
  • 51,770
  • 36
  • 127
  • 149
  • Quick additional point; 4-5 hours is 1/6th of a day, which is rather a long time for a job to be running. You might want to look into improving the procedure. – Ben Sep 13 '12 at 06:57