-
Notifications
You must be signed in to change notification settings - Fork 1
/
bvc_tokenizer_pkg.sql
156 lines (124 loc) · 5.01 KB
/
bvc_tokenizer_pkg.sql
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
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
-----------------------------------------------------------------------------------------
-- Bind Variables Checker: Tokenizer, and Statement Binder
-- Author: Alberto Dell'Era
-- Copyright: (c) 2003 - 2017 Alberto Dell'Era http://www.adellera.it
-----------------------------------------------------------------------------------------
--
-- Header comment explaining bvc overall is in bvc_tokenizer_body.sql
--
set echo on
set serveroutput on size 1000000 format wrapped
define deterministic=' '
----------------------------------------------------------
--------------------- PACKAGE HEADER ---------------------
----------------------------------------------------------
create or replace package bvc_tokenizer_pkg as
-- check (important) comments in body
function bound_stmt (
p_stmt varchar2,
p_normalize_numbers_in_ident varchar2 default 'Y',
p_normalize_partition_names varchar2 default 'Y',
p_in_bind_list_as_bind_vec varchar2 default 'Y',
p_strip_hints varchar2 default 'N'
)
return varchar2
&&deterministic.;
@@bvc_tokenizer_head_vars.sql
-- check (important) comments in body
function bound_stmt_verbose (
p_stmt varchar2,
p_normalize_numbers_in_ident varchar2 default 'Y',
p_normalize_partition_names varchar2 default 'Y',
p_in_bind_list_as_bind_vec varchar2 default 'Y',
p_strip_hints varchar2 default 'N',
p_num_replaced_literals out int,
p_replaced_values out t_varchar2,
p_replaced_values_type out t_varchar2_30
)
return varchar2
&&deterministic.;
-- check (important) comments in body
procedure tokenize (
p_stmt varchar2,
p_tokens out nocopy t_varchar2,
p_tokens_type out nocopy t_varchar2_30
);
-- check (important) comments in body
procedure set_log (p_value boolean default true);
-- check (important) comments in body
procedure debug_print_tokens (p_stmt varchar2);
end bvc_tokenizer_pkg;
/
show errors;
----------------------------------------------------------
---------------------- PACKAGE BODY ----------------------
----------------------------------------------------------
create or replace package body bvc_tokenizer_pkg as
@@bvc_tokenizer_body_vars.sql
@@bvc_tokenizer_body.sql
begin
populate_g_keywords;
end bvc_tokenizer_pkg;
/
show errors;
-- sanity check installation
begin
bvc_tokenizer_pkg.set_log;
bvc_tokenizer_pkg.debug_print_tokens (
replace ('/**/select /*+comment*/a,x.b,c_$#,e "ident" --+xx'||chr(10)||
'FROM t where!a!!b!= +1.e-23 and !!!!=:ph1:ind and :1=: "ph_23" and 1. = .0 or :y = : x',
'!', '''')
);
dbms_output.put_line('.');
bvc_tokenizer_pkg.debug_print_tokens (' where +1 = 3 and -9.1=a+1.9 and b = - 1 and c =c++1 and d=d*+1');
dbms_output.put_line('.');
bvc_tokenizer_pkg.debug_print_tokens (' select Q''|xxx|'', q''[xxx]'', q''{xxx}'', q''<xxx>'', q''(xxx)'', q''xxx'', q''()'' ' );
dbms_output.put_line('.');
bvc_tokenizer_pkg.debug_print_tokens ('insert into t partition ( SYS_P32596 ) select sum(x) over( partition by x) from t partition(SYS_P32596)');
bvc_tokenizer_pkg.debug_print_tokens ('declare x int; begin x:=owner . name(); end;');
bvc_tokenizer_pkg.set_log (false);
end;
/
col bound_stmt form a100
select bvc_tokenizer_pkg.bound_stmt ('select /*+hint*/ /*co*/ x , C, "AA" FROM t t103 where 1 = ''pippo'' and :ph3= "t103"') as bound_stmt from dual;
select bvc_tokenizer_pkg.bound_stmt ('insert into t partition ( SYS_P32596 ) select sum(x) over( partition by x) from t partition(SYS_P32596)') as bound_stmt from dual;
select bvc_tokenizer_pkg.bound_stmt ('insert into t partition ( SYS_P32596 ) select sum(x) over( partition by x) from t partition(SYS_PXXXXX)') as bound_stmt from dual;
select bvc_tokenizer_pkg.bound_stmt ('alter table t move partition SYS_P32596') as bound_stmt from dual;
/*
exec bvc_tokenizer_pkg.set_log(false);
spool x.txt
select sql_text, bvc_tokenizer_pkg.bound_stmt(sql_text, 'y') from v$sql;
spool off
*/
/*
@profreset
commit;
exec dbms_profiler.start_profiler ('tokenizer');
set timing on
declare
l_stmt varchar2(1000);
l_stmt2 varchar2(1000);
begin
select sql_text into l_stmt from v$sql where length(sql_text)=(select max(length(sql_text)) from v$sql) and rownum=1;
for i in 1..100 loop
l_stmt2 := bvc_tokenizer_pkg.bound_stmt (l_stmt);
end loop;
end;
/
exec dbms_profiler.stop_profiler;
*/
declare
l_values bvc_tokenizer_pkg.t_varchar2;
l_types bvc_tokenizer_pkg.t_varchar2_30;
i binary_integer;
begin
bvc_tokenizer_pkg.tokenize('select * from dual', l_values, l_types);
dbms_output.put_line(l_values.count);
i := l_values.first();
while i is not null loop
dbms_output.put_line(i||' '||l_values(i));
i := l_values.next(i);
end loop;
end;
/
select bvc_tokenizer_pkg.bound_stmt (q'[select * from t where x in ('a','b') and y in (1,2) and b in (:vc,:vb) and b1 in (:8,:9) and z in (:n)]', p_in_bind_list_as_bind_vec=>'Y') as bound_stmt from dual;