I have never used it, but I would look into something like SQL::Statement if I were writing this kind of application.

my $data; $data .= $_ while (<>);
my @code = split(/[;\/]/, $data);
for my $line (@code) { ... }
[download]

That's kinda what I'm leaning towards doing right now, but it seems very memory-wasteful. A similar idea i had is to slurp in the file, replace all the '/' with ';' and then go back to parsing a new temp file. The major problem with just splitting on ';' or '/' is dealing with procedures (see sample code), which I've made a work-around for by reading in line by line.

create or replace procedure myProc
as
begin
  select * from whatever;
end;
[download]

As you can see, those types of PL/SQL statements have multiple ';'s but are technically one statement. But I've got a messy work-around where I match up begins and ends for that. Just so people know what problem I'm working with, here's some sample data:

delete from mytable
  where my_id='12345'
/

insert into anothertable
values ('123456','somedata');
/
[download]

Bit of a restructure, but you could do something like this:

my $place = 0;
my ($buffer, $line) = (undef, undef);
while (sysread INPUT, $buffer, 1, $place) {
   $place++;
   if ( $buffer=~ m{;|/} ) {
      my_process_line($line);
      $line = undef;
   }
   else {
      $line .= $buffer;
   }
}
[download]

There are, of course, problems with that code; but, the general concept should work. I don't know how fast it would be, but...

I would most often use this approach myself. It, at least, will handle any situation thrown at it.

Unfortunately you also have to execute my_process_line($line) after the while loop to deal with the very last line/block.

Also, you'd need to consider the impact if there were multiple matches within the buffered line.

Finally if there were multiple matches on the buffered line you'd want to discard the processed data but retain that portion of the buffer at the end that didn't match so it could be appended to the next buffer read.

That worked pretty well. Because I have another procedure to deal with things like PL/SQL procedures and functions, I had to rework it a little, so here's what I came up with. Another function calls this bit of code.

    my ($buffer, $line) = (undef, undef);
    my $read = true;
    while ($read)
    {
       $buffer = $curline->getline();
       if ( $buffer =~ m{;|/} ) 
       {
          $read = undef;
       }
       $line .= $buffer;
    }
        process_SQL($line);
[download]

That won't handle the case where the lines are stacked up right on top of each other, but I think it'll work for the cases where we need it to.