CLOB Oracle 9i select issue

• Previous message: Donald A Slanina: "DBD DBI Software levels"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Date: 19 Dec 2003 16:32:23 -0800

Hi to all,

I am having the darnedest problem with selecting clobs from our Oracle
9.2i database. (Using Perl 5.6.1 on a WinXP machine.)

I tried the simple "select <clob_field_name> from <table_with_clob>
where <unique id>= ?", and I got the error
" -> fetchrow_array for DBD::Oracle::st
(DBI::st=HASH(0x1be156c)~0x1be159c) thr#0183F278
    !! ERROR: 1406 'ORA-01406: fetched column value was truncated
(DBD: ORA-01406 error on field 1 of 1, ora_type 112)'
    <- fetchrow_array= undef row1 at CAdatabase.pm line 364
DBD::Oracle::st fetchrow_array failed: ORA-01406: fetched column value
was truncated (DBD: ORA-01406 error on field 1 of 1, ora_type 112) at
c:/CA_Perl/CA_lib/CAdatabase.pm line 364.
can't execute line 223"

So I thought, I've exceeded the 4K limit, maybe I need to change
$dbh->{LongReadLen} (which I had set to 748363). But that didn't
work, so I thought, maybe I need to tell it I'm asking for a CLOB and
bind a parameter, and maybe get it in pieces.
Here's what I did:

use DBD::Oracle qw(:ora_types);
use strict;

my $get_clob = $dbh->prepare("DECLARE
                x CLOB := EMPTY_CLOB();
        BEGIN
                SELECT crr_print_image
                INTO x
                FROM crr
                WHERE crr_id = 2053495;
        :y := x;
        END;");

my $perl_pclob = '';
my %ora = (
        'ora_type'=>ORA_CLOB,
        'ora_field'=>'crr_print_image',
);
$get_clob->bind_param_inout(":y",
                        \$perl_pclob,
                        300,
                        \%ora,
                        );
# (I did try other values than 300 here: up to 30000)

$get_clob->execute() or die "AAAAAAAAAA line ".__LINE__;
                                                        
print "report is $perl_pclob\n";
---------------
But this, unfortunately, yielded the following:

Database connection seems to be established for quickclob.pl:
DBI::db=HASH(0x21564f0)!
    DBI 1.37-ithread dispatch trace level set to 2
    -> prepare for DBD::Oracle::db (DBI::db=HASH(0x21564f0)~0x21540f4
'DECLARE
                x CLOB := EMPTY_CLOB();
        BEGIN
                SELECT crr_print_image
                INTO x
                FROM crr
                WHERE crr_id = 2053495;
        :y := x;
        END;') thr#0183F258
    dbd_preparse scanned 1 distinct placeholders
    <- prepare= DBI::st=HASH(0x2035be4) at quickclob.pl line 25
    -> bind_param_inout for DBD::Oracle::st
(DBI::st=HASH(0x2035be4)~0x1835194 ':y' SCALAR(0x2158d7c) 300
HASH(0x21597f4)) thr#0183F258
       bind :y <== '' (type 0, inout 0x2158d7c, maxlen 300, attribs:
HASH(0x21597f4))
       bind :y <== '' (size 0/1/300, ptype 4, otype 8, inout)
    <- bind_param_inout= 1 at quickclob.pl line 40
    -> execute for DBD::Oracle::st (DBI::st=HASH(0x2035be4)~0x1835194)
thr#0183F258
    dbd_st_execute (for sql f34 after oci f62, out1)...
      with :y = '' (len 0/300, indp 0, otype 8, ptype 6)
    !! ERROR: 6502 'ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 8 (DBD: oexec error)'
    <- execute= undef at quickclob.pl line 45
DBD::Oracle::st execute failed: ORA-06502: PL/SQL: numeric or value
error
ORA-06512: at line 8 (DBD: oexec error) at
C:\CA_Perl\ca_automation\crrs\quickclob.pl line 45.
AAAAAAAAAA line 45 at C:\CA_Perl\ca_automation\crrs\quickclob.pl line
45.
    -> DESTROY for DBD::Oracle::st (DBI::st=HASH(0x1835194)~INNER)
thr#0183F258
       error: 6502 'ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 8 (DBD: oexec error)'
    <- DESTROY= undef
    -> DESTROY for DBD::Oracle::db (DBI::db=HASH(0x21540f4)~INNER)
thr#0183F258
Issuing rollback() for database handle being DESTROY'd without
explicit disconnect().
       error: 6502 'ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 8 (DBD: oexec error)'
    <- DESTROY= undef

main, C:\CA_Perl\ca_automation\crrs\quickclob.pl, 0, CAutil::END, 1, 0
main, C:\CA_Perl\ca_automation\crrs\quickclob.pl, 0, (eval), 0, 0
-- DBI::END
    -> disconnect_all for DBD::Oracle::dr
(DBI::dr=HASH(0x1b64ec8)~0x2156514) thr#0183F258
    <- disconnect_all= '' at DBI.pm line 649
! -> DESTROY for DBD::Oracle::dr (DBI::dr=HASH(0x2156514)~INNER)
thr#0183F258
! <- DESTROY= (not implemented) during global destruction

Can anyone explain to me why I have a numeric or value error? This
seems very complicated for what I'd optimistically hoped would be a
simple select. Most of the clobs in the table are pretty big, though.
I'd appreciate any help anyone can give!

Thanks!!
Becka Louden

• Previous message: Donald A Slanina: "DBD DBI Software levels"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]