Tip: Expanding a SQL statement

In all recent versions of the database you can call DBMS_UTILITY.EXPAND_SQL_TEXT to get the “true” version of a SQL that the database will run. It takes your SQL as input and returns a CLOB, which means its all a bit fiddly if you have a SQL statement already sitting in your SQLcl/SQLPlus buffer.

Using a trick borrowed from Tanel Poder’s pr script, here is a wrapper that makes it much simpler. Save this as expand.sql (or grab it from my git repo https://github.com/connormcd/misc-scripts)

.
set termout off
def _pr_tmpfile=x:tmppr.out
store set &_pr_tmpfile.set replace
set termout on
set serverout on size 1000000 termout off echo off
save &_pr_tmpfile replace
variable c123123 clob
set termout on
0 dbms_utility.expand_sql_text(q'
0 begin
999999 '
999999 ,:c123123); end;;
/
set long 1000000
print c123123
set termout off
@&_pr_tmpfile.set
get &_pr_tmpfile nolist
host del &_pr_tmpfile 
set termout on

Then whenever you have a SQL statement in your buffer, just lob an “@expand” onto it, for example:

SQL> select * from my_emps  -- this is a view
  2  @expand
PL/SQL procedure successfully completed.
SELECT "A1"."EMPNO" "EMPNO","A1"."ENAME" "ENAME","A1"."JOB" "JOB","A1"."MGR" "MGR","A1"."HIREDATE" "HIREDATE","A1"."SAL" "SAL","A1
"."COMM" "COMM","A1"."DEPTNO" "DEPTNO" FROM  (SELECT "A2"."EMPNO" "EMPNO","A2"."ENAME" "ENAME","A2"."JOB" "JOB","A2"."MGR" "MGR","
A2"."HIREDATE" "HIREDATE","A2"."SAL" "SAL","A2"."COMM" "COMM","A2"."DEPTNO" "DEPTNO" FROM "MCDONAC"."EMP" "A2") "A1"

Tip: Expanding a SQL statement

Rate

Share

Share

Rate