[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.