Home » SQL & PL/SQL » SQL & PL/SQL » remove comments from all_source output (10g r2)
| remove comments from all_source output [message #309533] |
Thu, 27 March 2008 16:00  |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
anyone got a slick select statement or barring that, a PL/SQL routine that will remove comments from a query off ALL_SOURCE?
For example: given this code as compiled source:
create or replace package pkg_test1 is
/*
this is a comment
*/
-- this is a comment
v1 number := 0; -- this is a comment
v2 number := 0; /* this is a comment */
procedure p1;
end;
/
show errors
I would like a query similar to:
select *
from some_thing_that_reads_all_source
where owner = 'KM21378' and name = 'PKG_TEST1' and type = 'PACKAGE BODY'
/
or this
select owner,name,type,line,somefunc(text) text
from all_source
where owner = 'KM21378' and name = 'PKG_TEST1' and type = 'PACKAGE BODY'
/
that gives this:
OWNER NAME TYPE LINE TEXT
---------- ----------- ---------- ---------- --------------------
KM21378 PKG_TEST1 PACKAGE 1 package pkg_test1 is
KM21378 PKG_TEST1 PACKAGE 2
KM21378 PKG_TEST1 PACKAGE 6
KM21378 PKG_TEST1 PACKAGE 8
KM21378 PKG_TEST1 PACKAGE 9 v1 number := 0;
KM21378 PKG_TEST1 PACKAGE 10 v2 number := 0;
KM21378 PKG_TEST1 PACKAGE 11
KM21378 PKG_TEST1 PACKAGE 12 procedure p1;
KM21378 PKG_TEST1 PACKAGE 13
KM21378 PKG_TEST1 PACKAGE 14 end;
or even this
OWNER NAME TYPE LINE TEXT
---------- ----------- ---------- ---------- ------------------------------
KM21378 PKG_TEST1 PACKAGE 1 package pkg_test1 is
KM21378 PKG_TEST1 PACKAGE 2
KM21378 PKG_TEST1 PACKAGE 3
KM21378 PKG_TEST1 PACKAGE 4
KM21378 PKG_TEST1 PACKAGE 5
KM21378 PKG_TEST1 PACKAGE 6
KM21378 PKG_TEST1 PACKAGE 7
KM21378 PKG_TEST1 PACKAGE 8
KM21378 PKG_TEST1 PACKAGE 9 v1 number := 0;
KM21378 PKG_TEST1 PACKAGE 10 v2 number := 0;
KM21378 PKG_TEST1 PACKAGE 11
KM21378 PKG_TEST1 PACKAGE 12 procedure p1;
KM21378 PKG_TEST1 PACKAGE 13
KM21378 PKG_TEST1 PACKAGE 14 end;
Notice
1) I am not real particular here, I don't need the lines renumbered whenever an entire line is dropped because it is part of a comment block.
2) I show comments at the end of lines but this is an obscure case so if this was not done that would be OK with me too.
3) If some solution simply replaces comments with blanks as the second example shows, that works great.
Don't spend too much time on it, was hoping someone had a quick hack for it.
Kevin
|
|
|
|
| Re: remove comments from all_source output [message #309558 is a reply to message #309533] |
Thu, 27 March 2008 21:09  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Are you fussy about Hints?
What about constant strings that look like comments?
SELECT '/*this is not a comment*/' from dual
I don't have a solution either way, but some people here love a challenge and are bound to code it up for you simply because it's interesting.
I also see opportunity for improving performance using syntax like:
SELECT *
FROM TABLE(my_pipelined_table_function(CURSOR(
SELECT *
FROM all_source
WHERE owner = 'KM21378'
AND name = 'PKG_TEST1'
AND type = 'PACKAGE BODY'
ORDER BY owner, name, type, line
)))
Then it should be simply a matter of sequentially parsing the cursor contents in PL/SQL using rules to filter or blank the comments.
Ross Leishman
|
|
|
|
| Re: remove comments from all_source output [message #309589 is a reply to message #309558] |
Fri, 28 March 2008 01:26 |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
| rleishman wrote on Fri, 28 March 2008 03:09 | Are you fussy about Hints?
What about constant strings that look like comments?
SELECT '/*this is not a comment*/' from dual
|
Must be a typo, since it actually IS a comment 
[Edit: OOPS!!!! Totally overlooked the quotes!!
Sorry, thought you gave an example of a 'hint']
[Updated on: Fri, 28 March 2008 01:28] Report message to a moderator |
|
|
|
| Re: remove comments from all_source output [message #309605 is a reply to message #309589] |
Fri, 28 March 2008 02:18 |
 |
Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
The following gets rid of single-line and multiple line comments, but not hints. I will leave it to someone else to figure out what to do about strings that contain /* or other exceptions.
SCOTT@orcl_11g> create or replace package pkg_test1 is
2
3 /*+ hint */
4
5
6 /*
7 this is a comment
8 */
9
10 -- this is a comment
11
12 v1 number := 0; -- this is a comment
13 v2 number := 0; /* this is a comment */
14
15
16 procedure p1;
17
18 end;
19 /
Package created.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE source_typ AS OBJECT
2 (name VARCHAR2(20),
3 type VARCHAR2(12),
4 line NUMBER,
5 text VARCHAR2(4000))
6 /
Type created.
SCOTT@orcl_11g> CREATE OR REPLACE TYPE source_tab AS TABLE OF source_typ;
2 /
Type created.
SCOTT@orcl_11g> CREATE OR REPLACE FUNCTION no_comments
2 (p_name IN VARCHAR2,
3 p_type IN VARCHAR2 DEFAULT NULL,
4 p_owner IN VARCHAR2 DEFAULT USER)
5 RETURN source_tab PIPELINED
6 AS
7 v_text VARCHAR2 (4000);
8 v_check NUMBER := 0;
9 BEGIN
10 FOR r IN
11 (SELECT name, type, line, text
12 FROM all_source
13 WHERE name = p_name
14 AND type = NVL (p_type, type)
15 AND owner = p_owner
16 ORDER BY owner, name, type, line)
17 LOOP
18 v_text := r.text;
19 IF v_check = 1 THEN
20 IF INSTR (v_text, '*/') > 0 THEN
21 v_text := SUBSTR (v_text, INSTR (v_text, '*/') + 2);
22 v_check := 0;
23 ELSE
24 v_text := '';
25 END IF;
26 END IF;
27 WHILE INSTR (v_text, '/*') > 0 AND INSTR (v_text, '/*') <> INSTR (v_text, '/*+') LOOP
28 IF INSTR (v_text, '*/') > 0 THEN
29 v_text := SUBSTR (v_text, 1, INSTR (v_text, '/*') - 1)
30 || SUBSTR (v_text, INSTR (v_text, '*/') + 2);
31 v_check := 0;
32 ELSE
33 v_text := SUBSTR (v_text, 1, INSTR (v_text, '/*') - 1);
34 v_check := 1;
35 END IF;
36 END LOOP;
37 IF INSTR (v_text, '--') > 0 THEN
38 v_text := SUBSTR (v_text, 1, INSTR (v_text, '--') - 1);
39 END IF;
40 PIPE ROW (source_typ (r.name, r.type, r.line, v_text));
41 END LOOP;
42 RETURN;
43 END no_comments;
44 /
Function created.
SCOTT@orcl_11g> show errors
No errors.
SCOTT@orcl_11g> COLUMN name FORMAT A9
SCOTT@orcl_11g> COLUMN type FORMAT A8
SCOTT@orcl_11g> COLUMN text FORMAT A60 WORD_WRAPPED
SCOTT@orcl_11g> SELECT * FROM TABLE (no_comments ('PKG_TEST1'))
2 /
NAME TYPE LINE TEXT
--------- -------- ---------- ------------------------------------------------------------
PKG_TEST1 PACKAGE 1 package pkg_test1 is
PKG_TEST1 PACKAGE 2
PKG_TEST1 PACKAGE 3 /*+ hint */
PKG_TEST1 PACKAGE 4
PKG_TEST1 PACKAGE 5
PKG_TEST1 PACKAGE 6
PKG_TEST1 PACKAGE 7
PKG_TEST1 PACKAGE 8
PKG_TEST1 PACKAGE 9
PKG_TEST1 PACKAGE 10
PKG_TEST1 PACKAGE 11
PKG_TEST1 PACKAGE 12 v1 number := 0;
PKG_TEST1 PACKAGE 13 v2 number := 0;
PKG_TEST1 PACKAGE 14
PKG_TEST1 PACKAGE 15
PKG_TEST1 PACKAGE 16 procedure p1;
PKG_TEST1 PACKAGE 17
PKG_TEST1 PACKAGE 18 end;
18 rows selected.
SCOTT@orcl_11g>
|
|
|
|
|
|
|
|
|
|
|
|
| Re: remove comments from all_source output [message #310496 is a reply to message #309533] |
Tue, 01 April 2008 07:34 |
mnitu
Messages: 159
Registered: February 2008
Location: Reims
|
Senior Member |
|
|
Filtering in stages and using regular expressions can simplify the job.
Connected to Oracle Database 10g Release 10.2.0.3.0
Connected as gf
SQL>
SQL> Create Or Replace Function no_multi_line_comments (
2 p_text In Varchar2
3 ) Return Varchar2 Is
4 l_str Varchar2(4000) := p_text;
5 l_flag Number(1) := sys_context('userenv','client_info');
6 Begin
7 If l_flag = 1 Then
8 If regexp_instr(p_text,'\*/') > 0 Then
9 dbms_application_info.set_client_info(0);
10 l_str := SubStr(p_text,regexp_instr(p_text,'\*/') + 2);
11 Else
12 l_str := Null;
13 End If;
14 ElsIf regexp_instr(p_text,'/\*[^+]') > 0 Then
15 dbms_application_info.set_client_info(1);
16 l_str := substr(p_text,1,regexp_instr(p_text,'/\*[^+]')-1);
17 End If;
18 --
19 Return l_str;
20 End;
21 /
Function created
SQL> show_errors
SQL> Exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed
SQL> Column name Format A9
SQL> Column text Format a50
SQL> Select /*Second stage: filter multi-line comments */
2 name,line, no_multi_line_comments(text) text
3 From (
4 Select /* First stage: filter single line comments */
5 name,line,
6 regexp_replace(regexp_replace(text,'/\*[^+].*\*/',''),'--.*$','') text
7 From all_source
8 Where name = 'PKG_TEST1'
9 Order by line
10 )
11 /
NAME LINE TEXT
--------- ---------- --------------------------------------------------
PKG_TEST1 1 package pkg_test1 is
PKG_TEST1 2
PKG_TEST1 3 /*+ hint */
PKG_TEST1 4
PKG_TEST1 5
PKG_TEST1 6
PKG_TEST1 7
PKG_TEST1 8
PKG_TEST1 9
PKG_TEST1 10
PKG_TEST1 11
PKG_TEST1 12 v1 number := 0;
PKG_TEST1 13 v2 number := 0;
PKG_TEST1 14
PKG_TEST1 15
PKG_TEST1 16 procedure p1;
PKG_TEST1 17
PKG_TEST1 18 end;
18 rows selected
SQL>
|
|
|
|
| Re: remove comments from all_source output [message #310500 is a reply to message #310496] |
Tue, 01 April 2008 07:45 |
 |
Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> create or replace procedure p is
2 v varchar2(100);
3 begin
4 select '/* this is not a comment */' into v from dual;
5 end;
6 /
Procedure created.
SQL>
SQL> Create Or Replace Function no_multi_line_comments (
2 p_text In Varchar2
3 ) Return Varchar2 Is
4 l_str Varchar2(4000) := p_text;
5 l_flag Number(1) := sys_context('userenv','client_info');
6 Begin
7 If l_flag = 1 Then
8 If regexp_instr(p_text,'\*/') > 0 Then
9 dbms_application_info.set_client_info(0);
10 l_str := SubStr(p_text,regexp_instr(p_text,'\*/') + 2);
11 Else
12 l_str := Null;
13 End If;
14 ElsIf regexp_instr(p_text,'/\*[^+]') > 0 Then
15 dbms_application_info.set_client_info(1);
16 l_str := substr(p_text,1,regexp_instr(p_text,'/\*[^+]')-1);
17 End If;
18 --
19 Return l_str;
20 End;
21 /
Function created.
SQL> Exec dbms_application_info.set_client_info(0);
PL/SQL procedure successfully completed.
SQL> Column name Format A9
SQL> Column text Format a50
SQL> Select /*Second stage: filter multi-line comments */
2 name,line, no_multi_line_comments(text) text
3 From (
4 Select /* First stage: filter single line comments */
5 name,line,
6 regexp_replace(regexp_replace(text,'/\*[^+].*\*/',''),'--.*$','') text
7 From all_source
8 Where name = 'P'
9 Order by line
10 )
11 /
NAME LINE TEXT
--------- ---------- --------------------------------------------------
P 1 procedure p is
P 2 v varchar2(100);
P 3 begin
P 4 select '' into v from dual;
P 5 end;
5 rows selected.
Failed!
Regards
Michel
|
|
|
|
| Re: remove comments from all_source output [message #310584 is a reply to message #310500] |
Tue, 01 April 2008 13:52 |
mnitu
Messages: 159
Registered: February 2008
Location: Reims
|
Senior Member |
|
|
Hi Michel,
as Barbara said "I will leave it to someone else to figure out what to do about strings that contain /* or other exceptions." What I mean by this is that my proposition it's actually an equivalent solution. By the way, try to test the result of applying NO_COMMENTS on itself, then do the same with NO_MULTI_LINE_COMMENTS.
But if you want to improve it here are some ideas.
Best regards
Marius NITU
|
|
|
|
|
|
| Re: remove comments from all_source output [message #310794 is a reply to message #309533] |
Wed, 02 April 2008 07:47 |
mnitu
Messages: 159
Registered: February 2008
Location: Reims
|
Senior Member |
|
|
Actually it’s handling less cases
Ok, let’s try to improve it (it worth nothing to mention that it does not handle all situation).
Connected to Oracle Database 10g Release 10.2.0.3.0
Connected as gf
SQL>
SQL> create or replace procedure p is
2 v varchar2(100);
3 begin
4 /* This is a comment */
5 -- also this one
6 /* this is a comment followed*/ /*by another comment followed by */ -- this last comment
7 select '/* this is not a comment */' into v from dual; /* test comment */
8 select '/* this is not a comment */' into v from dual; -- test comment
9 select ' -- neither this one ' into v from dual; -- test comment
10 end;
11 /
Procedure created
SQL> show errors
No errors for PROCEDURE GF.P
SQL> Create Or Replace Function no_single_line_comments (
2 p_text In Varchar2
3 ) Return Varchar2 Is
4 l_match_position PLS_INTEGER := 0;
5 l_str Varchar2(4000) := p_text ;
6 Begin
7 l_match_position := regexp_instr(l_str,'(/\*.*\*/)|(''(.*)[^''].*'')');
8 If l_match_position > 0 Then
9 If SubStr(l_str,l_match_position,1) = '''' Then
10 l_match_position := regexp_instr(l_str,'(/\*.*\*/)|(''(.*)[^''].*'')',1,1,1);
11 l_str := SubStr(l_str,1,l_match_position)||no_single_line_comments(SubStr(l_str,l_match_position));
12 Else
13 l_str := no_single_line_comments(regexp_replace(l_str,'/\*.*\*/',''));
14 End If;
15 Else
16 l_str := regexp_replace(l_str,'--.*$','');
17 End If;
18 --
19 Return l_str;
20 End;
21 /
Function created
SQL> show errors
No errors for FUNCTION GF.NO_SINGLE_LINE_COMMENTS
SQL> Column name Format A9
SQL> Column text Format a70
SQL> Select /* First stage: filter single line comments */
2 name,line,
3 no_single_line_comments(text) text
4 From all_source
5 Where name = 'P'
6 Order by line
7 /
NAME LINE TEXT
--------- ---------- ----------------------------------------------------------------------
P 1 procedure p is
P 2 v varchar2(100);
P 3 begin
P 4
P 5
P 6
P 7 select '/* this is not a comment */' into v from dual;
P 8 select '/* this is not a comment */' into v from dual;
P 9 select ' -- neither this one ' into v from dual;
P 10 end;
P 11
P 12
12 rows selected
SQL>
|
|
|
|
| Re: remove comments from all_source output [message #683787 is a reply to message #309533] |
Sun, 21 February 2021 20:27 |
 |
Youssef_Tadros
Messages: 2
Registered: February 2021
|
Junior Member |
|
|
This function will take all the comments out while dealing correctly with strings;
create or replace package what_a_mess is
/* This is a comment */
/*+ this is a hint */
word /*comment*/varchar2(10) := 'this /* comment */ is actually
part of a long string
where there are other -- comment-like things
'; -- but not this. this is a comment.
word2 varchar2(10) /*comment*/ := 'short /* string --';--comment
/* This is a comment.
...
it is a long comment.
Yes it is. */
end what_a_mess;
create or replace package flag_pkg is
flag varchar2(10) := 'code';
end flag_pkg;
/
with function no_comments (p_text varchar2)
return varchar2 as
given_str varchar2(4000) := p_text;
return_str varchar2(4000);
x_flag varchar2(10) := flag_pkg.flag;
function next_instr (str1 varchar2) return number as
num1 number := regexp_instr(str1,'/\*[^+]');
num2 number := instr(str1,'''');
num3 number := instr(str1,'--');
retnumber number;
begin
if num1 = 0 and num2 = 0 and num3 = 0 then
retnumber := 0;
else
num1 := case when num1 = 0 then 4001 else num1 end;
num2 := case when num2 = 0 then 4001 else num2 end;
num3 := case when num3 = 0 then 4001 else num3 end;
retnumber := least(num1, num2, num3);
end if;
return retnumber;
end next_instr;
begin
while length(given_str) > 0 loop
if x_flag = 'string' then
if instr(given_str,'''') > 0 then
return_str := return_str
|| substr( given_str, 1, instr(given_str,''''));
given_str := substr( given_str, instr(given_str,'''')+1);
x_flag := 'code';
else
return_str := return_str || given_str;
given_str := '';
end if;
elsif x_flag = 'comment' then
if instr(given_str, '*/') > 0 then
given_str := substr( given_str, instr(given_str,'*/')+2);
x_flag := 'code';
else
given_str := '';
end if;
elsif next_instr(given_str) = 0 then
return_str := return_str || given_str;
given_str := '';
elsif next_instr(given_str) = instr(given_str,'''') then
return_str := return_str
|| substr(given_str, 1, instr(given_str,''''));
given_str := substr(given_str, instr(given_str,'''')+1);
x_flag := 'string';
elsif next_instr(given_str) = regexp_instr(given_str,'/\*[^+]') then
return_str := return_str
|| substr(given_str, 1, regexp_instr(given_str,'/\*[^+]')-1);
given_str := substr(given_str, regexp_instr(given_str,'/\*[^+]')+2);
x_flag := 'comment';
else
return_str := return_str
|| substr(given_str, 1, instr(given_str,'--')-1);
given_str := '';
end if;
end loop;
flag_pkg.flag := x_flag;
return return_str;
end no_comments;
select name, line, no_comments(text) text
from (
select * from user_source
where name = 'WHAT_A_MESS'
order by line);
|
|
|
|
|
|
|
|
|
|
| Re: remove comments from all_source output [message #690015 is a reply to message #689979] |
Sat, 14 September 2024 10:31 |
Solomon Yakobson
Messages: 3301
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
mherrerat wrote on Tue, 10 September 2024 22:53Thanks Youssef, this implementation is working like a charm for me as part of a simple PL/SQL good practices validator.
Not 100%. It doesn't handle Q-literals. Code correcly removes
-- but not this. this is a comment.
word /*comment*/varchar2(10) := 'this /* comment */ is actually
part of a long string
where there are other -- comment-like things
'; -- but not this. this is a comment.
word /*comment*/varchar2(10) := Q'[this ' /* comment */ is actually
part of a long string
where there are other -- comment-like things
]'; -- but not this. this is a comment.
SQL> create or replace package what_a_mess is
2 /*
3 This is a comment
4 */
5 /*+ this is a hint */
6
7 word /*comment*/varchar2(10) := Q'[this O'Reilly /* comment */ is actually
8 part of a long string
9 where there are other -- comment-like things
10 ]'; -- but not this. this is a comment.
11 word2 varchar2(10) /*comment*/ := 'short /* string --';--comment
12 /* This is a comment.
13 ...
14 it is a long comment.
15 Yes it is. */
16 end what_a_mess;
17 /
Package created.
SQL>
SQL> /
NAME LINE TEXT
------------------------------ ---------- --------------------------------------------------------------------------------
WHAT_A_MESS 1 package what_a_mess is
WHAT_A_MESS 2
WHAT_A_MESS 3
WHAT_A_MESS 4
WHAT_A_MESS 5 /*+ this is a hint */
WHAT_A_MESS 6
WHAT_A_MESS 7 word varchar2(10) := Q'[this O'Reilly is actually
WHAT_A_MESS 8 part of a long string
WHAT_A_MESS 9 where there are other
WHAT_A_MESS 10 ]'; -- but not this. this is a comment.
WHAT_A_MESS 11 word2 varchar2(10) /*comment*/ := 'short
WHAT_A_MESS 12
WHAT_A_MESS 13
WHAT_A_MESS 14
WHAT_A_MESS 15
WHAT_A_MESS 16 end what_a_mess;
16 rows selected.
SQL>
-- but not this. this is a comment.
And from then on single quotes come mismatched (well, unless there will be another Q literal with odd number of single quotes inside) and therefore we see incorrect
word2 varchar2(10) /*comment*/ := 'short
word2 varchar2(10) := 'short /* string --';
[Updated on: Sat, 14 September 2024 14:22] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Thu Nov 21 08:04:16 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
