Skip to Main Content
  • Questions

  • Is there a way to force DML error logging without adding LOG ERRORS INTO clause to INSERT, UPDATE etc?

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Stephen.

Asked: October 13, 2016 - 1:07 pm UTC

Last updated: November 02, 2016 - 10:26 pm UTC

Version: 11g

Viewed 1000+ times

You Asked

For INSERT statements run against a table with an associated DML error log table, I would like to force DML error logging for INSERT statements that do not include a LOG ERRORS INTO clause.

For Example:
CREATE TABLE "WSB"."TEST"
("CCHAR" VARCHAR2(1 BYTE) NOT NULL ENABLE
);

BEGIN
dbms_errlog.create_error_log
(
dml_table_name => 'TEST'
);
END;

Running the below insert statement in SQLDeveloper:
INSERT INTO WSB.TEST VALUES('ABC') LOG ERRORS INTO WSB.ERR$_TEST (TO_CHAR(SYSTIMESTAMP));
Gives script output:
Error starting at line : 1 in command -
INSERT INTO WSB.TEST VALUES('ABC') LOG ERRORS INTO WSB.ERR$_TEST (TO_CHAR(SYSTIMESTAMP))
Error report -
SQL Error: ORA-12899: value too large for column "WSB"."TEST"."CCHAR" (actual: 3, maximum: 1)

The WSB.ERR$_TEST table contains:
ORA_ERR_NUMBER$ 12899
ORA_ERR_MESG$ "ORA-12899: value too large for column ""WSB"".""TEST"".""CCHAR"" (actual: 3, maximum: 1)"
ORA_ERR_ROWID$
ORA_ERR_OPTYP$ I
ORA_ERR_TAG$ 13-OCT-16 08.55.25.554291 AM -04:00
CCHAR ABC


If I run the same insert statement in SQLDeveloper (without the LOG ERRORS INTO clause):
INSERT INTO WSB.TEST VALUES('ABC');
Gives script output:
Error starting at line : 1 in command -
INSERT INTO WSB.TEST VALUES('ABC')
Error report -
SQL Error: ORA-12899: value too large for column "WSB"."TEST"."CCHAR" (actual: 3, maximum: 1)
But in the above case no rows are added to the WSB.ERR$_TEST

My question is there some way to force INSERT statements to use DML error logging even if the INSERT statement lacks a LOG ERRORS INTO clause?

Thanks!

and Chris said...

No. If you want to use DML error logging, you need to include the "log errors into" clause.

Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

*POOF* It's Magic! Subtitle: can we use either DBMS_ADVANCED_REWRITE or DBMS_SQL_TRANSLATOR

Duke Ganote, October 28, 2016 - 3:46 pm UTC

I was intrigued by the digression here:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9532113900346871379
Which led me to wonder:
1. What's the difference between DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR ?
2. Could either be used to add an "under the covers" DML error logging clause as requested by OP?
Connor McDonald
October 30, 2016 - 9:47 am UTC

1)

dbms_sql_translater is just the "tip of the iceberg". SQL translation also includes a suite of *automatic* tranlsations, eg,

http://docs.oracle.com/database/121/DRDAA/sql_transl_install.htm#DRDAA29156

2) Yep...nice idea, and seems to work :-)


SQL> drop user demo cascade;

User dropped.

SQL> create user demo identified by demo quota 100m on users;

User created.

SQL> grant create session, alter session, create table, create sql translation profile, create procedure to DEMO;

Grant succeeded.

SQL>
SQL> conn demo/demo
Connected.

SQL> create table emp ( x varchar(10));

Table created.

SQL> exec dbms_errlog.create_error_log ( dml_table_name => 'EMP' );

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> exec DBMS_SQL_TRANSLATOR.DROP_PROFILE(profile_name    => 'DEMO_PROFILE'); exception when others then null;

PL/SQL procedure successfully completed.

SQL>
SQL> exec dbms_sql_translator.create_profile('DEMO_PROFILE');

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2      DBMS_SQL_TRANSLATOR.REGISTER_SQL_TRANSLATION(
  3        profile_name    => 'DEMO_PROFILE',
  4        sql_text        => 'INSERT INTO EMP VALUES (''QBCDEFGHIJKKKKK'')',   -- trailing space deliberate to align with PLSQL
  5        translated_text => 'INSERT INTO EMP VALUES (''QBCDEFGHIJKKKKK'') LOG ERRORS INTO ERR$_EMP (TO_CHAR(SYSTIMESTAMP))');
  6  END;
  7  /

PL/SQL procedure successfully completed.

SQL>
SQL> conn system/admin
Connected.

