Home » Open Source » Programming Interfaces » ora_lob_read failed: ORA-01403: no data found (perl5.14 DBI 1.634 DBD::Oracle 1.74 oracle instantclient 12.1)
ora_lob_read failed: ORA-01403: no data found [message #649509] Tue, 29 March 2016 07:14 Go to next message
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 #649516 is a reply to message #649509] Tue, 29 March 2016 08:18 Go to previous messageGo to next message
Michel Cadot
Messages: 65255
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

My advice: nobody can debug code he does not see.
Hints: Perl options are not the same ones, database data are not the same ones...

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 messageGo to next message
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 messageGo to next message
Michel Cadot
Messages: 65255
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
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

Previous Topic: synchronizing data from sql server
Next Topic: Oracle Password Reset using CONNECTION_PROPERTY_SET_NEW_PASSWORD
Goto Forum:
  


Current Time: Sat Dec 16 00:31:35 CST 2017

Total time taken to generate the page: 0.02400 seconds