SQL*Plus error logging – workaround for ROLLBACK issue

In my previous post Oracle - SQL*Plus error logging , as a NOTE I stated an issue that whenever ROLLBACK is issued in the session, the feature fails to log the errors.
Thanks to Jacek Gebal for his blog “Oracle Thoughts”. I was really impressed by the workaround.

Workaround is simple but tricky.

Three simple steps :
1. Rename table SPERRORLOG to a new table.
1. Create a view as “SPERRORLOG”
2. Create a procedure with autonomous transaction.
3. Create an INSTEAD OF trigger to call above procedure. Instead of insert on SPERRORLOG, rather insert into the new table.

SQL> ALTER TABLE sperrorlog RENAME TO sperrorlog_new;
Table altered.

SQL> CREATE VIEW sperrorlog AS SELECT * FROM sperrorlog_new;

View created.

SQL> CREATE OR REPLACE PROCEDURE p_sperrorlog(
2       username VARCHAR2, timestamp TIMESTAMP, script VARCHAR2,
3       identifier VARCHAR2, message VARCHAR2, statement VARCHAR2
4 ) IS
5    PRAGMA AUTONOMOUS_TRANSACTION;
6 BEGIN
7    INSERT INTO sperrorlog_new
8    VALUES (username, timestamp, script, identifier, message, statement);
9    COMMIT;
10 END;
11 /

Procedure created.

SQL> SHOW ERRORS;
No errors.

SQL> CREATE OR REPLACE TRIGGER r_sperrorlog
2       INSTEAD OF INSERT ON sperrorlog FOR EACH ROW
3    CALL p_sperrorlog(
4       :NEW.username,:NEW.timestamp, :NEW.script,
5       :NEW.identifier, :NEW.message, :NEW.statement)
6 /

Trigger created.

SQL> SHOW ERRORS;
No errors.

Let’s test it and see.

SQL> show errorlogging;
errorlogging is OFF

SQL> set errorlogging on;

SQL> show errorlogging;
errorlogging is ON TABLE SPERRORLOG

SQL> insert into emp(empno) values ('abcd');
insert into emp(empno) values ('abcd')
*
ERROR at line 1:
ORA-01722: invalid number

SQL> select timestamp, script, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT

--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

03-MAY-14 06.38.42.000000 AM

ORA-01722: invalid number

SQL> ROLLBACK;

Rollback complete.

SQL> select timestamp, script, message from sperrorlog;

TIMESTAMP
---------------------------------------------------------------------------
SCRIPT

--------------------------------------------------------------------------------

MESSAGE
--------------------------------------------------------------------------------

03-MAY-14 06.38.42.000000 AM

ORA-01722: invalid number

It works! Now we could capture the errors even if ROLLBACK is issued.

WHEN OTHERS THEN NULL – A bug

A when others is almost always a BUG unless it is immediately followed by a RAISE.                    

– Quote from Thomas Kyte’s excellent thread on similar topic

Remember, for errors, RAISE –> CATCH –> HANDLE

The most common mistake that pl/sql developers often do is not handling the exceptions properly. Irrespective of the fact that the code is robust, having a weak exception handler could break the entire code and make the entire business logic prone to errors. We all know how painful it is to debug a huge piece of code just to find the line where the error occurs. Yes, it is time consuming and could be avoided most of the times if good coding practices are followed.

At first instance, why do we need an exception handler? To catch the errors, log them(optional), and finally do something about them. Isn’t it? If not, then why do we need the overhead of catching them and ultimately not doing anything?

In this post, I am restricting the scope to the ultimate bug in any pl/sql code, none other than “WHEN OTHERS THEN NULL”. Yes, it’s a bug waiting for its chance to break the atomicity of a procedural call.

Let’s see why.

A simple test case to show how WHEN OTHERS hides the actual error if not re-raised:

SQL> set serveroutput on;

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2           o_val NUMBER;
3        BEGIN
4           SELECT i_val INTO o_val FROM dual;
5           DBMS_OUTPUT.PUT_LINE(o_val);
6        EXCEPTION
7           WHEN OTHERS THEN
8              NULL;
9        END;
10      /
Procedure created

SQL> SHOW ERRORS;
No errors for PROCEDURE P_TEST_OTHERS

SQL> BEGIN
2          p_test_others('a');
3       END;
4       /
PL/SQL procedure successfully completed

So the above code did not throw any error and it says the procedure successfully completed. But, is it?

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2       o_val NUMBER;
3    BEGIN
4       SELECT i_val INTO o_val FROM dual;
5       DBMS_OUTPUT.PUT_LINE(o_val);
6    EXCEPTION
7       WHEN OTHERS THEN
8          DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);
9          DBMS_OUTPUT.PUT_LINE('Message: '||SQLERRM);
10   END;
11   /
Procedure created

