Find number of times the procedure is called using another procedure

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?

---

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.

---

insert a row into a log table.

---

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)?