[20240326]建立完善expand_sql_text.sql脚本.txt

[20240326]建立完善expand_sql_text.sql脚本.txt --//我写的脚本在19c上报错,检查发现一个小错误. --//原始脚本如下: $ cat expand_sql_text.sql set long 20000 set serveroutput on declare     L_sqltext clob := null;         l_version varchar2(3) := null;     l_sql     clob := null;     l_result  clob := null; begin         select regexp_replace(version,'\..*') into l_version from v$instance;         select sql_fulltext into l_sqltext from v$sqlarea where sql_id='&&1';         if l_version = '11' then        l_sql := 'begin                    dbms_sql2.expand_sql_text( :a,:b );                  end;';     elsif l_version = '12' then       l_sql := 'begin                   dbms_utility.expand_sql_text(:a,:b);                 end;';     end if;     execute immediate l_sql using in l_sqltext,out l_result;         dbms_output.put_line(l_result); end; / set serveroutput off --//我写的版本仅仅在11g ,12c下使用.我写死版本号. SYS@192.168.100.235:1521/orcl> SELECT stat_name,value FROM dba_hist_sysstat WHERE snap_id = 29153 AND instance_number = 1 AND dbid  = 1585360079 AND stat_name in ('sorts (rows)'); STAT_NAME                                                               VALUE ---------------------------------------------------------------- ------------ sorts (rows)                                                     203277136577 SYS@192.168.100.235:1521/orcl> @ hash   HASH_VALUE SQL_ID        CHILD_NUMBER   KGL_BUCKET PLAN_HASH_VALUE HASH_HEX   SQL_EXEC_START       SQL_EXEC_ID ------------ ------------- ------------ ------------ --------------- ---------- ------------------- ------------   2001683481 07pg2ntvnyj0t            3        82969      2415011809  774f4419  2024-03-26 09:21:48     16777225 SYS@192.168.100.235:1521/orcl> @ expand_sql_text 07pg2ntvnyj0t declare * ERROR at line 1: ORA-06535: statement string in EXECUTE IMMEDIATE is NULL or 0 length ORA-06512: at line 21 --//修改expand_sql_text.sql:     elsif l_version = '12' then --//为     elsif l_version >= '12' then SYS@192.168.100.235:1521/orcl> @ expand_sql_text 07pg2ntvnyj0t SELECT "A1"."STAT_NAME" "STAT_NAME"      , "A1"."VALUE" "VALUE"   FROM (         SELECT "A2"."SNAP_ID" "SNAP_ID"              , "A2"."DBID" "DBID"              , "A2"."INSTANCE_NUMBER" "INSTANCE_NUMBER"              , "A2"."STAT_NAME" "STAT_NAME"              , "A2"."VALUE" "VALUE"           FROM (                 SELECT /*+ LEADING ("A5" "A4" "A3") USE_HASH ("A4") USE_HASH ("A5") */ "A4"."SNAP_ID" "SNAP_ID","A4"."DBID" "DBID","A4"."INSTANCE_NUMBER"                        "INSTANCE_NUMBER","A3"."STAT_NAME" "STAT_NAME","A4"."VALUE" "VALUE"                   FROM "SYS"."WRM$_SNAPSHOT" "A5"                      , "SYS"."WRH$_SYSSTAT" "A4"                      , "SYS"."WRH$_STAT_NAME" "A3"                  WHERE "A4"."STAT_ID"         = "A3"."STAT_ID"                    AND "A4"."DBID"            = "A3"."DBID"                    AND "A4"."SNAP_ID"         = "A5"."SNAP_ID"                    AND "A4"."DBID"            = "A5"."DBID"                    AND "A4"."INSTANCE_NUMBER" = "A5"."INSTANCE_NUMBER"    AND "A5"."STATUS"          = 0) "A2") "A1"  WHERE "A1"."SNAP_ID"         = 29153    AND "A1"."INSTANCE_NUMBER" = 1    AND "A1"."DBID"            = 1585360079    AND "A1"."STAT_NAME"       = 'sorts (rows)' PL/SQL procedure successfully completed. --//做了格式化处理. --//更新脚本如下: $ cat expand_sql_text.sql -- Copyright 2023 lfree. All rights reserved. -- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms and conditions. -------------------------------------------------------------------------------- -- -- File name:   expand_sql_text.sql -- Purpose:     Display expand of sql fulltext --               -- Author:      lfree --               -- Usage:        --     @ expand_sql_text <sql_id> -- -------------------------------------------------------------------------------- set long 20000 set serveroutput on declare     L_sqltext clob := null;         l_version varchar2(3) := null;     l_sql     clob := null;     l_result  clob := null; begin         select regexp_replace(version,'\..*') into l_version from v$instance;         select sql_fulltext into l_sqltext from gv$sqlarea where sql_id='&&1' and rownum=1;         if l_version = '11' then        l_sql := 'begin                    dbms_sql2.expand_sql_text( :a,:b );                  end;';     elsif l_version >= '12' then       l_sql := 'begin                   dbms_utility.expand_sql_text(:a,:b);                 end;';     end if;     execute immediate l_sql using in l_sqltext,out l_result;         dbms_output.put_line(l_result); end; / set serveroutput off