From the Database-Geek.

Continuing with the parser, begun week in PL/SQL Parser Part 1 and PL/SQL Parser Part 2, today I am going to modify the code to account for keywords, operators and data. By data, I don’t mean strings. I mean anything not a keyword, not a comment and not an operator. Data may be a quoted string (which we accounted for in Part 2), but it is also non-language functions and procedures. If you call a user defined procedure, that procedure call is considered data (at least it is here, for now).

In the code presented below, I have done several things.

From the Database-Geek.

Continuing with the parser, begun last week in Building a PL/SQL Code Parser Part 1, today I am going to modify the code to allow for comments. Rather than dive back into code already covered, I will discuss what I have added and then provide the full code below.

I changed my sample code and test proc to a new format:

declare

  v_string varchar2(32000) :=
  'CREATE OR REPLACE PROCEDURE yada' || chr(10)  ||
      ' AS ' || chr(10) ||
      ' /* This is a comment */  ' || chr(10) ||
     ' BEGIN' || chr(10) ||
     '   DBMS_OUTPUT.PUT_LINE(''string with a space'' ||' || chr(10) ||
     '   ''string with emb''''edd''''''''ed quotes'');' || chr(10) ||
     '   -- second_func(''test'');' || chr(10) ||
     ' END;' ;

begin

  v_string := lrc_plsql_parser.parse_line(v_string);

END;

Notice that in addition to the comments, I have added line feeds.

LewisC’s An Expert’s Guide to Oracle Technology

Is it possible to build a PL/SQL parser using nothing but PL/SQL? To answer that question, I guess I need to define “PL/SQL parser”. What would be the intention of this parser?

I would like a way to parse a PL/SQL code block and let me determine some statistics: number of lines, number of keywords, number of DML statements. It would need to recognize comments. I also want to be able to format the code for pretty printing. I want to be able to apply user defined checks for adherence to coding standards.