ora_lob_read failed: ORA-01403: no data found [message #649509] |
Tue, 29 March 2016 07:14 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/7f6015ff0d7045389aa7fc62f23caca4?s=64&d=mm&r=g) |
r144
Messages: 3 Registered: March 2016
|
Junior Member |
|
|
I have a perl script that reads and writes an oracle database. It is installed on a sun solaris platform.
I am in the process of migrating it to a Linux platform (ubuntu precise).
I have installed the oracle instantclient (12.1) on that machine and I can access and write oracle databases with no lobs. But this script does read and write lobs. And it fails with this message:
DBD::Oracle::db ora_lob_read failed: ORA-01403: no data found (DBD INVALID_HANDLE: OCILobRead) at /home/rdozijn/project/textpool/bin/../lib/Blobdownloader.pm line 269.
I have tried to find solutions on internet, but up till now I have not found a solution.
On the solaris machine, the script uses a library directory oracle10 with library files. I had to remove this library, since it contains solaris .so files. I hoped the recently installed DBD::Oracle and DBI module would make it unnecessary, but the result is the above message.
I hope you can advice me what I should do now.
Ruud
[Updated on: Tue, 29 March 2016 07:16] Report message to a moderator
|
|
|
|
Re: ora_lob_read failed: ORA-01403: no data found [message #649517 is a reply to message #649516] |
Tue, 29 March 2016 10:06 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/7f6015ff0d7045389aa7fc62f23caca4?s=64&d=mm&r=g) |
r144
Messages: 3 Registered: March 2016
|
Junior Member |
|
|
Hi Michel,
you are completely right. The database is on another server, and is accessed through the script. So that should not be an issue.
Concerning the code:
The relevant part of the script that causes the message is
# get content-blob
foreach my $f ('XML', 'SGM')
{
my $sql = "SELECT cnt_inhoud FROM content WHERE " .
"cnt_doc_id = '$id' " .
"AND cnt_formaat = '$f'";
my $sth = $dbh->prepare ($sql, { ora_auto_lob => 0}) ||
confess "execute error: $DBI::errstr";
$sth->execute () || return "Prepare error";
($locator) = $sth->fetchrow_array ();
$sth->finish();
$formaat = $f;
last if (defined ($locator));
}
if (! defined ($locator))
{
print STDERR "No *ML-blob present voor $id.\n" unless ($::silent);
return ($this->errorstatus_nothing_found(), undef);
}
my $file = "$dir/$prefix$id.OUT";
my $fh;
if (! open ($fh, ">$file"))
{
print STDERR "kan $file niet openen voor schrijven\n" unless($::silent);
return ($this->errorstatus_blobdump_faalde(), undef);
}
print Dumper $dbh;
print Dumper $locator;
my $chunk_size = 1024 * 5; # 5 K
my $offset = 1; # Offsets start at 1, not 0
# THIS ora_lob_read call CAUSES THE ERROR MESSAGE
while(my $data = $dbh->ora_lob_read ($locator, $offset, $chunk_size))
{
print $fh $data;
$offset += $chunk_size;
}
I hope this is sufficient to clarify the situation. If someone wants more info, please ask.
thanks, Ruud
|
|
|
Re: ora_lob_read failed: ORA-01403: no data found [message #649518 is a reply to message #649517] |
Tue, 29 March 2016 14:42 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
The problem is that you finish the statement and so can't read the lob afterwards.
If you move the $sth->finish after the loop on ora_lob_read it will work (you have also to move the variable declaration):
use strict;
use DBI;
use DBD::Oracle;
=cut
drop table content;
create table content (
cnt_doc_id integer primary key,
cnt_formaat varchar2(3),
cnt_inhoud clob
)
/
insert into content values (0,'XML',null);
insert into content values (1,'XML',empty_clob());
insert into content values (2,'XML','Michel Cadot');
commit;
=cut
print "Perl version: $^V\n";
print "DBI version: $DBI::VERSION\n";
print "DBD::Oracle version: $DBD::Oracle::VERSION\n";
my $dbh = DBI->connect ('dbi:Oracle:','michel','michel')
|| ( print "execute error: $DBI::errstr\n" && exit 1);
print "Database version: " .
($dbh->selectrow_array
("select version from product_component_version where product like 'Oracle%'")) .
"\n\n";
my $locator;
my $formaat;
my $sth;
for my $id (0,1,2) {
print "id=$id\n";
# get content-blob
foreach my $f ('XML') #, 'SGM')
{
my $sql = "SELECT cnt_inhoud FROM content WHERE " .
"cnt_doc_id = '$id' " .
"AND cnt_formaat = '$f'";
$sth = $dbh->prepare ($sql, { ora_auto_lob => 0}) ||
( print "prepare error: $DBI::errstr\n" && exit 1 );
$sth->execute () || ( print "execute error: $DBI::errstr\n" && exit 1);
($locator) = $sth->fetchrow_array ();
# $sth->finish();
$formaat = $f;
last if (defined ($locator));
}
if (! defined ($locator))
{
print STDERR "No *ML-blob present voor $id.\n" ;
next;
}
my $chunk_size = 1024 * 5; # 5 K
my $offset = 1; # Offsets start at 1, not 0
# THIS ora_lob_read call CAUSES THE ERROR MESSAGE
while(my $data = $dbh->ora_lob_read ($locator, $offset, $chunk_size))
{
# print $fh $data;
print "$data\n";
$offset += $chunk_size;
}
$sth->finish();
}
exit 0;
E:\>perl test_lob.pl
Perl version: v5.20.2
DBI version: 1.633
DBD::Oracle version: 1.74
Database version: 11.2.0.4.0
id=0
No *ML-blob present voor 0.
id=1
id=2
Michel Cadot
Note that you should use bind variables in your SQL statement and not put the variable values inside the SQL text.
|
|
|
Re: ora_lob_read failed: ORA-01403: no data found [message #649537 is a reply to message #649518] |
Wed, 30 March 2016 03:09 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
![](http://www.gravatar.com/avatar/7f6015ff0d7045389aa7fc62f23caca4?s=64&d=mm&r=g) |
r144
Messages: 3 Registered: March 2016
|
Junior Member |
|
|
Michel,
I have changed the script according to your advice. And it worked right away. Thanks a ton for your help. I was assuming there was something wrong with the environment. Wrong library or something like that. It appears that this kind of tunnel vision isn't very helpful. And it shows the value of forums like this!!!
best regards, Ruud
|
|
|