Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Oracle Db Link Error ORA-01001: invalid cursor

by mediocremorsov (Initiate)
on Jan 09, 2017 at 00:45 UTC ( #1179194=perlquestion: print w/replies, xml ) Need Help??

mediocremorsov has asked for the wisdom of the Perl Monks concerning the following question:

I am trying to debug performance issues with a perl file that runs a snippet of code similar to psuedo code like below. This script is running on a Unix machine with a db connection to a local Oracle 11.2.0 database.

my $sth1 = $localDB->prepare("< Some select query that returns mor +e 100000 records (1 column) >"); my $sth2 = $localDB->prepare("< Inner select query that uses the v +alues (id) from the previous sql >"); $sth1->execute(); my ($id); $sth1->bind_columns( undef, \$id); while ( $sth1->fetch() ) { ... $sth2->execute($id); my $dblinkname; while (($dblinkname) = $sth2->fetchrow_array()) { my $dblink_sql = "<Innermost select query that dynamically + creates a sql query with $dblinkname>"); E.G. <select * from table@dblinkname;> eval { my $dblink_sth = $localDB->prepare($dblink_sql); <-- +INV CURSOR HERE $dblink_sth->execute($id); <-- +EXECUTE FAILED HERE ... $dblink_sth->finish(); }; if ($@) { <log errors> next; } } ... $sth2->finish(); ... } $sth1->finish();

There are some machines that do not exist and executing the dblink query for those sql statements would throw the database not reachable error. However, every time I run the perl file the prepare statement marked above throws an invalid cursor (ORA-01001: invalid cursor) error and some times the execute statement right below throws an execute failed (DBD::Oracle::st fetchrow_array failed: ERROR no statement executing). I am currently at a loss on how to troubleshoot this script. Any pointers would be greatly appreciated.

Replies are listed 'Best First'.
Re: Oracle Db Link Error ORA-01001: invalid cursor
by GotToBTru (Prior) on Jan 09, 2017 at 21:56 UTC

    If it is the prepare statement failing, it seems likely the problem is with your SQL. You don't show us that so it's hard to tell. Also, are you sure your database connection is being created successfully?

    But God demonstrates His own love toward us, in that while we were yet sinners, Christ died for us. Romans 5:8 (NASB)

      The innermost select query is in a loop that queries across multiple databases. And the query works for some databases and for some the prepare statement throws an error. The tablename is the same across databases For e.g
      select * from customers@db_01; <-- This will execute ... ... select * from customers@db_08; <-- This one will throw a prepare fail.
      I am currently investigating if the way I open my local DB connection with AutoCommit has got anything to do with this.

        What is the relationship between id and linkname, is it 1 to 1, 1 to many or many to 1 ?


Log In?

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1179194]
Approved by kcott
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others taking refuge in the Monastery: (2)
As of 2022-09-30 19:56 GMT
Find Nodes?
    Voting Booth?
    I prefer my indexes to start at:

    Results (126 votes). Check out past polls.