Using Perl to read Oracle Long Raw column

[kennychw]

Hi all
I have the following in a Red Hat system
perl v5.6.1
perl-DBI-1.18
DBD-Oracle-1.08

I wrote a perl script to extract a long row column "doc" and dump the contents into a file. I copied and modified the code from http://search.cpan.org/~timb/DBD-Oracle-1.15/Oracle.pm

#! /usr/bin/perl
use DBI;
$dbh = DBI->connect("dbi:Oracle:wipmon", "wipmon", "wipmon",{AutoCommit => 0,RaiseError => 1})
or die "Can't connect to database!";
$dbh -> do(q{alter session set NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS'});
$dbh-> {'LongTruncOk'} = 1;
$dbh-> {'LongReadLen'} = 2000000;
$output_file = "data.xls";

open(MVOU,"> $output_file")
or die("Cannot open $output_file");

$sth = $dbh -> prepare_cached (q{select doc from FA@wip_directsp where _NO='AAAABBBB'});
$sth -> execute ()
or die ($dbh ->errstr."\nError at select distinct .");

my($char_locator)=$sth->fetchrow_array();

my $chunk_size=4096;
my $offset = 1; # Offsets start at 1, not 0

while( my $data = $dbh->func( $char_locator, $offset, $chunk_size,'ora_lob_read' ) )
{
print MVOU $data;
$offset += $length;
}

The problem is when perl executes the last 3rd line $dbh->func( $char_locator, $offset, $chunk_size,'ora_lob_read' ) )
It returns the following error
Can't locate DBI object method "ora_lob_read" via package "DBD::Oracle::db" at myperl.pl
I followed the web site instruction to use the func() function but it still does not work

Thanks for any help

[tamilselvan]

Hi all
I have the following in a Red Hat system
perl v5.6.1
perl-DBI-1.18
DBD-Oracle-1.08

I wrote a perl script to extract a long row column "doc" and dump the contents into a file. I copied and modified the code from http://search.cpan.org/~timb/DBD-Oracle-1.15/Oracle.pm

#! /usr/bin/perl
use DBI;
$dbh = DBI->connect("dbi:Oracle:wipmon", "wipmon", "wipmon",{AutoCommit => 0,RaiseError => 1})
or die "Can't connect to database!";
$dbh -> do(q{alter session set NLS_DATE_FORMAT='YYYY-MON-DD HH24:MI:SS'});
$dbh-> {'LongTruncOk'} = 1;
$dbh-> {'LongReadLen'} = 2000000;
$output_file = "data.xls";

open(MVOU,"> $output_file")
or die("Cannot open $output_file");

$sth = $dbh -> prepare_cached (q{select doc from FA@wip_directsp where _NO='AAAABBBB'});
$sth -> execute ()
or die ($dbh ->errstr."\nError at select distinct .");

my($char_locator)=$sth->fetchrow_array();

my $chunk_size=4096;
my $offset = 1; # Offsets start at 1, not 0

while( my $data = $dbh->func( $char_locator, $offset, $chunk_size,'ora_lob_read' ) )
{
print MVOU $data;
$offset += $length;
}

The problem is when perl executes the last 3rd line $dbh->func( $char_locator, $offset, $chunk_size,'ora_lob_read' ) )
It returns the following error
Can't locate DBI object method "ora_lob_read" via package "DBD::Oracle::db" at myperl.pl
I followed the web site instruction to use the func() function but it still does not work

Thanks for any help

How do you handle if the LONG col has more than 32K data?
Your simple "SELECT long_col from TABLE" will not work.

Tamil