Take the 2-minute tour ×

Oracle version:10g I have a stored procedure, I want to know how many times my stored procedure has run. Was it successful or not?

share|improve this question
I don't believe there is anything built in that could do this. What does the SP do? Could it not just change a date in a table once it has run? Alternatively, if it is a maintenance SP, you might want to consider setting it up as a job as I suspect this would record some history. –  Robbie Dee Mar 8 '13 at 11:29

2 Answers 2

Oracle does not store this information by default.

You can use the AUDIT statement to enable auditing of procedural calls, by auditing EXECUTE PROCEDURE:

audit execute procedure on my_stored_proc;

(where my_stored_proc can be a package or whatever). You need to have the appropriate system privilege to AUDIT things, even if they're your things. This is because auditing is an overhead, plus access to the audit trail itself is tightly controlled (for understandable reasons).

So AUDIT probably isn't what you're looking for. Besides it won't give the low level stuff you want ("Was it successful or not?"). Which leaves you with the option of building instrumentation into your procedure. This is generally a good idea, because debugging is a lot easier with a decent trace.

Oracle doesn't provide a built-in library for this (which I find frankly astonishing but there you go). Building your own logger is quite easy. Alternatively check out out Tyler Muth's Logger project. Find it here. (No warranty is implied or intended)

share|improve this answer

no way to do this in oracle database. I am also shocked they do not provide a package that would log information to a table for you. They could provide a default logging package that provided following columns

  • UserID
  • Log Level
  • Time stamp
  • Command

Implement your own package with stored procedures for logging.

share|improve this answer

Your Answer


