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 Go to next message
Kevin Meade
Messages: 2101
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Smile

[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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
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 #309609 is a reply to message #309605] Fri, 28 March 2008 02:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
There is the case posted by Frank:
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> COLUMN name FORMAT A9
SQL> COLUMN type FORMAT A9
SQL> COLUMN text FORMAT A60 WORD_WRAPPED
SQL> SELECT * FROM TABLE (no_comments ('P'));

NAME      TYPE            LINE TEXT
--------- --------- ---------- -------------------------------
P         PROCEDURE          1 procedure p is
P         PROCEDURE          2 v varchar2(100);
P         PROCEDURE          3 begin
P         PROCEDURE          4 select '' into v from dual;
P         PROCEDURE          5 end;

5 rows selected.

Regards
Michel
Re: remove comments from all_source output [message #309610 is a reply to message #309609] Fri, 28 March 2008 02:39 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
As I said, "I will leave it to someone else to figure out what to do about strings that contain /* or other exceptions." I was not claiming to handle that situation where you have a quoted string that contains /* making it look like a comment.
Re: remove comments from all_source output [message #309612 is a reply to message #309610] Fri, 28 March 2008 02:43 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Sorry, I'm always impressed by your codes, so I was hungry to read this one and skipped the first sentence. Embarassed

Regards
Michel

[Updated on: Fri, 28 March 2008 02:44]

Report message to a moderator

Re: remove comments from all_source output [message #309720 is a reply to message #309533] Fri, 28 March 2008 10:12 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
You guys are great as usual.

I have become over the years, a 95% guy. I have found that for most non-production uses, a 95% solution will work 100% of the time (sometime I think I am ready for government work...). Not dealing with hints is no big deal at all for me. This plsql routine is exactly what I was looking for.

Once gain my buddies have made my day. I like Oracle support a lot these days, but for things like this, OraFAQ is way better.

Thanks Barbara (and Ross and Michel and Frank). I'll let you all know in a few days how things have worked out with it.

Kevin
Re: remove comments from all_source output [message #310496 is a reply to message #309533] Tue, 01 April 2008 07:34 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #310589 is a reply to message #310584] Tue, 01 April 2008 14:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just thought you wanted to post something "better" (handling more cases).

Regards
Michel
Re: remove comments from all_source output [message #310794 is a reply to message #309533] Wed, 02 April 2008 07:47 Go to previous message
mnitu
Messages: 159
Registered: February 2008
Location: Reims
Senior Member
Actually it’s handling less cases Smile
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> 

Previous Topic: toad error
Next Topic: Error ORA-01652 while running select query
Goto Forum:
  


Current Time: Sat Dec 10 08:51:26 CST 2016

Total time taken to generate the page: 0.13169 seconds