----- Original Message -----
From: Baranitharan Santhanagopalan <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Thursday, April 26, 2001 8:32 AM
Subject: problems in inserting clob data using DBI Perl
>
> hello,
> im using DBI::VERSION 1.6 and DBD::ORACLE::VERSION 0.59 on perl
5.0005_05 under AIX.
I thought DBI 1.5 was the latest version. And i think i use DBD Oracl 1.06.
> i tried to insert a data of large size into a oracle table which has
field of clob data_type.
> i encounter error like "Error : Insert Error ORA-01461: can bind a LONG
value only for insert into a LONG column (DBD: oex )"
>
> #!/usr/local/bin/dbiperl
> use DBI;
> use DBD::Oracle qw(:ora_types);
> use ebi_db;
> #connection estabilished with oracle
> $MainDBHandle->{LongReadLen}=64000;
No need to set lonreadlen when inserting
> $MainDBHandle->{LongTruncOk}=0;
> open(FILE,"< clobdata.txt") || die ("Open Error");
> while(<FILE>)
> {
> $tempval.=<FILE>;
> }
> close(FILE);
> $insertCursor=$MainDBHandle->prepare("insert into clob_test_table
(clob_fld_id,clob_fld_value) values (?,?)");
> $insertCursor->bind_param(1,1);
>
$insertCursor->bind_param(2,$tempval,{ora_type=>ORA_CLOB,ora_field=>clob_fld
_value});
The value for ora_field can be ommited here. But if you want to set it, you
have to quote it.
cuz it will now insert the value of the glob clob_fld_value which i think is
undef.
Put use strict at the top of your script.
> $insertCursor->execute(1,$tempval) || die("Insert Error $DBI::errstr \n");
> $insertCursor->finish() || die("Finish Error $DBI::errstr \n");
> #Connection disconnected .
>
> Environment:AIX.
> DBI::VERSION-1.06
> DBD::Oracle - 0.59.
> Perl 5.0005_05
>
> while trying the fetch the clob data i encountered error like "ORA-03115:
unsupported network datatype or representation (DBD: odescr failed) at
clobtest"
>
> #!/usr/bin/perl
> use ebi_db;
> use DBI;
> use DBD::Oracle qw(:ora_types);
>
> DBI->trace(3, "test.out");
> &DatabaseLogin();
> if (defined $DBI::err && $DBI::err != 0) {
> die("Login Error $DBI::errstr \n");
> }
> $MainDBHandle->{LongTruncOk} = 1;
> $MainDBHandle->{LongReadLen} = 64000;
Better set LongReadLen to the actual size of the data in the clob field.
> my $sql = "SELECT clob_fld_value FROM clob_test_table";
> my $sth = $MainDBHandle->prepare($sql) or die $DBI::errstr;
> $sth->execute or die $DBI::errstr;
> my (@data) = $sth->fetchrow or die $DBI::errstr if $DBI::err;
> my (@data) = $sth->fetchrow_array or die $DBI::errstr if $DBI::err;
> my $blob = '';
> my $lump = 4096; # use benchmarks to get best value for you
> my $offset = 0;
> while (1) {
> my $frag = $sth->blob_read(0, $offset, $lump);
why are you using blob read when the value is already in $data[0]??
> die $DBI::errstr if $DBI::err;
> last unless defined $frag;
> my $ll = length $frag;
> last unless $ll;
> $blob .= $frag;
> $offset += $ll;
> }
> print $blob;
> $sth->finish;
>
