As an example, I'd like to be able to write

SELECT
    *
FROM
    MY_SCHEMA.MY_TABLE@/*<DATABASE>*/MY_DB/*</DATABASE>*/ MY,
    YOUR_SCHEMA.YOUR_TABLE@/*<DATABASE>*/YOUR_DB/*</DATABASE>*/ YOUR
WHERE
    MY.JOIN_KEY = YOUR.JOIN_KEY
[download]

in my PL/SQL scripts, and have Perl be able to identify the database names.

Sound simple? Well, it seems to be...

To test how easy it was to extract the database names in this way, I made a small test script.

#! /usr/bin/perl -w

use strict ;
use warnings ;
use diagnostics ;
use Data::Dumper ;
$|++ ;

#---------------------------------------

print "1ST TRY\n------\n" ;

my @db_list_one = () ;
my $text_one = q(
    SELECT
        *
    FROM
        SYNERGEN.SA_ASSET@/*<DATABASE>*/SGENQA/*</DATABASE>*/
) ;

my $matches_one = 0 ;

if ( $text_one =~
    m/\/\*\s*<DATABASE>\s*\*\/(.*)\/\*\s*<\/DATABASE>\s*\*\//i )
{
    push( @db_list_one, $1 ) ;
    $matches_one++ ;
}

print Dumper( \@db_list_one ), "\n\n" ;

#---------------------------------------

print "2ND TRY\n------\n" ;

my @db_list_two = () ;
my $text_two = q(
    SELECT
        *
    FROM
        SYNERGEN.SA_ASSET@/*<DATABASE>*/SGENQA/*</DATABASE>*/,
        SYNERGEN.SA_WORK_ORDER@/*<DATABASE>*/SGENTEST/*</DATABASE>*/
) ;

my $text_two_copy = $text_two ;
my $matches_two = 0 ;

while ( $text_two_copy =~
    s/\/\*\s*<DATABASE>\s*\*\/(.*)\/\*\s*<\/DATABASE>\s*\*\///i )
{
    push( @db_list_two, $1 ) ;
    $matches_two++ ;
}

print Dumper( \@db_list_two ), "\n\n" ;
[download]

This gives me the following output:

1ST TRY
------
$VAR1 = [
          'SGENQA'
        ];


2ND TRY
------
$VAR1 = [
          'SGENQA',
          'SGENTEST'
        ];
[download]

which was exactly what I had hoped for. After working this all out, I'm left with two big questions about my code.

• In the second example, I make a copy of the string, then strip out the matches until they are gone. Is there a better way to do this?
• I was quite interested in the Death to Dot Star! thread, but between my wetware and the Camel book I couldn't find a way to adapt merlyn's or Ovid's regexes to do the job without things getting horribly messy. Is there a compact way to do this?

Thanks for reading. :-)

_______________
D a m n D i r t y A p e
Home Node | Email