RE: Inserting CLOB values with DBD::Oracle

[Sterin, Ilya]

LongReadLen and LongTruncOk have nothing to do with inserting *LOB data.

Ilya 

-----Original Message-----
From: Kotha, Nagaraju
To: [EMAIL PROTECTED]
Sent: 2/19/02 2:51 PM
Subject: RE: Inserting CLOB values with DBD::Oracle

Here is the thing you need to do:

BEGIN
{
        $dbh = DBI->connect("connect_sting") or  die "$DBI::errstr \n";
            
        $dbh->{LongReadLen}=65000;
        $dbh->{LongTruncOk}=1;
}


    $sth->bind_param(1,$clob1,{ora_type=>ORA_CLOB,
ora_field=>'FIELDNAMEOFCLOB1'});
    $sth->bind_param(2,$clob2,{ora_type=>ORA_CLOB,
ora_field=>'FIELDNAMEOFCLOB2'});

read DBD::Oracle perldoc also.

--Raju


-----Original Message-----
From: Jeremy Keeler [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, February 19, 2002 1:49 PM
To: [EMAIL PROTECTED]
Subject: Inserting CLOB values with DBD::Oracle


Greetings dbi-users,

Does the newest version of DBD::Oracle fully support the SQL_CLOB type?

When I try to use bind_param() to bind character data to a CLOB field
using 
DBI::SQL_CLOB type in an Oracle 8 database, DBI is giving me the
message:

"SQL type 40 for ':p4' is not fully supported, bound as SQL_VARCHAR
instead 
at loadtable.pl line 131, <FH> line 5."

SQL type 40 is SQL_CLOB.

Here is the call to bind_param:
......
if( $COLTYPES->{$fieldno} eq "CLOB" ) {
        $sth->bind_param( $fieldno, $data, DBI::SQL_CLOB );
}
etc...
($fieldno does point to the correct field)

I also tried using DBD::Oracle qw(:ora_types); and then calling 
bind_param() as follows:
$sth->bind_param( $fieldno, $RS->{$k}, { ora_type => ORA_CLOB } );

That gave me the error message:
"DBD::Oracle::st execute failed: ORA-01461: can bind a LONG value only
for 
insert
  into a LONG column (DBD: oexec error) at loadtable.pl line 144, <FH>
line 
3285."

This second example works when I bind values that have a length < 4000 
because the driver treats the field as a VARCHAR which accepts up to
4000. 
It only breaks on data > 4000 characters. The second example
successfully 
loaded 3284 records. Record # 3285 is the first field > 4000.

I'm using DBD::Oracle 1.06 on Windows 2000 Server, ActivePerl 5.6.1
build 
613. My ppm thinks DBD::Oracle 1.06 is the current version so I'd like
to 
find out if it's going to do any good to go through the whole makefile 
routine for 1.12.

Is there another way to go about this that circumvents the problem?

TIA for any help anyone can offer,
Jeremy Keeler