Weird Oracle CLOB insert problem
From: Mark Coleman (mcoleman_at_insteel.com)
Date: 05/28/04
- Next message: Silvio Wanka: "build of DBD-Oracle 1.15 not possible using Oracle 8.0.6"
- Previous message: Soo Hom: "RE: make problem"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: 28 May 2004 09:14:57 -0700
Hi,
I'm working on some test code to insert and read clob and blob
objects from Oracle 9.2. I've got perl 5.8.4 and DBD-Oracle 1.15
installed. The code I am testing with is from the example page of the
DBD-Oracle documentation. I can run the code and it appears to work
with no problem, but when I look at the table in sqlplus I see 2 rows
inserted with the same lob_id, one with blank chardata and the other
with just a number in it. I did a select dbms_lob.getlength on the
data and one row is 0 and the other is 3 or 4 or 5, even though I put
multiple lines of text in the source file. If I just select the data
one row has a number in the chardata column and the other is empty.
Any help anyone can provide would be much appreciated.
Here is a copy of the code I was using. The sample table is
lob_example, lob_id number, chardata clob).
use strict;
use DBI;
my $dbh = DBI->connect('dbi:Oracle:edwtest',
'rep_user',
'rep_user') || die "Database connection not
made: $DBI::errstr";
print("Selecting next val\n");
my $lob_id = $dbh->selectrow_array( <<" SQL" );
SELECT lob_example_seq.nextval FROM DUAL
SQL
my $sth = $dbh->prepare( <<" SQL" );
INSERT INTO lob_example
( lob_id, chardata )
VALUES ( ?, ? )
SQL
print("Binding params\n");
use DBD::Oracle qw( :ora_types);
$sth->bind_param(1,$lob_id);
$sth->bind_param(2,'',{ ora_type => ORA_CLOB });
$sth->execute();
$sth->execute( $lob_id, '' );
print("Selecting lob for update\n");
$sth = $dbh->prepare( <<" SQL", { ora_auto_lob => 0 } );
SELECT chardata
FROM lob_example
WHERE lob_id = ?
FOR UPDATE
SQL
$sth->execute( $lob_id );
my ( $char_locator ) = $sth->fetchrow_array();
$sth->finish();
print("Opening the file\n");
open (CHAR_FH, "/oracle/perlstuff/test.txt") or die;
my $chunk_size = 4096; # Arbitrary chunk size
# BEGIN WRITING CHAR_DATA COLUMN
my $offset = 1; # Offsets start at 1, not 0
my $length = 0;
my $buffer = '';
while( $length = read( CHAR_FH, $buffer, $chunk_size ) ) {
$dbh->ora_lob_write( $char_locator, $offset, $length );
$offset += $length;
}
print("Finished\n");
$sth->finish();
$dbh->disconnect();
- Next message: Silvio Wanka: "build of DBD-Oracle 1.15 not possible using Oracle 8.0.6"
- Previous message: Soo Hom: "RE: make problem"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
