Code enhancements without changing code

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)

6 thoughts on “Code enhancements without changing code

    • 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.

  1. 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.

  2. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s