2015年11月18日 星期三

Perl and Oracle BLOB

#!/usr/bin/perl

use DBI;

require "$ENV{HOME}/perl/setEnv.pl";

my $dbh = DBI->connect( "dbi:Oracle:$topprod", "$echoUser", "$echoPass" )
    || die( $DBI::errstr . "\n" );
$dbh->{LongReadLen} = 50000000;  # Make sure buffer is big enough for BLOB
my $sth = $dbh->prepare(q{
select regexp_substr(gca01,'ASO[789]-.*') oea01,
       gcb07,gcb09
  from gca_file,gcb_file
 where gca07 = gcb01
   and gca01 in (
                 select 'oea01='||oga16 gca01
                   from echo01.oga_file
                  where oga913 = to_date('20151001','yyyymmdd')
                    and regexp_like(oga01,'^ASH[789]-')
                )
}) || die "\nPrepare error: $DBI::err .... $DBI::errstr\n";;
$sth->execute() || die "\nExecute error: $DBI::err .... $DBI::errstr\n";

&blobSelect();

sub blobSelect()
{
   while (@data = $sth->fetchrow_array())
   {
      open FILE, "> $data[0]_$data[1]";
      print FILE $data[2];
      close FILE;
   }
   $sth->finish();
}

沒有留言:

張貼留言