[20211123]完善expand sql text.txt

[20211123]完善expand sql text.txt --//在整理自己以前写的一些脚本，发现tpt下也有一个类似脚本。  $ cat expandlast.sql . -- Copyright 2020 Tanel Poder. All rights reserved. More info at https://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. COL outsql FOR A100 WORD_WRAP VAR outsql CLOB 0 c clob := q'\ 0 declare 999999      \';; 999999 begin 999999     dbms_utility.expand_sql_text(c, :outsql);; 999999 end;; / PRINT outsql --//11g版本要调用dbms_sql2.expand_sql_text。12c以上才是执行dbms_utility.expand_sql_text. --//如果以上脚本在11g上执行一定报错。 --//如果修改如下，加入参数1输入： $ cat expandlast.sql . -- Copyright 2020 Tanel Poder. All rights reserved. More info at https://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. COL outsql FOR A100 WORD_WRAP VAR outsql CLOB 0 c clob := q'\ 0 declare 999999      \';; 999999 begin 999999     --dbms_utility.expand_sql_text(c, :outsql);; 999999     dbms_&&1..expand_sql_text(c, :outsql);; 999999 end;; / PRINT outsql --//这样输入参数1=sql2,就可以在11g执行，输入参数1=utility就可以在12c以上执行。 SCOTT@book> @ ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production SCOTT@book> select sysdate  from dual ; SYSDATE ------------------- 2021-11-23 15:31:33 SCOTT@book> @ expandlast.sql sql2 PL/SQL procedure successfully completed. OUTSQL ---------------------------------------------------------------------------------------------------- SELECT SYSDATE "SYSDATE" FROM "SYS"."DUAL" "A1" --//很明显要记住输入参数sql2 ，utility 。还是很不方便。如果输入参数11，自动变为sql2，就容易记住也方便使用。 --//我改写如下，不行!! $ cat expand.sql . -- Copyright 2020 Tanel Poder. All rights reserved. More info at https://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. COL outsql FOR A100 WORD_WRAP VAR outsql CLOB column arg1 new_value arg1 0 c clob := q'\ 0 declare 999999      \';; 0 select decode(&1,11,'sql2','utility') arg1 from dual ;; 999999 begin 999999     --dbms_utility.expand_sql_text(c, :outsql);; 999999     dbms_&&arg1..expand_sql_text(c, :outsql);; 999999 end;; / PRINT outsql --//注：0 select decode(&1,11,'sql2','utility') arg1 from dual ;; 不能写在前面，这样输出的是这条语句。 SCOTT@book> @ expand  11 select decode(11,11,'sql2','utility') arg1 from dual;                                                     * ERROR at line 1: ORA-00911: invalid character SP2-1504: Cannot print uninitialized LOB variable "OUTSQL" --//调用ed打开，看到内容如下： select decode(&1,11,'sql2','utility') arg1 from dual; declare c clob := q'\ select * from emp      \'; begin     --dbms_utility.expand_sql_text(c, :outsql);     dbms_&&arg1..expand_sql_text(c, :outsql); end; / --//放弃！视乎这样必须要在declare里面才行，单独保存不报错。我可以写成如下： $ cat expand.sql . -- Copyright 2020 Tanel Poder. All rights reserved. More info at https://tanelpoder.com -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions. COL outsql FOR A100 WORD_WRAP VAR outsql CLOB 0 c clob := q'\ 0 declare 999999      \';; 999999 begin 999999     &&1 dbms_utility.expand_sql_text(c, :outsql);; 999999     &&2 dbms_sql2.expand_sql_text(c, :outsql);; 999999 end;; / PRINT outsql --//通过控制参数1,参数2实现，比如： SCOTT@book> select sysdate  from Dual ; SYSDATE ------------------- 2021-11-23 17:45:21 SCOTT@book> @ expand  -- "" PL/SQL procedure successfully completed. OUTSQL ---------------------------------------------------------------------------------------------------- SELECT SYSDATE "SYSDATE" FROM "SYS"."DUAL" "A1" --//不过还是不好记忆，不知道有什么好方法实现....还是采用输入参数带有sql_id的方式改写如下: $ cat expandz.sql set long 40000 set serveroutput on column arg new_value arg set term off select decode(&2,11,'sql2','utility') arg from dual; set term on prompt declare     l_sqltext clob := null;     l_result  clob := null; begin         select sql_fulltext into l_sqltext from v$sqlarea where sql_id='&&1';         dbms_&arg..expand_sql_text(l_sqltext,l_result);         dbms_output.put_line(l_result); end; / set serveroutput off