Oracle Stored Procedure Error Logging
Contents |
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more oracle stored procedure logging about Stack Overflow the company Business Learn more about hiring developers or posting
Oracle Raise Exception With Message
ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack error table in oracle Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up Log error messages in Oracle stored procedure up vote 2 down dml error logging in oracle 11g vote favorite 1 We plan to configure a stored procedure to run as a batch job daily using Oracle DBMS scheduler package. We would like to know what would be the best way to log an error message when there is an error occured. Is logging to a temporary table an option? or is there a better option. Thanks in advance. sql oracle stored-procedures plsql share|improve this question edited
Oracle Pl Sql Error Handling Best Practices
Oct 14 '09 at 18:42 OMG Ponies 199k37361417 asked Oct 14 '09 at 18:34 user175698 add a comment| 4 Answers 4 active oldest votes up vote 0 down vote accepted You say that you don't have a lot of control over the DB environment to install logging packages - if this is the case then you'll be limited to querying the information in the dba_scheduler_job_run_details and dba_scheduler_job_log system views - you'll be able to see the history of executions here. Unhandled exceptions will show up in the ADDITIONAL_INFO column. If you need notification you can poll these views and generate email. share|improve this answer answered Oct 14 '09 at 19:28 dpbradley 9,1461930 add a comment| up vote 8 down vote If you decide to roll your own logging and log into a table you might go the Autonomous Transaction route. An Autonomous Transaction is a transaction that can be commited independently of the current transaction you are in. That way you can log and commit all the info you want to your log table independently of the success or failure of your stored procedure or batch process parent transaction. CREATE OR REPLACE PROCEDURE "SP_LOG" ( P_MESSAGE_TEXT VARCHAR2 ) IS pragma autonomous_transaction; BEGIN DBMS_OUTPUT.PUT_LINE(P_MESSAGE_TEXT); INSER
March 2012 Oracle Magazine Online 2016 2015 2014 2013 2012 2011 2010 January 2012 March 2012 May 2012 July 2012 September 2012 November 2012 As Published In March/April 2012 TECHNOLOGY: PL/SQL Error Management By Steven Feuerstein Part 6 pl sql exception handling examples in a series of articles on understanding and using PL/SQL Even if you write pl sql logging example absolutely perfect PL/SQL programs, it is possible and even likely that something will go wrong and an error will occur when those
Anonymous Exception In Oracle
programs are run. How your code responds to and deals with that error often spells the difference between a successful application and one that creates all sorts of problems for users as well as developers. This http://stackoverflow.com/questions/1568166/log-error-messages-in-oracle-stored-procedure article explores the world of error management in PL/SQL: the different types of exceptions you may encounter; when, why, and how exceptions are raised; how to define your own exceptions; how you can handle exceptions when they occur; and how you can report information about problems back to your users. Exception Overview There are three categories of exceptions in the world of PL/SQL: internally defined, predefined, and user-defined. An internally defined exception is http://www.oracle.com/technetwork/issue-archive/2012/12-mar/o22plsql-1518275.html one that is raised internally by an Oracle Database process; this kind of exception always has an error code but does not have a name unless it is assigned one by PL/SQL or your own code. An example of an internally defined exception is ORA-00060 (deadlock detected while waiting for resource). A predefined exception is an internally defined exception that is assigned a name by PL/SQL. Most predefined exceptions are defined in the STANDARD package (a package provided by Oracle Database that defines many common programming elements of the PL/SQL language) and are among the most commonly encountered exceptions. One example is ORA-00001, which is assigned the name DUP_VAL_ON_INDEX in PL/SQL and is raised when a unique index constraint is violated. A user-defined exception is one you have declared in the declaration section of a program unit. User-defined exceptions can be associated with an internally defined exception (that is, you can give a name to an otherwise unnamed exception) or with an application-specific error. Every exception has an error code and an error message associated with it. Oracle Database provides functions for retrieving these values when you are handling an exception (see Table 1). Description How to Get It The error code. This code is useful when you need to look up generic information about what mi
program to continue to operate in the presence of errors. Topics: Overview of PL/SQL Run-Time Error Handling Guidelines for Avoiding and Handling PL/SQL Errors and Exceptions Advantages of PL/SQL Exceptions https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/errors.htm Predefined PL/SQL Exceptions Defining Your Own PL/SQL Exceptions How PL/SQL Exceptions Are Raised How PL/SQL Exceptions Propagate Reraising a PL/SQL Exception Handling Raised PL/SQL Exceptions Overview of PL/SQL Compile-Time Warnings Overview of PL/SQL Run-Time Error Handling In PL/SQL, an error condition is called an exception. An exception can be either internally defined (by the run-time system) or user-defined. Examples of internally defined exceptions are in oracle ORA-22056 (value string is divided by zero) and ORA-27102 (out of memory). Some common internal exceptions have predefined names, such as ZERO_DIVIDE and STORAGE_ERROR. The other internal exceptions can be given names. You can define your own exceptions in the declarative part of any PL/SQL block, subprogram, or package. For example, you might define an exception named insufficient_funds to flag overdrawn bank accounts. User-defined exceptions oracle stored procedure must be given names. When an error occurs, an exception is raised. That is, normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined exceptions must be raised explicitly by RAISE statements or invocations of the procedure DBMS_STANDARD.RAISE_APPLICATION_ERROR. To handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, control returns to the host environment. For information about managing errors when using BULK COLLECT, see Handling FORALL Exceptions (%BULK_EXCEPTIONS Attribute). Example 11-1 calculates a price-to-earnings ratio for a company. If the company has zero earnings, the division operation raises the predefined exception ZERO_DIVIDE, the execution of the block is interrupted, and control is transferred to the exception handlers. The optional OTHERS handler catches all exceptions that the block does not name specifically. Example 11-1 Run-Time Error Handling DECLARE stock_price NUMBER := 9.73; net_earnings NUMBER := 0; pe_ratio NUMBER; BEGIN -- Calculation might cause division-by-zero error. pe_ratio := stock_price / net_earnings; DBMS_OU