Fun with SQL Translation Framework – By-Passing Parse Errors

Invalid SQL being sent to the database is something to watch out for,
it’s a real performance killer because once SQL is rejected by parser,
it is not cached in shared pool, resulting in hard parse every time the SQL is encountered.

One cool new feature of Oracle 12.2 is the fact that such SQL parse errors are automatically logged to the alert log (by default every 100 occurrences of a particular SQL)

(Random thoughts, the database must be storing these invalid SQL somewhere to keep track of the parse error count, I wonder where that is? I guess though that even though it has stored cached the statement in it’s “Invalid SQL” list it will still have to re-parse every time it’s encountered as the statement may become valid if say a table it relied on got created).  (Edit: 2018-10-10 As is often the case, Jonathan Lewis seems to have the answer to this)

A similar effect can be achieved on previous versions by setting event 10035.

One instance that I’ve been monitoring has regular occurrences of the following SQL:

SELECT INVALID SELECT STATEMENT TO FORCE ODBC DRIVER TO UNPREPARED STATE

A quick google returns lots of hits, but no real solution. Oracle Support bug 8469553 also has some clues. Basically it seems to be a problem with older versions of ODBC, and likely solution is to upgrade ODBC version.

However I was thinking about a short-term fix, and recalled a presentation from Kerry Osborne regarding SQL Translation Framework to transform one SQL statement to another, and wondered whether I could transform this invalid statement to a valid one?

To my surprise it worked, as I demonstrate below:

SQL> begin
  2     dbms_sql_translator.create_profile('odbc_profile');
  3     dbms_sql_translator.register_sql_translation( profile_name => 'odbc_profile',
  4                                                   sql_text => 'SELECT INVALID SELECT STATEMENT TO FORCE ODBC DRIVER TO UNPREPARED STATE',
  5                                                   translated_text => 'SELECT DUMMY FROM DUAL');
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> alter session set sql_translation_profile=odbc_profile;

Session altered.

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

Session altered.

SQL> SELECT INVALID SELECT STATEMENT TO FORCE ODBC DRIVER TO UNPREPARED STATE;
D
-
X

SQL>

Now I’m not sure if this has any knock on effects on the application in question, but at least goes to show a usage of the SQL Translation Framework that I hadn’t seen or considered before.

I’m sure there are many more.