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

--//以前写的生成执行SQL语句的执行脚本b5.sql，存在1个问题，就是无法抓取timestamp类型，适当修改解决此问题，并做了适当调整。
--//比如改用gv$sqlarea视图并且取一条记录，当然缺点就是如果不在共享池，无法抓取sql语句。
--//不过该脚本我个人很少使用,当然还是存在许多问题，to_char(sql_fulltext)超过4000个字符报错.

$ cat b5.sql
/* Formatted on 2026-01-08 17:03:13 (QP5 v5.277) */
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..sql5

SELECT 'set sqlblanklines on' txt FROM DUAL
UNION ALL
SELECT 'alter session set current_schema=' || PARSING_SCHEMA_NAME || ';' txt
FROM gv$sqlarea
WHERE sql_id = '&&sql_id' AND ROWNUM = 1
UNION ALL
SELECT 'alter session set statistics_level=all;' txt 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
SELECT LISTAGG (
             --CASE WHEN (datatype_string='NUMBER' or value_string='NULL') THEN NULL ELSE '' END ||
             CASE datatype_string
                WHEN 'NUMBER'
                THEN
                   DECODE (value_string, 'NULL', NULL, NVL (value_string, '00'))
                WHEN 'DATE'
                THEN
                      'TO_DATE('''
                   || TO_CHAR (TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss')
                   || ''',''yyyy/mm/dd hh24:mi:ss'')'
                WHEN 'TIMESTAMP'
                THEN
                      'TO_TIMESTAMP('''
                   || TO_CHAR (ANYDATA.accesstimestamp (value_anydata), 'yyyy/mm/dd hh24:mi:ss.ff9')
                   || ''',''yyyy/mm/dd hh24:mi:ss.ff9'')'
                ELSE
                   '''' || DECODE (value_string, 'NULL', NULL, NVL (value_string, '00')) || ''''
             END
          || ' -- '
          || name
         --|| CASE WHEN (datatype_string='NUMBER' or value_string='NULL') THEN NULL ELSE '' END
         ,CHR (10) || ',')
       WITHIN GROUP (ORDER BY POSITION)
          txt
FROM v$sql_bind_capture
WHERE sql_id = '&&1' AND child_number = &&child_no 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 '@dpc &sql_id outline ''''' txt FROM DUAL;

SELECT 'set sqlblanklines off' txt FROM DUAL
UNION ALL
SELECT 'rollback;' txt FROM DUAL
UNION ALL
SELECT '--@10046off' txt FROM DUAL
UNION ALL
SELECT 'alter session set current_schema=' || USER || ' ;' txt
FROM DUAL;

PROMPT
SPOOL OFF
UNDEF sql_id
UNDEF child_no
UNDEF inst_id
SET FEEDBACK 6;
SET HEAD ON
SET LINESIZE 277