/
sql_text.sql
71 lines (61 loc) · 2.2 KB
/
sql_text.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
set termout off timing off head off feed off serverout off
var cur refcursor;
declare
v_sql clob;
v_sql_splitted ku$_vcnt;
function f_split( p_txt clob
, p_len int
, p_delim varchar2
)
return ku$_vcnt
is
v_txt clob:=p_txt;
v_chunk varchar2(4000);
v_pos int;
v_res ku$_vcnt:=ku$_vcnt();
i int:=0;
begin
loop
i:=i+1;
exit when trim(v_txt) is null or length(trim(v_txt))=0;
if length(v_txt)>p_len then
v_chunk := substr(v_txt,1,p_len);
v_pos := instr(v_chunk, p_delim,-1,1);
if v_pos = 0 then
v_pos := p_len;
end if;
v_chunk := substr(v_chunk,1,v_pos);
v_txt := substr(v_txt,v_pos+1);
else
v_chunk := v_txt;
v_txt := null;
end if;
v_res.extend;
v_res(v_res.count):=v_chunk;
end loop;
return v_res;
end;
begin
select
coalesce(
(select sql_fulltext from gv$sqlarea a where a.sql_id='&1' and rownum=1)
, (select sql_text from dba_hist_sqltext a where a.sql_id='&1' and a.dbid in (select d.dbid from v$database d union select i.dbid from dba_hist_database_instance i) and rownum=1)
) qtext
into v_sql
from dual;
v_sql_splitted := f_split(v_sql,1500,' ');
open :cur for select column_value qtext from table(v_sql_splitted);
exception when others then dbms_output.put_line(sqlerrm);
end;
/
col qtext format a32000
prompt ################################ Original query text: ################################################;
spool &_SPOOLS/to_format.sql
print cur
spool off
col qtext clear
set termout on head on
prompt ################################ Formatted query text #################################################;
--host perl inc/sql_format_standalone.pl &_SPOOLS/to_format.sql
host java -jar inc/SQLBeautifier.jar &_SPOOLS/to_format.sql
prompt ################################ Formatted query text End #############################################;