complex regex to identify parameters with brackets only

Question

I'm trying to build a regex that will identify parameters inside brackets and ignore pl/sql comments (single line --, and multiple lines /* */)

For instance:

create or replace table_name   ---sfjdslkfjslkfjslkfjdsfsdf

**(var1 in out number, var2 number)**

/* sdfls 
sfdsd jfs
 sfs f
sd f
sfsf */

AS 

BEGIN

(var1 in out number, var2 number) should be matched only. It should also account for cases where:

• There are not comments (single or multiple lines)
• There are only single line comments either before or after the parameters
• There are only multiple line comments either before or after the parameters
• There are no parameters

Assumptions:

• Parameters are always enclosed in brackets ()
• Procedures can sometimes have no parameters but have comments (either single or multiple line comments) before the AS BEGIN clause
• Procedures start with create or replace table_name
• We're only interested to read the until the AS BEGIN clause

In other words, I need to find the index of the first opening bracket '(' that is outside any comments (single or multiple lines) and that comes before the AS BEGIN clause.

UPDATE:

I have managed to match the comments using the following regex:

(?:\/\*(?:[\s\S]*?)\*\/)|(?:\-\-(?:.*)$)

For instance here it will match all the comments:

create or replace table_name 

-- sdlfksl kjs slkjslds js

/* lsdjfdkj 
s fskjfs
 sf sf
 sdf;;''
sfs fs
 */

 (hello number, var2 number)

 --sdflksf

 /*sl --sdflks s kdjfls())({fsfs */


AS

BEGIN

I can do this now in Java to identify the first opening bracket outside of any matching group. However it would be easier if I could just ignore the matching group and match the one parameters in the brackets only instead.

EDIT

This is not asking for a solution with pl/sql or sqlplus or whatever. I have a few pl/sql procedures stored in files that I need to modify and add new parameters to. I'm using Java to do that and inside Java im using a combination of loops and regexs.

Answer 1

Not sure why you want to reinvent the wheel. In Oracle, you could use the *_ARGUMENTS view to get the list of all the arguments.

For example,

SQL> CREATE OR REPLACE
  2  PROCEDURE p(
  3      in_date  DATE,
  4      out_text VARCHAR2)
  5  AS
  6  BEGIN
  7    NULL;
  8  END;
  9  /

Procedure created.

SQL>
SQL> SELECT object_name,
  2    package_name,
  3    argument_name,
  4    position,
  5    data_type
  6  FROM USER_ARGUMENTS
  7  WHERE OBJECT_NAME = 'P';

OBJECT_NAM PACKAGE_NA ARGUMENT_NAME     POSITION DATA_TYPE
---------- ---------- --------------- ---------- ---------
P                     OUT_TEXT                 2 VARCHAR2
P                     IN_DATE                  1 DATE

SQL>

Answer 2

I'm unfamiliar with Oracle's flavor of regexes, but I made a pattern that works in most regex-engines. I know MySQL has a wildly different syntax so this is quite possibly also the case in Oracle, but the general idea is this:

create or replace table_name(?:(?!AS\s+BEGIN)(?:--[^\n]*(?:\n|$)|\/\*(?:[^*]|\*(?!\/))*\*\/|(?!\/\*|--)[^(]))*\(([^)]+)

Debuggex Demo

It's a "match this except in contexts A, B or C" approach also used here and explained in depth in this SO answer. Combined with the requirement that whatever we're matching isn't followed by AS\s+BEGIN.

You can see in the Debuggex Demo that the correct part between brackets is matched in capture group 1. As well, the 2nd create doesn't have parameters, and indeed nothing is matched (even though there's brackets in comments and after the AS BEGIN.

The task at hand is porting this to Oracle's regex flavor.