SQL>
SQL> BEGIN
2       p_test_others('a');
3    END;
4    /
SQLCODE: -6502
Message: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
PL/SQL procedure successfully completed

So, we do see an error being captured. However, it is only as good as knowing that the error exists, but what needs to be done with it? If such a code is a part of any job, the job would simply complete successfully. Do you to proceed blindly with errors?

You need to re-raise the error.

SQL> CREATE OR REPLACE PROCEDURE p_test_others(i_val IN VARCHAR2) AS
2       o_val NUMBER;
3    BEGIN
4       SELECT i_val INTO o_val FROM dual;
5       DBMS_OUTPUT.PUT_LINE(o_val);
6    EXCEPTION
7       WHEN OTHERS THEN
8          DBMS_OUTPUT.PUT_LINE('SQLCODE: '||SQLCODE);
9          DBMS_OUTPUT.PUT_LINE('Message: '||SQLERRM);
10         RAISE;
11    END;
12    /
Procedure created
SQL>
SQL> BEGIN
2          p_test_others('a');
3       END;
4       /
SQLCODE: -6502
Message: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
BEGIN
p_test_others('a');
END;
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "P_TEST_OTHERS", line 10
ORA-06512: at line 2

So where to use WHEN OTHERS? Confined to the following cases :

• Error logging.
     when others
        then
        log_the_error(required_parameters);
        raise;
     end;
• To catch all other exceptions apart from the Named System Exceptions and Named Programmer-Defined Exceptions.
• Send an email regarding the error captured(Might be helpful during development stage or unit testing).
• To close all the opened resources such as Cursors, files etc.

ORA-06503: PL/SQL: Function returned without value

An important thing regarding function, you would agree with me that, at least once a PL/SQL developer must have heard that “A function MUST ALWAYS RETURN a VALUE of proper datatype”. Having been said that a million times on various platforms, still developers make this mistake.

ORA-06503: PL/SQL: Function returned without value
Cause: A call to PL/SQL function completed, but no RETURN statement was executed.
Action: Rewrite PL/SQL function, making sure that it always returns a value of a proper type.

DB version : 11.2.0.2.0

Let’s see the various scenarios of this error :

Without a RETURN statement in the function body and without exception handler(most stupid way):


SQL> set serveroutput on;

SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8 END;
9 /
Function created

SQL> select f_test(100) from dual;
select f_test(100) from dual
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "F_TEST", line 8

Now, in the above code, the mathematical logic was correct, hence there was no SQL error to override the PL/SQL error. Let’s see how ORA-01476 will override the ORA-06503 error.


SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8 END;
9 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-01476: divisor is equal to zero
ORA-06512: at "F_TEST", line 5

Well, that’s quite obvious, isn’t it?

2.  Without a RETURN statement in the exception handler(most common mistake) :


SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8
9 RETURN o_val;
10
11 EXCEPTION
12 WHEN OTHERS THEN
13 NULL;
14 END;
15 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "F_TEST", line 14


—————————————-/ OFF TOPIC /—————————————-

This is somewhat important to share.

EXCEPTION WHEN OTHERS THEN NULL;
–> is itself a bug in the code waiting for its chance to break the code.

At least a good developer would remember that WHEN OTHERS should be always followed by a RAISE. Re-raising the error would show us the root cause, rather than the confusing “ORA-06503: PL/SQL: Function returned without value” error.


SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8
9 RETURN o_val;
10
11 EXCEPTION
12 WHEN OTHERS THEN
13 NULL;
14 RAISE;
15 END;
16 /
Function created

SQL> select f_test(0) from dual;
select f_test(0) from dual
ORA-01476: divisor is equal to zero
ORA-06512: at "F_TEST", line 14


—————————————-/ OFF TOPIC /—————————————-

Now let’s put a RETURN statement at required places and the code should work fine without any error :



SQL> CREATE OR REPLACE FUNCTION f_test(i_val NUMBER)
2 RETURN NUMBER AS
3 o_val NUMBER;
4 BEGIN
5 SELECT 100 / i_val
6 INTO o_val
7 FROM DUAL;
8
9 RETURN o_val;
10
11 EXCEPTION
12 WHEN OTHERS THEN
13 DBMS_OUTPUT.PUT_LINE('Came inside Exception handler');
14 RETURN 0;
15 END;
16 /
Function created

SQL> select f_test(0) from dual;

F_TEST(0)
---------
0

Came inside Exception handler

Bottom line is that :

