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

[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