Expanding SQL

Here’s a little thing that Dan Morgan mentioned to me some time ago. It’s a little routine from a package (owned by sys) that appeared in 11.2.0.3 that gives you some idea of the mess hidden behind a query that uses views. The procedure is called dbms_sql2.expand_sql_text and it takes two (CLOB) parameters: an IN parameter that is the text you want expanded and an OUT parameter that is likely to be a long and messy piece of text.

[Update July 2013]: The procedure is officially available in 12c in the dbms_utility package – see comment 6 below – so I’ve updated the published script to show both packages, using the “conditional compilation” features of PL/SQL to ensure that the correct package is called depending on version – and I’ve also  (redundantly) used the same mechanism to demonstrate “quote escaping”.

Rather than say any more, here’s an example of usage – you will probably have to connect as a user with dba privileges for it to work:

rem
rem     Script:         expand_sql.sql
rem     Author:         Jonathan Lewis
rem     Dated:          July 2012
rem

set linesize 70
set pagesize 0
set feedback off

declare

        $if dbms_db_version.ver_le_11_2 $then
                m_sql_in    clob :='select * from dba_synonyms where owner = ''TEST_USER''';
        $else
                m_sql_in    clob := q'{select * from dba_synonyms where owner = 'TEST_USER'}';
        $end

        m_sql_out   clob := empty_clob();
 
begin
 
        $if dbms_db_version.ver_le_11_2 $then

                dbms_output.put_line('=======================');
                dbms_output.put_line('Expanded with dbms_sql2');
                dbms_output.put_line('=======================');

                dbms_sql2.expand_sql_text(
                        m_sql_in,
                        m_sql_out
                );
 
        $else

                dbms_output.put_line('==========================');
                dbms_output.put_line('Expanded with dbms_utility');
                dbms_output.put_line('==========================');

                dbms_utility.expand_sql_text(
                        m_sql_in,
                        m_sql_out
                );
 
        $end

        dbms_output.put_line(m_sql_out);

end;
/

Note, by the way, how I’ve had to double up all the single quotes in the 11g variant, which is what makes the 12c  quote escaping so convenient.

Here’s the output from 11.2.0.3 – you might want to try this only after setting linesize to 32767, and then you can paste the result into an SQL formatter:

SELECT "A1"."OWNER" "OWNER","A1"."SYNONYM_NAME" "SYNONYM_NAME","A1"."T
ABLE_OWNER" "TABLE_OWNER","A1"."TABLE_NAME" "TABLE_NAME","A1"."DB_LINK
" "DB_LINK" FROM  (SELECT "A4"."NAME" "OWNER","A2"."NAME" "SYNONYM_NAM
E","A3"."OWNER" "TABLE_OWNER","A3"."NAME" "TABLE_NAME","A3"."NODE" "DB
_LINK" FROM "SYS"."USER$" "A4","SYS"."SYN$" "A3", (SELECT "A6"."OBJ#"
"OBJ#","A6"."DATAOBJ#" "DATAOBJ#","A6"."OWNER#" "DEFINING_OWNER#","A6"
."NAME" "NAME","A6"."NAMESPACE" "NAMESPACE","A6"."SUBNAME" "SUBNAME","
A6"."TYPE#" "TYPE#","A6"."CTIME" "CTIME","A6"."MTIME" "MTIME","A6"."ST
IME" "STIME","A6"."STATUS" "STATUS","A6"."REMOTEOWNER" "REMOTEOWNER","
A6"."LINKNAME" "LINKNAME","A6"."FLAGS" "FLAGS","A6"."OID$" "OID$","A6"
."SPARE1" "SPARE1","A6"."SPARE2" "SPARE2","A6"."SPARE3" "SPARE3","A6".
"SPARE4" "SPARE4","A6"."SPARE5" "SPARE5","A6"."SPARE6" "SPARE6","A6"."
SPARE3" "OWNER#",CASE  WHEN (("A6"."TYPE#"<>4 AND "A6"."TYPE#"<>5 AND
"A6"."TYPE#"<>7 AND "A6"."TYPE#"<>8 AND "A6"."TYPE#"<>9 AND "A6"."TYPE
#"<>10 AND "A6"."TYPE#"<>11 AND "A6"."TYPE#"<>12 AND "A6"."TYPE#"<>13
AND "A6"."TYPE#"<>14 AND "A6"."TYPE#"<>22 AND "A6"."TYPE#"<>87) OR BIT
AND("A5"."SPARE1",16)=0) THEN NULL WHEN "A5"."TYPE#"=2 THEN  (SELECT "
A9"."NAME" "NAME" FROM SYS."OBJ$" "A9" WHERE "A9"."OBJ#"="A5"."SPARE2"
) ELSE 'ORA$BASE' END  "DEFINING_EDITION" FROM SYS."OBJ$" "A6",SYS."US
ER$" "A5" WHERE "A6"."OWNER#"="A5"."USER#" AND ("A6"."TYPE#"<>4 AND "A
6"."TYPE#"<>5 AND "A6"."TYPE#"<>7 AND "A6"."TYPE#"<>8 AND "A6"."TYPE#"
<>9 AND "A6"."TYPE#"<>10 AND "A6"."TYPE#"<>11 AND "A6"."TYPE#"<>12 AND
 "A6"."TYPE#"<>13 AND "A6"."TYPE#"<>14 AND "A6"."TYPE#"<>22 AND "A6"."
TYPE#"<>87 AND "A6"."TYPE#"<>88 OR BITAND("A5"."SPARE1",16)=0 OR ("A6"
."TYPE#"=4 OR "A6"."TYPE#"=5 OR "A6"."TYPE#"=7 OR "A6"."TYPE#"=8 OR "A
6"."TYPE#"=9 OR "A6"."TYPE#"=10 OR "A6"."TYPE#"=11 OR "A6"."TYPE#"=12
OR "A6"."TYPE#"=13 OR "A6"."TYPE#"=14 OR "A6"."TYPE#"=22 OR "A6"."TYPE
#"=87) AND ("A5"."TYPE#"<>2 AND SYS_CONTEXT('userenv','current_edition
_name')='ORA$BASE' OR "A5"."TYPE#"=2 AND "A5"."SPARE2"=SYS_CONTEXT('us
erenv','current_edition_id') OR  EXISTS (SELECT 0 FROM SYS."OBJ$" "A8"
,SYS."USER$" "A7" WHERE "A8"."TYPE#"=88 AND "A8"."DATAOBJ#"="A6"."OBJ#
" AND "A8"."OWNER#"="A7"."USER#" AND "A7"."TYPE#"=2 AND "A7"."SPARE2"=
SYS_CONTEXT('userenv','current_edition_id'))))) "A2" WHERE "A2"."OBJ#"
="A3"."OBJ#" AND "A2"."TYPE#"=5 AND "A2"."OWNER#"="A4"."USER#") "A1" W
HERE "A1"."OWNER"='TEST_USER'

This expansion is probably the first step the optimizer takes in handling your code – but it’s still not necessarily the transformed text that is ultimately the “unparsed” version of your statement that you may find in the 10053 (optimizer) trace file.

Footnote

The procedure will also handle “with” subqueries – at least as far as subqueries that are in-lined is concerned. If you have “with” subqueries that materialize they will still be presented as inline views by this package but with the difference that the alias for the inline view will be the name you gave the subquery rather than a generated alias like “A1″.

The procedure is also convenient for showing you the transformation that Oracle does to convert “ANSI” style SQL to Oracle’s traditional format before optimising it.