• A function MUST ALWAYS RETURN a value of proper datatype, no matter from the body or exception.
• We must do something with the error not just return junk. We must RAISE/log error and handle it, do something about the error so that underlying process has no impact.
• Lastly, not to forget, EXCEPTION WHEN OTHERS THEN NULL; –> is itself a bug in the code waiting for its chance to break the code.

Oracle – Insert Comma separated string values into Table

This is a small demonstartion of how to insert the values of a comma separated string to a table as different rows. This is not something new, Tom Kyte has already demonstrated it lot of times in AskTom. However, I just want to keep it simple, just to insert values of a comma separated string into a table.

Let’s see the demo :


SQL> SET SERVEROUTPUT ON;
SQL>
SQL> DROP TABLE TEST
2 /

Table dropped
SQL> CREATE TABLE TEST (COL1 VARCHAR2(10))
2 /

Table created
SQL> DECLARE
2 L_INPUT VARCHAR2(4000) := 'THIS,IS,A,COMMA,SEPARATED,STRING';
3 L_COUNT BINARY_INTEGER;
4 L_ARRAY DBMS_UTILITY.LNAME_ARRAY;
5 BEGIN
6 DBMS_UTILITY.COMMA_TO_TABLE(LIST => REGEXP_REPLACE(L_INPUT,
7 '(^|,)',
8 '\1x'),
9 TABLEN => L_COUNT,
10 TAB => L_ARRAY);
11 DBMS_OUTPUT.PUT_LINE(L_COUNT);
12 FOR I IN 1 .. L_COUNT LOOP
13 DBMS_OUTPUT.PUT_LINE('Element ' || TO_CHAR(I) ||
14 ' of array contains: ' || SUBSTR(L_ARRAY(I), 2));
15 INSERT INTO TEST VALUES (SUBSTR(L_ARRAY(I), 2));
16 COMMIT;
17 END LOOP;
18 END;
19 /

6
Element 1 of array contains: THIS
Element 2 of array contains: IS
Element 3 of array contains: A
Element 4 of array contains: COMMA
Element 5 of array contains: SEPARATED
Element 6 of array contains: STRING

PL/SQL procedure successfully completed
SQL> SELECT * FROM TEST
2 /

COL1

THIS
IS
A
COMMA
SEPARATED
STRING

6 rows selected

SQL>

Hope it helps!

How to generate trace file – SQL Trace and TKPROF in Oracle

It is a frequently asked question in almost all the Oracle forums. There have had been numerous questions/posts regarding “But how to generate the trace file?” Well, it might seem a heck of a task, however, looking it step by step will make you understand that it is actually not that difficult.

Usually, database application developers do not have the required/necessary permissions/privileges to do all the steps that I will mention below. However, most of the steps could be done by application developer. A few steps will need a DBA privilege.

Let me take you through the steps :

Turning the tracing event ON with a proper level

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12'; 

The different levels of tracing event:-

0 – No trace. Like switching sql_trace off.

2 – The equivalent of regular sql_trace.

4 – The same as 2, but with the addition of bind variable values.

8 – The same as 2, but with the addition of wait events.

12 – The same as 2, but with both bind variable values and wait events.

Giving a proper name/number for the trace file identifier

 SQL> alter session set tracefile_identifier = 'test_plan1';

 Execute the SQL query for which you want the trace

 SELECT col1, col2…..FROM t1, t2…..WHERE…..;

 Turning the tracing event OFF

 SQL> alter session set events '10046 trace name context off';

 Finding the directory where the trace files are generated(this is the directory which is in “init.ora” file) 

SQL> SHOW PARAMETER user_dump_dest;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

user_dump_dest                       string      /opt/oracle/diag/rdbms/lalit/trace

 Finding the trace file in the UNIX directory 

lalit@Orcl:DEV$ cd /opt/oracle/diag/rdbms/lalit/trace

lalit@Orcl:/opt/oracle/diag/rdbms/lalit/trace DEV$ ls -lrt *test_plan1.trc

-rw-r—–    1 oracle   dba         1076036 Jan 01 06:46 lalit_ora_30539942_test_plan1.trc

-rw-r—–    1 oracle   dba         1903274 Jan 01 06:51 lalit_ora_33030344_test_plan1.trc

So now we have the trace file, just give the trace file name to DBA to get the tkprof output and take read access on tkprof.out file

lalit@Orcl:/opt/oracle/diag/rdbms/lalit/trace DEV$ls -lrt *.out

-rw-r–r–    1 oracle   dba           17273 Jan 01 06:52 tkprof.out

 Analyze the tkprof.out file to find the issue with the SQL query.

From 10g onwards all the tracing options have been centralized into DBMS_MONITOR package. You can look for the package in docshttp://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_monitor.htm