Using Perl to read Oracle Long Raw column
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Using Perl to read Oracle Long Raw column

  1. #1
    Join Date
    Apr 2003
    Posts
    9

    Unhappy Using Perl to read Oracle Long Raw column

    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

  2. #2
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    Quote Originally Posted by 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
    How do you handle if the LONG col has more than 32K data?
    Your simple "SELECT long_col from TABLE" will not work.

    Tamil

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width