PL/SQL- REGEXP_LIKE

I'm writing a procedure that reads some PL/SQL code from a repository and then verifies that each procedure/function has the appropriate documentation header. Giving the following PL/SQL code contained in variable my_code:

my_code varchar2(1024) := '
  script package test_lib
  is
     -------------------------------------------------------------------------------
     -- <function name="my_func" begin="9-Dec-2010">
     --   <summary>
     --     Test function.
     --   </summary>
     --   <authors>
     --     <author name="Giuseppe Greco" email="giuseppe.greco@b-source.ch"/>
     --   </authors>
     --   <params>
     --     <param name="num" mandatory="yes" type="input">
     --       Test param.
     --     </param>
     --   </params>
     --   <return>
     --     True if it is an alarm; otherwise, false.
     --   </return>
     -- </function>
     --------------------------------------------------------------------------------
     function my_func(par1 NUMBER)
     return boolean
     is
       l_num NUMBER开发者_Python百科 := 0;
     begin
       if l_num < 1 then
         dbms_output.put_line(''my_func'');
       end if;
       return true;
     end my_func;
   end test_lib;
 ';

I've tried something similar... but it does not work:

if REGEXP_LIKE(my_code, 'function (\w).+end \1;') then
    l_number_of_funcs := LENGTH(
        REGEXP_REPLACE(
            my_code,
            '([^f]+[^u]+[^n]+[^c]+[^t]+[^i]+[^o]+[^n]+)(.+)is(.*)begin(.*)end([a-z0-9_\-]+);'));
end if;

In the statements above, REGEXP_LIKE never returns true, so REGEXP_REPLACE is never executed. What I'm doing is determining whether or not the code contains at least one procedure, and if it does, I invoke REGEXP_REPLACE to determine how many procedures the code contains (in the example above it should return 1). I first invoke REGEXP_LIKE because if the code does not contain any procedure, REGEXP_REPLACE crashes.

Any idea or suggestion? Any help would be really appreciated.

Thanks, Jeff

1. Try a simpler regular expression

   '^.*function.*return.*(is|as).*begin.*end'
   

2. The REGEXP_COUNT function might be more appropriate in this context.

3. The presence of multiple lines is going to break this very badly. Even the simplified regular expression in (1) above doesn't work if there are multiple lines. I suggest that you write a comment-stripper and newline-remover, pass your code string through those functions, then run it through the regular expression.

In any case, using regular expressions to parse text is often-times not as helpful as might be hoped. You really need a parser for PL/SQL - see

http://database-geek.com/2009/02/06/building-a-plsql-code-parser-using-plsql-part-1-2/

Share and enjoy.

I think with NVL you do not need two checks. Try:

l_number_of_funcs := 
NVL(LENGTH(REGEXP_REPLACE(l_code,'(f)unction [^(]+\(|.','\1',1,0,'in')),0);

I could not test this, but it should go in the right direction.