Today i’ve something interesting which i implemented in my project, like to share the same.
This about error logging mechanism for Pl/sql code in the custom Error table.
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
The error code. This code is useful when you need to look up generic information about what might cause such a problem.
How to get? –> SQLCODE
The error message. This text often contains application-specific data such as the name of the constraint or the column associated with the problem.
How to get?–>SQLERRM (commonly used) but from Oracle 10G release we have an advance new feature called DBMS_UTILITY.FORMAT_ERROR_STACK. Ok? What is the difference?
Yes the difference is if you use SQLERRM there is a possibility of truncate of the error message by oracle. But if we use the other it doesn’t , so if you are using 10G+ use the above.
The line on which the error occurred. This capability was added in Oracle Database 10g Release 2 and is enormously helpful in tracking down the cause of errors.
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
The execution call stack. This answers the question “How did I get here?” and shows you the path through your code to the point at which DBMS_UTILITY.FORMAT_CALL_STACK is called.
DBMS_UTILITY.FORMAT_CALL_STACK
Now we will see small example
Recording errors. Suppose something’s gone wrong in your application and an exception was raised. You can certainly just let that exception propagate unhandled all the way out to the user, by not writing any exception sections in your subprograms. Users will then see the error code and message and either report the problem to the support team or try to fix the problem themselves.
In most cases, however, you’d like to store the information about the error before it is communicated to the user. That way you don’t have to rely on your users to give you information such as the error code or the error message.
When you record your error, you should include the information shown in Table, all obtainable through calls to functions supplied by Oracle Database. All of this information will help a developer or a member of the support team diagnose the cause of the problem. You may, in addition, want to record values of application-specific data, such as variables or column values.
If you decide to store your error information in a table, you should not put the INSERT statements for the error log table directly inside your exception. Instead, you should build and call a procedure that does this for you. This process of “hiding” the way you implement and populate your log will make it easier and more productive to log errors.
To understand these advantages, let’s build a simple error log table and try using it in my exception section. Suppose my error log table looks like this:
CREATE TABLE APPS.XX_SECURITY_ERROR_LOG
(
ERROR_ID NUMBER(15) NOT NULL,
ERROR_CODE VARCHAR2(100 BYTE),
ERROR_NAME VARCHAR2(100 BYTE),
CREATED_BY NUMBER(15),
CREATION_DATE DATE,
FUNCTION_NAME VARCHAR2(100 BYTE),
BACKTRACE CLOB,
CALLSTACK CLOB
)
Exception Handling Procedure
CREATE OR REPLACE PROCEDURE APPS.test_Ram(v_empid number)
IS
lv_ename VARCHAR2(100);
lv_err_msg VARCHAR2 (1000);
lv_err_code VARCHAR2(1000);
lv_seq number;
begin
select ename into lv_ename from emp where empno=v_Empid;
dbms_output.put_line(‘Emp name’||’,’||lv_ename);
EXCEPTION
WHEN OTHERS THEN
lv_err_code:= SQLCODE;
lv_err_msg := SQLERRM;
select xx_security_error_log_s.nextval into lv_seq from dual;
xx_sec_err_log(lv_seq,lv_err_code,
sys.DBMS_UTILITY.format_error_stack,
1,sysdate,’TEST’,
sys.DBMS_UTILITY.format_error_backtrace,
sys.DBMS_UTILITY.format_call_stack);
end;
/
begin
test_ram(1001);
end;
By declaring the procedure to be an autonomous transaction, I can commit or roll back any of the changes I make to tables inside this procedure without affecting other changes made in my session. So I can now save the new row in my error log, and a later rollback of the business transaction will not wipe out this information.
Conclusion
PL/SQL provides a wide range of features to help you catch and diagnose errors as well as communicate application-specific errors to your users. The exception section makes it easy to centralize all your exception handling logic and thereby manage it more effectively.
Thats it for the day!!
Happy Coding 🙂
S Ramkumar 