1

I have couple stored procedures and a package.

Whenever this stored procedure and package goes invalid, I need to send an email.

How can I achieve this?

Can you please give me an idea how this can be done.

Hannah Vernon
  • 70,928
  • 22
  • 177
  • 323
user37143
  • 45
  • 1
  • 1
  • 5

1 Answers1

0

The scope of this question requires some work on your part but as a rough guide:

  • get an email package. You can roll your own using UTL_SMTP (Pre Oracle 10) or it's succesor UTL_MAIL (Oracle 10 onwards). However this is such a basic functionality that many people have written and shared their own versions. For Oracle 11 and beyond do not forget to allocate permissions for your database to access the ports required.
  • create a package to manage your errors. Today you want to see when a package has been invalidated, tomorrow it will be something else. Plan for the future.... You would need to create a procedure with a cursor along the lines of SELECT owner, object_type, object_name, status FROM dba_objects WHERE status = 'INVALID' ORDER BY owner, object_type, object_name;
  • open the cursor, when you find something, use the email package to mail who you want
  • finally, if you find a problem, fix the problem: use the handy code here: http://www.oracle-base.com/articles/misc/recompiling-invalid-schema-objects.php to recompile the package

Edit: the original poster asks how to track incidents. More information is needed such, Oracle version, what kind of incidents, how often do you want to receive notifications.

kevinskio
  • 4,272
  • 1
  • 30
  • 50