[20220510]完善tpt expandz.sql指令碼.txt

[20220510]完善tpt expandz.sql指令碼.txt

--//以前改寫的版本,要求輸入引數2作為版本號.改為自動識別最佳.

$ cat tpt/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_output.put_line(l_sqltext);
--      dbms_sql2.expand_sql_text(l_sqltext,l_result);
        dbms_&arg..expand_sql_text(l_sqltext,l_result);
        dbms_output.put_line(l_result);
end;
/
set serveroutput off

--//修改如下:
$ cat expandzz.sql
set long 40000
set serveroutput on
column arg new_value arg
define noprint='print'

set term off head off
col tpt_version_old  &noprint new_value _tpt_version_old
col tpt_version_new  &noprint new_value _tpt_version_new
col tpt_noprint      &noprint new_value _tpt_noprint

WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 11 THEN '' ELSE '--' END tpt_version_old
      ,CASE WHEN v > 11  THEN '' ELSE '--' END tpt_version_new
  FROM version;

set term on head 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_output.put_line(l_sqltext);
&&_tpt_version_old dbms_sql2.expand_sql_text(l_sqltext,l_result);
&&_tpt_version_new dbms_utility.expand_sql_text(l_sqltext,l_result);
        dbms_output.put_line(l_result);
end;
/
set serveroutput off

--//仔細注意一個細節，必須先設定current_schema，不然報錯。
declare
*
ERROR at line 1:
ORA-24256: EXPAND_SQL_TEXT failed with ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_UTILITY", line 1568
ORA-06512: at line 8

> @ cs pppppp_hhh
alter session set current_schema=pppppp_hhh
Session altered.