[20150616]Creating Test Script With Bind Variable.txt

--原链接:
http://blog.itpub.net/267265/viewspace-1401633/

---最近发现一些小问题，又做了一些更正。这个脚本直接生成后,可以执行用于优化sql语句：

/* Formatted on 2015/6/16 8:57:47 (QP5 v5.252.13127.32867) */
-------------------------------------------------------------------------------------------------------
--
-- File name:   build_bind_vars3.sql
--
-- Purpose:     Build SQL*Plus test script with variable definitions
--
-- Author:      Jack Augustin and Kerry Osborne
--
-- Description: This script creates a file which can be executed in SQL*Plus. It creates bind variables,
--              sets the bind variables to the values stored in V$SQL_BIND_CAPTURE, and then executes
--              the statement. The sql_id is used for the file name and is also placed in the statement
--              as a comment. Note that numeric bind variable names are not permited in SQL*Plus, so if
--              the statement has numberic bind variable names, they have an 'N' prepended to them. Also
--              note that CHAR variables are converted to VARCHAR2.
--
-- Usage:       This scripts prompts for two values.
--
--              sql_id:   this is the sql_id of the statement you want to duplicate
--
--              child_no: this is the child cursor number from v$sql
--                        (the default is 0 second)
--
-- http://kerryosborne.oracle-guy.com/2009/07/creating-test-scripts-with-bind-variables/
-- modify : add date convert varchar2(32) 20130622
-- modify : bug replace char to varchar2(32) 20150114
-- modify : replace sql_fulltext chr(13) to '' 20150114
-- modify : add 'set sqlblanklines on' and 'set sqlblanklines off' 20150616
-- modify : use distinct to delete duplicate  20160616
-------------------------------------------------------------------------------------------------------
--
SET VERIFY OFF
SET SQLBLANKLINES ON
SET TRIMSPOOL ON
SET TRIMOUT ON
SET FEEDBACK OFF;
SET LINESIZE 4000;
SET PAGESIZE 50000;
SET TIMING OFF;
SET HEAD OFF
--
ACCEPT sql_id CHAR PROMPT "Enter SQL ID ==> " DEFAULT &1
ACCEPT child_no CHAR PROMPT "Enter Child Number ==> " DEFAULT 0
VAR isdigits NUMBER
VAR v_sql_fulltext CLOB
--
--
COL sql_fulltext FOR a4000 WORD_WRAP
SPOOL &&sql_id\.sql
--
--Check for numeric bind variable names
--

BEGIN
   SELECT CASE REGEXP_SUBSTR (REPLACE (name, ':', ''), '[[:digit:]]')
             WHEN REPLACE (name, ':', '') THEN 1
          END
     INTO :isdigits
     FROM V$SQL_BIND_CAPTURE
    WHERE sql_id = '&&sql_id' AND child_number = &&child_no AND ROWNUM < 2;
END;
/

--
-- Create variable statements
--

SELECT DISTINCT
          'variable '
       || CASE :isdigits
             WHEN 1 THEN REPLACE (name, ':', 'N')
             ELSE SUBSTR (name, 2, 30)
          END
       || ' '
       || DECODE
          (
             datatype_string
            ,'DATE', 'VARCHAR2(32)'
            ,REPLACE (datatype_string, 'CHAR(', 'VARCHAR2(')
          )
          txt
  --decode(datatype_string, 'DATE','VARCHAR2(32)','CHAR(', 'VARCHAR2(',datatype_string) txt
  --replace(datatype_string,'CHAR(','VARCHAR2(') txt
  FROM V$SQL_BIND_CAPTURE
WHERE sql_id = '&&sql_id' AND child_number = &&child_no;

--
-- Set variable values from V$SQL_BIND_CAPTURE
--

SELECT 'begin' txt FROM DUAL;

SELECT DISTINCT
          CASE :isdigits WHEN 1 THEN REPLACE (name, ':', ':N') ELSE name END
       || ' := '
       || CASE datatype_string WHEN 'NUMBER' THEN NULL ELSE '''' END
       || CASE datatype_string
             WHEN 'DATE'
             THEN
                TO_CHAR
                (
                   TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss')
                  ,'yyyy/mm/dd hh24:mi:ss'
                )
             ELSE
                NVL (value_string, '00')
          END
       || CASE datatype_string WHEN 'NUMBER' THEN NULL ELSE '''' END
       || ';'
          txt
  FROM V$SQL_BIND_CAPTURE
WHERE sql_id = '&&sql_id' AND child_number = &&child_no;

SELECT 'end;' txt FROM DUAL;

SELECT '/' txt FROM DUAL;

--
-- Generate statement
--

SELECT 'set termout off' txt FROM DUAL;

SELECT 'set sqlblanklines on' txt FROM DUAL;

SELECT 'alter session set current_schema=' || PARSING_SCHEMA_NAME || ';' txt
  FROM v$sqlarea
WHERE sql_id = '&&sql_id';

SELECT 'alter session set statistics_level=all;' FROM DUAL;

SELECT '' FROM DUAL;

SELECT REPLACE (sql_fulltext, CHR (13), '')
  FROM (SELECT REGEXP_REPLACE
               (
                  sql_fulltext
                 ,'(select |SELECT )'
                 ,'select /* test &&sql_id */ /*+ gather_plan_statistics */ '
                 ,1
                 ,1
               )
                  sql_fulltext
          FROM (SELECT    CASE :isdigits
                             WHEN 1 THEN REPLACE (sql_fulltext, ':', ':N')
                             ELSE sql_fulltext
                          END
                       || ';'
                          sql_fulltext
                  FROM v$sqlarea
                 WHERE sql_id = '&&sql_id'));

--

SELECT 'set termout on' txt FROM DUAL;

SELECT 'set sqlblanklines off' txt FROM DUAL;

SELECT '@zws '''' ''''' txt FROM DUAL;

SELECT '--@dpc '''' ''''' txt FROM DUAL;

SELECT 'rollback;' txt FROM DUAL;

SPOOL OFF;
UNDEF sql_id
UNDEF child_no
SET FEEDBACK ON;
SET HEAD ON