Skip to content
June 27, 2013 / doganay

REPLACE BIND VARIABLES in v$sql

 

undefine sql_id
accept sql_id prompt "SQL_ID......: "
set serveroutput on
declare
 v_sql_id varchar2(13):=trim('&&sql_id');
 TYPE varchar2_array IS VARRAY(1000) of varchar2(32767);
 v_sql_fulltext varchar2_array := varchar2_array();
 j number:=1;
begin
 v_sql_fulltext.extend(1000);
 for i in
 (
 select substr(sql_fulltext,1,32767) vc_sql_fulltext from gv$sql where sql_id=v_sql_id
 )
 loop
 v_sql_fulltext(j):=i.vc_sql_fulltext;
 j:=j+1;
 end loop;
 for i in
 (
 SELECT name,
 DECODE (
 SUBSTR (datatype_string, 1, 4),
 'NUMB', value_string,
 'VARC', ''''
 || REPLACE (
 DECODE (value_string, 'NULL', '', value_string),
 '''',
 '''''')
 || '''',
 'NVAR', ''''
 || REPLACE (
 DECODE (value_string, 'NULL', '', value_string),
 '''',
 '''''')
 || '''',
 'CHAR', ''''
 || REPLACE (
 DECODE (value_string, 'NULL', '', value_string),
 '''',
 '''''')
 || '''',
 'DATE', 'to_date('''
 || value_string
 || ''',''MM/DD/YY HH24:MI:SS'')')
 AS "VALUE"
 FROM gv$sql_bind_capture
 WHERE sql_id = v_sql_id
 AND child_address =
 (SELECT child_address
 FROM ( SELECT s.child_address
 FROM gv$sql s, gv$sql_bind_capture b
 WHERE s.sql_id = v_sql_id
 AND s.sql_id = b.sql_id
 AND s.executions > 0
 AND b.WAS_CAPTURED = 'YES'
 ORDER BY s.elapsed_time / s.executions DESC)
 WHERE ROWNUM = 1)
GROUP BY name,
 DECODE (
 SUBSTR (datatype_string, 1, 4),
 'NUMB', value_string,
 'VARC', ''''
 || REPLACE (
 DECODE (value_string, 'NULL', '', value_string),
 '''',
 '''''')
 || '''',
 'NVAR', ''''
 || REPLACE (
 DECODE (value_string, 'NULL', '', value_string),
 '''',
 '''''')
 || '''',
 'CHAR', ''''
 || REPLACE (
 DECODE (value_string, 'NULL', '', value_string),
 '''',
 '''''')
 || '''',
 'DATE', 'to_date('''
 || value_string
 || ''',''MM/DD/YY HH24:MI:SS'')')
-- order by to_number(replace(name,substr(name,1,2),'')) desc
-- order by to_number(REGEXP_REPLACE(name, '[^0-9]', '')) desc
ORDER BY name DESC
 )
 loop
 for j IN 1 .. v_sql_fulltext.COUNT
 LOOP
 v_sql_fulltext(j):=replace(replace(upper(v_sql_fulltext(j)), ':"'||substr(upper(i.name),2)||'"', i.value),upper(i.name), i.value);
 END LOOP;
 end loop;
 dbms_output.put_line(v_sql_fulltext(1));
-- for j IN 1 .. v_sql_fulltext.COUNT
-- LOOP
-- dbms_output.put_line(v_sql_fulltext(j));
-- END LOOP;
end;
/
undefine sql_id
Advertisements

One Comment

Leave a Comment
  1. doganay / Nov 10 2016 7:57 am

    Reblogged this on Mustafa DOGANAY's Blog.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

%d bloggers like this: