Expanding SQL

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 dbms_sql2.expand_sql_text and 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. Rather than say any more, here’s an example of usage:

set linesize 70
set pagesize 0
set feedback off

declare
 m_sql_in clob :='select * from dba_synonyms where owner = ''TEST_USER''';
 m_sql_out clob := empty_clob();

begin

 dbms_sql2.expand_sql_text(
  m_sql_in,
  m_sql_out
 );

 dbms_output.put_line(m_sql_out);
end;
/

Notice how I’ve doubled up all the single quotes.

Here’s the output – 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${body}quot; "A4","SYS"."SYN${body}quot; "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${body}quot; "OID${body}quot;,"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${body}quot; "A9" WHERE "A9"."OBJ#"="A5"."SPARE2"
) ELSE 'ORA$BASE' END  "DEFINING_EDITION" FROM SYS."OBJ${body}quot; "A6",SYS."US
ER${body}quot; "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${body}quot; "A8"
,SYS."USER${body}quot; "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 the transformed text that is ultimately the “unparsed” version of your statement.



DIGITAL JUICE