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.

  • what do you mean by "go invalid"? Do you mean "they fail to execute"? – Max Vernon May 26 '14 at 16:11
  • "go invalid" means when an object gets invalidated – user37143 May 26 '14 at 16:37
  • Why send an email? You should try to fix the root cause: Why are your packages becoming invalid? – Colin 't Hart May 28 '14 at 11:49
  • not sure,why these are getting invalid – user37143 May 28 '14 at 12:22

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.

  • can i get an overview on how to send email notification on expiring tickets -incidents.i can handle the email part now – user37143 May 28 '14 at 8:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.