An interesting suggestion came through on AskTom this week, which prompted the following exploration.
Let us assume you are populating a table with
INSERT INTO EMP SELECT * FROM SOURCE_EMPS
and it falls over with
ORA-12899: value too large for column
To capture the data that caused that error is not trivial. Of course, we could edit the code to perform DML error logging but of course, that means changing the source code, which means change control, testing, red tape etc etc etc. Is there a better way ? Perhaps there is.
In 12c, there is the SQL translation framework, designed to work for those applications being migrated from one database platform to Oracle. We intercept incoming SQL statements in (say) SQL Server syntax format, and recast it on the fly to an equivalent Oracle syntax. But we can also take advantage of that to handle this data error issue. Here’s a demo of that in action:
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> create table source_emps as
2 select cast(rpad('E',rownum,'E') as varchar2(20)) x_in
3 from dual connect by level <= 15; Table created. SQL> select * from source_emps;
X_IN
--------------------
E
EE
EEE
EEEE
EEEEE
EEEEEE
EEEEEEE
EEEEEEEE
EEEEEEEEE
EEEEEEEEEE
EEEEEEEEEEE
EEEEEEEEEEEE
EEEEEEEEEEEEE
EEEEEEEEEEEEEE
EEEEEEEEEEEEEEE
15 rows selected.
So we have an EMP table with 10byte column ”X”, and some source rows from SOURCE_EMPS with ever increasingly larger sizes for “X_IN”. So once our rows get larger than 10 bytes, we are going to have a problem inserting them into EMP.
SQL> INSERT INTO EMP SELECT * FROM SOURCE_EMPS;
INSERT INTO EMP SELECT * FROM SOURCE_EMPS
*
ERROR at line 1:
ORA-12899: value too large for column "DEMO"."EMP"."X" (actual: 11, maximum: 10)
So let us now intercept that SQL using the translation framework.
SQL>
SQL> begin
2 dbms_sql_translator.drop_profile(profile_name => 'DEMO_PROFILE');
3 exception when others then null;
4 end;
5 /
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 SELECT * FROM SOURCE_EMPS',
5 translated_text => 'INSERT INTO EMP SELECT * FROM SOURCE_EMPS LOG ERRORS INTO ERR$_EMP (TO_CHAR(SYSTIMESTAMP)) REJECT LIMIT UNLIMITED');
6 END;
7 /
PL/SQL procedure successfully completed.
So we can create an error logging table, and activate our translation profile
SQL> exec dbms_errlog.create_error_log ( dml_table_name => 'EMP' );
PL/SQL procedure successfully completed.
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 SELECT * FROM SOURCE_EMPS;
10 rows created.
Notice now that our insert was “successful” because we are now capturing the errors.
SQL> select count(*) from err$_emp;
COUNT(*)
----------
5
SQL>
SQL> select ORA_ERR_MESG$ from err$_emp;
ORA_ERR_MESG$
---------------------------------------------------------------------------------
ORA-12899: value too large for column "DEMO"."EMP"."X" (actual: 11, maximum: 10)
ORA-12899: value too large for column "DEMO"."EMP"."X" (actual: 12, maximum: 10)
ORA-12899: value too large for column "DEMO"."EMP"."X" (actual: 13, maximum: 10)
ORA-12899: value too large for column "DEMO"."EMP"."X" (actual: 14, maximum: 10)
ORA-12899: value too large for column "DEMO"."EMP"."X" (actual: 15, maximum: 10)