SQL> alter system flush shared_pool;

System altered.

SQL> conn demo/demo
Connected.

SQL> INSERT INTO EMP VALUES ('QBCDEFGHIJKKKKK');
INSERT INTO EMP VALUES ('QBCDEFGHIJKKKKK')
                        *
ERROR at line 1:
ORA-12899: value too large for column "DEMO"."EMP"."X" (actual: 15, maximum: 10)


SQL> select count(*) from err$_emp;

  COUNT(*)
----------
         0

SQL>
SQL> alter session set sql_translation_profile = DEMO_PROFILE;

Session altered.

SQL> alter session set events = '10601 trace name context forever, level 32';

Session altered.

SQL>
SQL> INSERT INTO EMP VALUES ('QBCDEFGHIJKKKKK');
INSERT INTO EMP VALUES ('QBCDEFGHIJKKKKK')
                        *
ERROR at line 1:
ORA-12899: value too large for column "DEMO"."EMP"."X" (actual: 15, maximum: 10)


SQL> select count(*) from err$_emp;

  COUNT(*)
----------
         1

SQL>
SQL>

Neat! Treat! and Trickery

Duke Ganote, October 31, 2016 - 3:14 pm UTC

I've tried, but never needed DBMS_ADVANCED_REWRITE (thus far). I found it wicked fun for Halloween trick-or-treat... :)
Chris Saxon
October 31, 2016 - 5:06 pm UTC

It's certainly a great way to wind up your colleagues...

difference between DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR

Rajeshwaran Jeyabal, November 01, 2016 - 11:29 am UTC

....
What's the difference between DBMS_ADVANCED_REWRITE and DBMS_SQL_TRANSLATOR ?
....

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:9531536600346185281#9531834500346476920

Error message on console after "profile" set ?!?!?!?!

Rajeshwaran Jeyabal, November 01, 2016 - 11:40 am UTC 

SQL> alter session set sql_translation_profile = DEMO_PROFILE;

Session altered.

SQL> alter session set events = '10601 trace name context forever, level 32';

Session altered.

SQL>
SQL> INSERT INTO EMP VALUES ('QBCDEFGHIJKKKKK');
INSERT INTO EMP VALUES ('QBCDEFGHIJKKKKK')
                        *
ERROR at line 1:
ORA-12899: value too large for column "DEMO"."EMP"."X" (actual: 15, maximum: 10)


SQL> select count(*) from err$_emp;

  COUNT(*)
----------
         1

SQL>


When i tried to run this test script on my local, i don't get that error message on console after the profile is set in the session.

demo@ORA12C> create table emp (x varchar2(10));

Table created.

demo@ORA12C> exec dbms_errlog.create_error_log('EMP');

PL/SQL procedure successfully completed.

demo@ORA12C> exec dbms_sql_translator.drop_profile(profile_name=>'DEMO_PROFILE');

PL/SQL procedure successfully completed.

demo@ORA12C> exec dbms_sql_translator.create_profile(profile_name=>'DEMO_PROFILE');

PL/SQL procedure successfully completed.

demo@ORA12C> begin
  2     dbms_sql_translator.register_sql_translation(
  3             profile_name=>'DEMO_PROFILE',
  4             sql_text=>q'|insert into emp(x) values('QBCDEFGHIJKKKKK')|',
  5             translated_text=>q'|insert into emp(x) values('QBCDEFGHIJKKKKK') log errors into err$_emp reject limit unlimited|');
  6  end;
  7  /

PL/SQL procedure successfully completed.

demo@ORA12C> @printtbl "select * from err$_emp"

PL/SQL procedure successfully completed.

demo@ORA12C> alter session set sql_translation_profile=DEMO_PROFILE;

Session altered.

demo@ORA12C> alter session set events = '10601 trace name context forever, level 32';

Session altered.

demo@ORA12C> insert into emp(x) values('QBCDEFGHIJKKKKK');

0 rows created.

demo@ORA12C> @printtbl "select * from err$_emp"
ORA_ERR_NUMBER$               : "12899"
ORA_ERR_MESG$                 : "ORA-12899: value too large for column "DEMO"."EMP"."X" (actual: 15, maximum: 10)
"
ORA_ERR_ROWID$                : ""
ORA_ERR_OPTYP$                : "I"
ORA_ERR_TAG$                  : ""
X                             : "QBCDEFGHIJKKKKK"
-----------------

PL/SQL procedure successfully completed.

demo@ORA12C>

Connor McDonald
November 02, 2016 - 10:26 pm UTC

Might be a cut/paste fail on my part
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

DBMS_ERRLOG

More on PL/SQL routine DBMS_ERRLOG here