-
Notifications
You must be signed in to change notification settings - Fork 3
/
xplan_utilities_body.sql
191 lines (175 loc) · 6.28 KB
/
xplan_utilities_body.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
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
--------------------------------------------------------------------------------
-- Author: Alberto Dell'Era
-- Copyright: (c) 2008-2021 Alberto Dell'Era http://www.adellera.it
--------------------------------------------------------------------------------
-----------------------------------------------------------
-- enables or disables buffer redirection to internal buffer.
-- In both cases, clears the buffer.
procedure enable_print_buffer (p_enable_or_disable varchar2 default 'ENABLE')
is
begin
m_print_buffer.delete;
if p_enable_or_disable = 'ENABLE' then
m_print_buffer_enabled := true;
elsif p_enable_or_disable = 'DISABLE' then
m_print_buffer_enabled := false;
else
raise_application_error (-20001, 'enable_print_buffer: p_enable_or_disable is not ENABLE or DISABLE.');
end if;
end enable_print_buffer;
-----------------------------------------------------------
-- prints to dbms_output.put_line or to internal buffer
-- if requested
procedure print_or_buffer (p_line varchar2)
is
begin
if m_print_buffer_enabled then
m_print_buffer(m_print_buffer.count) := p_line;
else
dbms_output.put_line (p_line);
end if;
end print_or_buffer;
-----------------------------------------------------------
-- transforms a statement into lines (with maxsize)
-- It's a pretty-printer as well.
procedure print_stmt_lines (p_text varchar2)
--create or replace procedure str2lines (p_text varchar2)
is
l_text long default rtrim(p_text);
l_text_length number default length(l_text);
l_pos int default 1;
l_chunk_size int default &LINE_SIZE.;
l_curr varchar2(400);
l_last int;
begin
&COMM_IF_GT_10GR1. if l_chunk_size > 255 then l_chunk_size := 255-5; end if;
loop
l_curr := substr (l_text, l_pos, l_chunk_size);
exit when l_curr is null;
-- chop at the FIRST newline, if any
l_last := instr (l_curr, chr(10));
-- if not, chop at the last pos if shorter than chunksize
if l_last <= 0 and length(l_curr) < l_chunk_size then
l_last := l_chunk_size;
end if;
-- if not, chop at the LAST blank, if any
if l_last <= 0 then
l_last := instr (l_curr, ' ', -1);
end if;
-- if not, chop BEFORE an operator or separator
if l_last <= 0 then
l_last := -1 + greatest (instr (l_curr , '<=', -1),
instr (l_curr , '>=', -1),
instr (l_curr , '<>', -1),
instr (l_curr , '!=', -1),
instr (l_curr , ':=', -1),
instr (l_curr , '=' , -1),
instr (l_curr , '<' , -1),
instr (l_curr , '>' , -1),
instr (l_curr , ',' , -1),
instr (l_curr , ';' , -1),
instr (l_curr , '+' , -1),
instr (l_curr , '-' , -1),
instr (l_curr , '*' , -1),
instr (l_curr , '/' , -1),
instr (l_curr , '(' , -1),
instr (l_curr , '/*', -1)
);
-- handle clash of '=' and '<=', '>=','!=' or ':='; of '*' and '/*'
if l_last > 2 and substr (l_curr, l_last, 2) in ('<=','>=','<>','!=','/*') then
l_last := l_last-1;
end if;
end if;
-- last resort: don't chop
if l_last <= 0 then
l_last := l_chunk_size;
end if;
-- print (or buffer) line
print_or_buffer ( rtrim ( substr (l_curr, 1, l_last), chr(10) ));
-- advance current position
l_pos := l_pos + l_last;
exit when l_pos > l_text_length;
end loop;
end print_stmt_lines;
-----------------------------------------------------------
-- print a line, breaking it if necessary
procedure print (p_text varchar2)
is
begin
print_stmt_lines (p_text);
end print;
-----------------------------------------------------------
-- print a long (coming from a query) using print()
-- adapted from Tom's showlong: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:665224430110
procedure print_long (
p_query varchar2,
p_bind_1_name varchar2,
p_bind_1_value varchar2,
p_bind_2_name varchar2,
p_bind_2_value varchar2)
as
l_cursor integer default dbms_sql.open_cursor;
l_n number;
l_long_val varchar2(32000);
l_long_len number;
l_buflen number := 32000;
l_curpos number := 0;
begin
dbms_sql.parse( l_cursor, p_query, dbms_sql.native );
dbms_sql.bind_variable( l_cursor, p_bind_1_name, p_bind_1_value );
dbms_sql.bind_variable( l_cursor, p_bind_2_name, p_bind_2_value );
dbms_sql.define_column_long(l_cursor, 1);
l_n := dbms_sql.execute(l_cursor);
if (dbms_sql.fetch_rows(l_cursor)>0)
then
loop
dbms_sql.column_value_long(l_cursor, 1, l_buflen,
l_curpos , l_long_val,
l_long_len );
l_curpos := l_curpos + l_long_len;
print ( l_long_val );
exit when l_long_len = 0;
end loop;
end if;
dbms_sql.close_cursor(l_cursor);
exception
when others then
if dbms_sql.is_open(l_cursor) then
dbms_sql.close_cursor(l_cursor);
end if;
raise;
end print_long;
-----------------------------------------------------------
-- print a CLOB
procedure print_clob( p_clob clob)
is
l_buffer long;
l_amount binary_integer;
l_offset int;
begin
l_amount := 32767;
l_offset := 1;
loop
dbms_lob.read( p_clob, l_amount, l_offset, l_buffer );
print( l_buffer );
exit when l_amount < 32767;
l_offset := l_offset + l_amount;
end loop;
exception
when no_data_found then
null;
end print_clob;
-----------------------------------------------------------
function d2s (p_date date) return varchar2
is
begin
return to_char (p_date, 'yyyy/mm/dd hh24:mi:ss');
end d2s;
-----------------------------------------------------------
-- check whether the argument is an integer
function is_integer (p_s varchar2)
return boolean
is
begin
return trim ( translate (p_s, '0123456789', ' ') ) is null;
end is_integer;