Skip to content
November 10, 2016 / doganay

REPLACE BIND VARIABLES in v$sql

Mustafa DOGANAY's Blog

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, NVL ( DECODE ( SUBSTR (datatype_string, 1, 4), 'NUMB', value_string, 'VARC', '''' || value_string || '''', 'NVAR', '''' || value_string || '''', 'CHAR', '''' || value_string || '''', 'DATE', 'to_date(''' || value_string || ''',''MM/DD/YY HH24:MI:SS'')', ''), 'NULL') AS "VALUE" FROM gv$sql_bind_capture WHERE sql_id = v_sql_id AND child_address = (SELECT child_address FROM (SELECT child_address FROM gv$sql WHERE sql_id = v_sql_id ORDER BY elapsed_time / (executions + 1) DESC) WHERE ROWNUM = 1) GROUP BY name, DECODE ( SUBSTR (datatype_string, 1, 4), 'NUMB', value_string, 'VARC', '''' || value_string || '''', 'NVAR', '''' || value_string || '''', 'CHAR', '''' || value_string || ''''…

View original post 34 more words

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: