my @map_cols = qw( a.mapping a.path_prefix a.parent_mapping
                   a.parent_element a.rule a.data_nature
                   a.table_suffix b.element b.column_name
                   b.priority_local b.priority_global );

my $map_sql = 'SELECT ' . join( ',', @map_cols ) .
  'FROM cor_ekl_map a, cor_ekl_map_dfn b
  WHERE a.mapping = ? and a.mapping = b.mapping';

my $map_sth = $dbh->prepare( $map_sql );

my @proc_cols = qw(a.process_id a.process_ts a.process_stage
                   b.ekl_set b.mapping);

my $proc_sql = 'SELECT ' . join( ',', @proc_cols ) .
  'FROM sys_ekl_ipt_001 a, cor_ekl_set_dfn b
  WHERE a.ekl_set = b.ekl_set';

my $proc_sth = $dbh->prepare( $proc_sql );

s/^[ab]\.// for ( @map_cols, @proc_cols );  # don't need table prefixes now

my $regex_sth = $dbh->prepare( 'SELECT regex FROM cor_ekl_rul WHERE rule = ?' );
my $cdata_sth = $dbh->prepare( 'SELECT column_name column_type
  FROM cor_dat_col WHERE data_nature = ? AND table_suffix = ?' );

$proc_sth->execute;

while ( my $proc_row = $proc_sth->fetchrow_arrayref ) 
{
    my %procdata;
    @procdata{@proc_cols} = @$proc_row;

    $map_sth->execute( $procdata{"mapping"} );

    while ( my $map_row = $map_sth->fetchrow_arrayref ) 
    {
        my %mapdata;
        @mapdata{@map_cols} = @$map_row;

        my $table_name = join( '_', 'dat',
                               $mapdata{data_nature},
                               $mapdata{table_suffix} );

        $regex_sth->execute( $mapdata{rule} );
        my ( $regex ) = $regex_sth->fetchrow_array;

        # and so on...
    }
}