forked from flashdba/scripts
-
Notifications
You must be signed in to change notification settings - Fork 1
Expand file tree
/
Copy pathbind_replace.sql
More file actions
76 lines (72 loc) · 2.32 KB
/
bind_replace.sql
File metadata and controls
76 lines (72 loc) · 2.32 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
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', '''' || replace(value_string,'''','''''') || '''',
'NVAR', '''' || replace(value_string,'''','''''') || '''',
'CHAR', '''' || replace(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', '''' || replace(value_string,'''','''''') || '''',
'NVAR', '''' || replace(value_string,'''','''''') || '''',
'CHAR', '''' || replace(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