I have two procedures A and B. Procedure A performs certain tasks. Procedure B has to monitor how many times procedure A is called in a day.
How to achieve this?
+1
A:
Add a statement to the procedure:
update statistics_table
set proc_a_count = proc_a_count + 1;
Of course, you'll have to create a suitable table to hold the count and initialize it with a zero in the field.
wallyk
2010-02-12 06:25:24
except that this would serialize the procedure.
Jeffrey Kemp
2010-02-12 06:41:17
That'll only work if the insert into the log file is guaranteed to commit independent of the applications unit of wok, requiring an autonomous transaction, and that gets dangerous. Auditing execute is the better solution.
Adam Musch
2010-02-15 05:54:29
Agreed, Oracle's auditing feature is an excellent and perhaps better option, but autonomous transactions are only dangerous if they're misused. I use them when I want to log activity, regardless of the success/failure of the calling transaction. For this purpose they're perfectly suited.
Jeffrey Kemp
2010-02-16 02:01:51
+1
A:
Oracle does not track this sort of thing by default but if you just want to record some simple information then switch on the built-in AUDIT functionality:
AUDIT EXECUTE PROCEDURE BY ACCESS;
You can view the accesses in the view dba_audit_trail. Find out more.
If for some reason you don't want to use the audit trail - say you want to capture more information - then you will need to use your own logging mechanism. This is a good use for the AUTONOMOUS TRANSACTION pragma. Just be careful that writing the log records doesn't have an undue impact on the performance of your application.
edit
The role of procedure B in your question is entirely superfluous: either the database records how often procedure A runs or else A writes its own trace records. Unless B is a packaged query on the log (however implemented)?
APC
2010-02-12 07:04:28