[20240314]建立完善b5.sql脚本.txt

--//昨天优化调试sql语句,遇到sql语句如下:

SYS@127.0.0.1:9014/ywdb> @ sql_id 1f52yqf7s5bs3
--SQL_ID = 1f52yqf7s5bs3
Select sum ( zjje ) From zy_fymx Where yzxh =:1 And zyh =:2 ;

--//这样生成的sql语句不能直接在sqlplus下执行,我使用b9.sql生成的脚本在:1修改为:N1,
--//我记忆里我以前写过一个可以执行的脚本,很少使用。给写成如下：
begin
execute immediate q'[Select sum ( zjje ) From zy_fymx Where yzxh =:1 And zyh =:2 ]' using
0
,978498
;
end;
/

--//发现原来的脚本一些细节存在问题,比如没有考虑timestamp数据类型,做一些修改.
--//我不保证生成的脚本可以正常执行,也许小量修改就ok了.另外比如NULL，变成字符串'NULL',不想修正这个错误了。
--//而且存在几个缺点,to_char(sql_fulltext)转换clob到字符类型,可能遇到无法转换的情况,sql语句超过4000个字符.
--//另外最大的问题是还不能直接查看sql语句的执行计划,看到的是全部，不知道实际执行的是那个子光标。
--//不过我设置了statistics_level=all，执行计划对应详细的统计信息的应该就是该次执行的执行计划。
--//并且我还打开10046跟踪，可以查看跟踪文件了解真实的执行计划。

$ cat -s  b5z.sql
set feedback off
set head off
set verify off

define sql_id=&1

--
--Ascertain child_no , inst_id
--
COL CHILD_NUMBER  NEW_VALUE CHILD_NO
COL INST_ID   NEW_VALUE INST_ID OLD_VALUE INST_ID

SET TERMOUT OFF

SELECT child_number, inst_id FROM GV$SQL WHERE sql_id = '&&sql_id' AND ROWNUM = 1;

SELECT child_number, inst_id
  FROM GV$SQL_BIND_CAPTURE
 WHERE sql_id = '&&sql_id' AND ROWNUM = 1;

SET TERMOUT ON

spool &&1..txt

SELECT 'set sqlblanklines on' txt FROM DUAL;
select 'alter session set current_schema='||PARSING_SCHEMA_NAME||';' txt from gv$sqlarea where sql_id = '&&sql_id' and rownum=1;
select 'alter session set statistics_level=all;' txt from dual;
select '' from dual;

select '@10046on 12' txt from dual;

select 'begin' txt from dual
union all
select 'execute immediate q'''||chr(7)||(select to_char(sql_fulltext) txt from gv$sqlarea where sql_id='&&1' and rownum=1)||chr(7)||''' using' txt from dual
union all
SELECT LISTAGG ( DECODE ( DATATYPE_STRING ,'NUMBER', value_string
            ,'DATE', '''' || TO_CHAR ( TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss') ,'yyyy/mm/dd hh24:mi:ss') || ''''
            ,'TIMESTAMP', '''' ||TO_CHAR ( ANYDATA.accesstimestamp (value_anydata) ,'yyyy/mm/dd hh24:mi:ss.ff9')|| ''''
            ,'''' || value_string || '''') ,CHR (10) || ',') WITHIN GROUP (ORDER BY POSITION)
          txt
  FROM gv$sql_bind_capture
 WHERE     sql_id = '&&sql_id'
      AND child_number = &&child_no
       AND inst_id = &&inst_id
       AND was_captured = 'YES'
       AND DUP_POSITION IS NULL
union all
select ';' txt from dual
union all
select 'end;' txt from dual
union all
select '/'  txt from dual;

SELECT 'set sqlblanklines off' txt FROM DUAL;
SELECT '@dpc &sql_id outline ''''' txt FROM DUAL;
SELECT 'rollback;' txt FROM DUAL;

select '@10046off' txt from dual;

SELECT 'alter session set current_schema='||user||' ;' txt from dual;

spool off
UNDEF sql_id
UNDEF child_no
UNDEF inst_id
SET FEEDBACK 6;
SET HEAD ON
SET LINESIZE 277