2014-01-01 | 2 Comments Recently I’m heavily involved in SQL*Plus scripting automation tasks. I must admit that despite all the settings you may set and retrieve with SQL*Plus, it is a real pain to do some good automation with this tool. I’m very glad that Oralce continues to extend Oracle SQL*Plus features, but i must say that it seems like the design details for those new features are not well thought, which gives me a headache when working with this tool and forces me to look for tricks and workarounds for badly designed functionality. In this post I’ll write about the downsides of the ERRORLOGGING feature introduced with SQL*Plus 11.1 and above. The feature itself is described in the Oracle documentation, so i will not reedit it here. For purposes of the post i’m using the Oracle sample schema HR. The SET ERRORLOGGING ON command however will not give you the expected results in some cases. Follow the examples to see the ERRORLOGGING fail to log errors into errors table. Lets first create the error logging table. CREATE TABLE sperrorlog( username VARCHAR(256), timestamp TIMESTAMP, script VARCHAR(1024), identifier VARCHAR(256), message CLOB, statement CLOB ); Once done, we can see it’s working with the following script. SET ERRORLOGGING ON INSERT INTO bad_table(a) VALUES(1); SET ERRORLOGGING OFF SELECT timestamp, script, substr(message,1,100) message FROM sperrorlog;[/sql] The output below proves the errorlogging to work. SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 1 22:48:33 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Use "connect username/password@XE" to connect to the database. SQL> conn hr/hr Connected. SQL> SET ERRORLOGGING ON SQL> INSERT INTO bad_table(a) VALUES(1); INSERT INTO bad_table(a) VALUES(1) * ERROR at line 1: ORA-00942: table or view does not exist SQL> SET ERRORLOGGING OFF SQL> SELECT timestamp, script, substr(message,1,100) message FROM sperrorlog; TIMESTAMP --------------------------------------------------------------------------- SCRIPT -------------------------------------------------------------------------------- MESSAGE -------------------------------------------------------------------------------- 01-JAN-14 10.49.02.000000 PM ORA-00942: table or view does not exist SQL> So far so good. Now it’s time to show where it fails my expectations. Lets try the above script with a small modification. SET ERRORLOGGING ON INSERT INTO bad_table(a) VALUES(1); SET ERRORLOGGING OFF ROLLBACK; SELECT timestamp, script, substr(message,1,100) message FROM sperrorlog; SQL*Plus: Release 11.2.0.2.0 Production on Wed Jan 1 23:22:20 2014 Copyright (c) 1982, 2011, Oracle. All rights reserved. Use "connect username/password@XE" to connect to the database. SQL> conn hr/hr Connected. SQL> truncate table sperrorlog; Table truncated. SQL> SET ERRORLOGGING ON SQL> INSERT INTO bad_table(a) VALUES(1); INSERT INTO bad_table(a) VALUES(1) * ERROR at line 1: ORA-00942: table or view does not exist SQL> SET ERRORLOGGING OFF SQL> ROLLBACK; Rollback complete. SQL> SELECT timestamp, script, substr(message,1,100) message FROM sperrorlog; no rows selected SQL> The error logging is not using autonomous transaction to log the error that occurs. You will not be able to log errors using ERRORLOGGING command if your script is performing ROLLBACK operations. Oracle guys did a really sloppy work by not implementing the ERRORLOGGING feature in autonomous transaction. If your scripts are using ERRORLOGGING along with ROLLBACK they will not work as expected. Now here is a real-life example. Consider that you have a script that is supposed to Update and Insert some data in EMPLOYEES table. The script is supposed to log all the errors that occur. The script is also supposed to exit with failure exit code and rollback all the changes done if an error occurred. WHENEVER SQLERROR EXIT FAILURE ROLLBACK; SET ERRORLOGGING ON; REM Start processing changes UPDATE EMPLOYEES SET SALARY = SALARY * 1.1; INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER, HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID) VALUES (100,'Steven','King','SKING','515.123.4567', to_date('2003-06-17 00:00:00','YYYY-MM-DD HH24:MI:SS'), 'AD_PRES',24000,null,null,90); SET ERRORLOGGING OFF; REM Finished processing changes EXIT SUCCESS COMMIT; The script is written to fail on the insert statement. Unfortunately, there is no trace of the failure in the ERRORLOG table, as we requested to ROLLBACK all changes on SQLERROR. See the solution I’ve found for this issue.
I have a script myscript.sql : WHENEVER SQLERROR EXIT SQL.SQLCODE DECLARE l_cnt NUMBER; BEGIN SELECT INTO l_cnt FROM user_tab_columns t WHERE t.table_name = ‘IVN_TB_COVERSHEET_IMAGE’ AND t.COLUMN_NAME =’EAM_ORGUNIT’; raise_application_error( -20001, ‘Column to this particular table is already added.’); END; / I run this script from a shell script : shell script has code : ;sqlplus -S /nolog <<! set errorlogging on truncate @myscript.sql set errorlogging off exit 0 ! code=$? echo $code When I run the shell script , it returns exit code 0 , if I remove the 'set errorlogging' part from shell script , it returns code 33. Can you please explain this behavior . Reply
Hi. The script you’ve provided contains errors, however, after fixes I’ve tested it and didn’t get the issue you’re getting. I keep getting return code = 33 even if I use errorologging. I’ve tested it in a bit different way: Shell script: sqlplus -S /nolog <<! conn $db_user/$db_pass set errorlogging on truncate @myscript.sql set errorlogging off exit 0 ! code=$? echo $code SQL script: WHENEVER SQLERROR EXIT SQL.SQLCODE DECLARE l_cnt NUMBER; BEGIN SELECT COUNT(1) INTO l_cnt FROM user_tab_columns t WHERE t.table_name='IVN_TB_COVERSHEET_IMAGE' AND t.COLUMN_NAME ='EAM_ORGUNIT'; raise_application_error( -20001, 'Column to this particular table is already added.'); END; / What version of SQL Plus are you using? What version of database are you using? Reply