Creating Test Script With Bind Variable-白红宇

[20150814] Creating Test Script With Bind Variable.txt

--原链接:

--我在原来的基础上做了许多修正。纯粹是工作需要，解决一些优化问题。

--我们的生产系统我设置会话cursor_sharing=force，主要程序存在大量没有使用绑定变量的情况，这样一些常数参数也变成了参数，

--今天建立一个新的脚本，把里面:"SYS_B_NN" 替换为常量，欢迎大家测试：

--注意我日期类型选择环境变量

NLS_TIMESTAMP_TZ_FORMAT=YYYY-MM-DD HH24:MI:SS.FF TZH:TZM

NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF

NLS_DATE_FORMAT=YYYY-MM-DD HH24:MI:SS

--这样直接使用字符串表示时间就没有问题。

-------------------------------------------------------------------------------------------------------

-- 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)

-- 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 20150616

-- modify : do not use distinct to delete duplicate , add where dup_position is null 20150814

-- modify : delete some comment code.

-------------------------------------------------------------------------------------------------------

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 '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

from

V$SQL_BIND_CAPTURE

where

sql_id='&&sql_id'

and child_number = &&child_no

and dup_position is null;

-- Set variable values from V$SQL_BIND_CAPTURE

select 'begin' txt from dual;

select

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

and dup_position is null;

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;

--begin

--select replace(sql_fulltext,chr(13),'') into :v_sql_fulltext 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'));

--end;

--/

--replace :"SYS_B_NN" using constant value

SET SERVEROUTPUT ON

declare

v_sql_fulltext clob;

v_sql clob;

begin

select replace(sql_fulltext,chr(13),'') into v_sql_fulltext 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'));

FOR cursor_1 IN (

select

case :isdigits when 1 then replace(name,':',':"N') else replace(name,':',':"') end||'"' c1,

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 c2

from

V$SQL_BIND_CAPTURE

where

sql_id='&&sql_id'

and child_number = &&child_no

and name like ':SYS\_B\_%' escape '\'

and dup_position is null order by name desc )

LOOP

BEGIN

select replace(v_sql_fulltext,cursor_1.c1,cursor_1.c2) into v_sql_fulltext from dual;

END;

END LOOP;

dbms_output.put_line(v_sql_fulltext);

end;

SET SERVEROUTPUT Off

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