Generate sql text from awr for Oracle sql using bind variable

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

--

-- File name:   build_bind_vars_awr.sql

--

-- Purpose:     Build SQL*Plus test script with variable definitions

--

-- Author:      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 DBA_HIST_SQLSTAT.BIND_DATA, 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. You should also watch out for dates

--              as SQL*Plus doesn't have a date datatype.

--

-- Usage:       This scripts prompts for two values.

--

--              sql_id:   this is the sql_id of the statement you want to duplicate

--

--              snap_id: this is the snapshot to pull the data from (if you want a specific one)

--

-- See kerryosborne.oracle-guy.com for more info.

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

set sqlblanklines on

set trimspool on

set trimout on

set feedback off;

set linesize 255;

set pagesize 50000;

set timing off;

set head off

--

accept sql_id char prompt "Enter SQL ID ==> " -

default '8p1yd8wtg86vs'

accept snap_id char prompt "Enter Snap_Id ==> "

var isdigits number

col sql_text for a140 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 DBA_HIST_SQL_BIND_METADATA b

where b.sql_id = '&&sql_id'

and rownum < 2

order by position;

end;

/

--

-- Create variable statements

--

select

'variable ' ||

   case :isdigits when 1 then replace(name,':','N') else substr(name,2,30) end || ' ' ||

replace(datatype_string,'CHAR(','VARCHAR2(') txt

from

DBA_HIST_SQL_BIND_METADATA

where sql_id='&&sql_id';

--

-- Set variable values from DBA_HIST_SQLSTAT 

--

select 'begin' txt from dual;

SELECT  

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

' := ' ||

case when b.datatype = 1 then '''' else null end ||

a.value_string ||

case when b.datatype = 1 then '''' else null end ||

';' txt

from table(

  select dbms_sqltune.extract_binds(bind_data) from DBA_HIST_SQLSTAT

  where sql_id like nvl('&&sql_id',sql_id)

  and snap_id like nvl('&&snap_id',snap_id)

and rownum < 2

and bind_data is not null) a, DBA_HIST_SQL_BIND_METADATA b

where b.sql_id = '&&sql_id'

and a.position = b.position

order by b.position;

select 'end;' txt from dual;

select '/' txt from dual;

--

-- Generate statement

--

select regexp_replace(sql_text,'(select |SELECT )','select /* test &&sql_id */ ',1,1) sql_text from (

select case :isdigits when 1 then replace(sql_text,':',':N') else sql_text end ||';' sql_text

from dba_hist_sqltext

where sql_id = '&&sql_id');

spool off;

-- ed &&sql_id\.sql

undef sql_id

undef snap_id

set feedback on;

set head on