0

All I've created a table in Toad for oracle called error_log. The purpose of this to log any errors that previously written packages may encounter and log them for the developers to see. Right now, I am tasked with creating a package/procedure that will essentially induce an error and be able to log that into the error_log table? Any thoughts on how I would go about this?

Jules
  • 215
  • 1
  • 8
  • 13
  • a simple [example](http://stackoverflow.com/questions/4919437/dbms-output-put-line/4921960#4921960) – tbone Sep 09 '15 at 16:56

2 Answers2

5

First of all, make the package procedure an autonomous transaction, so that whatever you write to the log stays written, even if the calling transaction rolls back.

Second, make the caller of you log procedure pass as little information as possible: ideally, just a message. Inside your log procedure, you can do things such as:

  • decide if logging is even "turned on" and logs should be written
  • decide what package and line number called the log procedure (so you can write the source of the message)
  • decide what the timestamp for the message should be
  • decide who the user is, what his IP address is, etc, if you want to log those things

Third, and this is just my opinion, don't have a concept of "message level". I basically have two types of messages -- errors and non-errors. Errors are always written; non-errors can be turned on or off. When you try to have logging levels like "Fine", "Finer", and "Finest", you're always going to turn it on to "Finest" whenever there is a problem.

Matthew McPeak
  • 17,705
  • 2
  • 27
  • 59
-1

You'll want to use the RAISE_APPLICATION_ERROR procedure that allows you to raise an error you have defined.

Handling PL/SQL Errors

Nick
  • 2,524
  • 17
  • 25