[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