I tried using DBMS_LOB package and the problem -End of Communications Channel- got resolved. More over, now I can insert data up to 32KB, previously, it was limited to 4K.
I believe, you are getting the error only when you try inserting data greater than 4000 characters.
Here is an example on how to use DBMS_LOB:
my $block=<<TAG;
DECLARE
lobloc CLOB := EMPTY_CLOB();
buffer VARCHAR2(32000);
amount NUMBER := 320000;
offset NUMBER := 1;
BEGIN
--Initialize buffer with data to be inserted
buffer := '$clob_data';
amount := length(buffer);
--dbms_output.put_line(buffer);
--dbms_output.put_line(to_char(amount));
SELECT clob_column INTO lobloc -- get LOB handle
FROM table_name
WHERE column_value = '$value' FOR UPDATE;
dbms_lob.write(lobloc,amount,1,buffer);
COMMIT;
END;
$csr7 = $dbh_prod->prepare($block);
eval {
$rc = $csr7->execute();
};
if($@){
print "patch1 failed :: $bug_id \n";
print $search_stmt;
print " ORACLE Error:: $@ \n";
$email_string.="Failed at keyword update:$@\n";
return;
}
else{
print "patch1 worked\n";
}
It has got one more problem: If you are inserting or updating a null CLOB field, it will give an error. My work around was to insert/update with a space (' ') or any other character and execute $block after that. If you could find a better work around for this issue, let me know.
The only de-merit I found in using DBMS_LOB is that the process is a bit slow.
Hope this helps.
Thanks,
-Jibo
At 01:09 PM 11/13/2002 -0500, Wysocki, James (J.D.) wrote:
I still get the ORA-03113 (End of Communications Channel) when I don't use bind variables.
..
..
..
$sth->execute( $id, $text ) || die "Executing...".$dbh->errstr;
$dbh->commit() || die "During commit...".$dbh->errstr;
..
..
..
It's interesting that it actually "dies" during the Commit, not the execute. I believe the execute (a simple insert statement) kills the connection to the database.
-----Original Message-----
From: Sterin, Ilya (I.)
Sent: Wednesday, November 13, 2002 1:02 PM
To: Wysocki, James (J.D.); '[EMAIL PROTECTED]'
Subject: RE: Problem inserting into a CLOB
Try with no bind parameter. I don't believe you need one.
Ilya
-----Original Message-----
From: Wysocki, James (J.D.) [mailto:jwysock3@;ford.com]
Sent: Wednesday, November 13, 2002 10:55 AM
To: '[EMAIL PROTECTED]'
Subject: RE: Problem inserting into a CLOB
It appears that ORA_CLOB isn't defined in my version of DBD::Oracle. According to the documentation, ORA_CLOB = 112 so I tried putting in ora_type => 112 in my bind_param statement, but now I get "Can't bind :p2, ora_type 112 not a simple string type at clob2.pl line 33, <INFILE> chunk 1."
I'm guessing I'll need to get the sysadmins to update DBI.pm and DBD.pm.
Thanks for your help,
Jim
-----Original Message-----
From: Ronald J Kimball [mailto:rjk-dbi@;focalex.com]
Sent: Wednesday, November 13, 2002 10:23 AM
To: Wysocki, James (J.D.)
Cc: '[EMAIL PROTECTED]'
Subject: Re: Problem inserting into a CLOB
On Wed, Nov 13, 2002 at 10:15:18AM -0500, Wysocki, James (J.D.) wrote:
> Hi,
>
> I'm having a problem inserting a row into my database with a CLOB column.
> My database version is Oracle 8.1.7. I can get it to work as long as my
> CLOB contains < 4948 bytes. However, if it is >= 4948, I get an Oracle
> error ORA-03113 "end-of-file on communication channel".
>
> I'm using the following statement to bind my variable to my insert statement:
> $sth->bind_param(2, $file_text);
>
> I have the feeling that I should be doing something like
> "$sth->bind_param(2, $file_text, {TYPE=>SQL_???? );" but I can't find the
> correct TYPE.
You're on the right track. Check the docs for DBD::Oracle, under handling
LOBs. You need to import DBD::Oracle's type constants, and then specify
the type as ORA_CLOB. The documentation has examples.
Ronald
