regex: separating procedure/function from PL/SQL code

Question

I am writing Perl regex code to separate out PL/SQL procedure from the package.

Each procedure starts with the PROCEDURE key word and ends with END, But the END is for BEGIN, IF, or LOOP. There may be many BEGIN|IF|LOOP.

Below is the kind of input, I want to separate each procedure. How can I do this?

PROCEDURE LOG_ECS_MSG( MD_CURR  CHAR,
                     MSG_TIME DATE ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
BEGIN
INSERT INTO INFO_SERV_ECS_LOG
VALUES ( MD_CURR, MD, SVTY, ACT_DATE, ACCT, MSG_TXT, MSG_TIME);
COMMIT;
END;
END;




PROCEDURE LOG_ECS_MSG( MD_CURR  CHAR,
                     MSG_TIME DATE ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF....
INSERT INTO INFO_SERV_ECS_LOG
VALUES ( MD_CURR, MD, SVTY, ACT_DATE, ACCT, MSG_TXT, MSG_TIME);
COMMIT;
END IF
END;

Answer 1

The easiest way to do this is to logically separate the text based on the occurrence of PROCEDURE:

undef $/;
my $line = <DATA>;
my @proc = split /(?=^PROCEDURE )/m, $line;
use Data::Dumper;
die Dumper \@proc;

Answer 2

For a quick and dirty solution looking for the token PROCEDURE could be sufficient for separating the code blocks. But if you want this to be a mature solution you should really try to find (or write) a parser for PL/SQL and use this instead. Otherwise you can't handle occurences of the token PROCEDURE inside of strings or comments.

Answer 3

Try this :

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;

undef $/;
my $line = <DATA>;
my @procedures = split /(?=PROCEDURE)/, $line;
print Dumper(\@procedures);

__DATA__
PROCEDURE LOG_ECS_MSG( MD_CURR  CHAR,
                     MSG_TIME DATE ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
BEGIN
INSERT INTO INFO_SERV_ECS_LOG
VALUES ( MD_CURR, MD, SVTY, ACT_DATE, ACCT, MSG_TXT, MSG_TIME);
COMMIT;
END;
END;




PROCEDURE LOG_ECS_MSG( MD_CURR  CHAR,
                     MSG_TIME DATE ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF....
INSERT INTO INFO_SERV_ECS_LOG
VALUES ( MD_CURR, MD, SVTY, ACT_DATE, ACCT, MSG_TXT, MSG_TIME);
COMMIT;
END IF
END;

Output:

$VAR1 = [
          'PROCEDURE LOG_ECS_MSG( MD_CURR  CHAR,
                     MSG_TIME DATE ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
BEGIN
INSERT INTO INFO_SERV_ECS_LOG
VALUES ( MD_CURR, MD, SVTY, ACT_DATE, ACCT, MSG_TXT, MSG_TIME);
COMMIT;
END;
END;




',
          'PROCEDURE LOG_ECS_MSG( MD_CURR  CHAR,
                     MSG_TIME DATE ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF....
INSERT INTO INFO_SERV_ECS_LOG
VALUES ( MD_CURR, MD, SVTY, ACT_DATE, ACCT, MSG_TXT, MSG_TIME);
COMMIT;
END IF
END;'
        ];