Posted by 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)
Learning is not a spectator sport 
Nice!
But I don’t think that auditors and the red tape department would allow this on a production environment.
why is “alter session set events = ‘10601 trace name context forever, level 32’;” is needed? What does it do.
My understanding of the event is: SQL translation is intended for non-Oracle apps coming into the database. The event flicks a switch to let the translator know that even though we’re on an Oracle app (sqlplus) on a Oracle client running Oracle SQL 🙂 that we still want to do some translation.
Yes this is true.. We need this event to “test” profile in sqlplus.
Interesting post, useful for cases where a change to the application to fix the SQL just takes too long.
I immediately considered if this could solve my current grievance with dbms_advanced_rewrite. After a quick check, it works when there’s an order by clause in a select statement, hurray!
It’s a little annoying that explain plan doesn’t tell you about the rewrite but I suppose that’s because it’s a solution for migrations rather than a performance tool.
Execution plan still shows the query rewrites. you can see a test case for that at below link.
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:9531536600346185281#9531885900346341582
Something similar for SELECT only) could be possible with DBMS_ADVANCED_REWRITE, e.g. change the SELECT statement completely or partially, but there are more restriction (e.g. agg=gregate does not work etc..).
Jan
If everyone would like to see how to use SQL Translation Framework and change select statement into delete statement please read my blog post.. I’m just dig in SQL TF little deeper http://blog.tiento.pl/sql-translation-framework/