Jeff, Thanks for your reply.
First of all, the versions of software I'm running are:
Oracle8i Enterprise Edition Release 8.1.7.2.0
perl5 (5.0 patchlevel 4 subversion 4)
Solaris 2.6
The sample of code which I'm using to test is attached (test.pl), and
the output
I get (including an echo of the first SQL, a result status, an echo of
the
2nd SQL, and then the error) is as follows:
BEGIN sp_exec_imm('drop index CMBUS1'); END;
Return Status: 1
select trade_date, host_cust_id from cm_trade_fact where trade_id
= 210960459
DBD::Oracle::db prepare failed: (DBD INVALID_HANDLE:
OCIStmtPrepare) at test.pl line 86.
(DBD INVALID_HANDLE: OCIStmtPrepare) at test.pl line 86.
If I reverse the order of the two SQL statements, to prove that the 2nd
works, then
the output is as follows:
select trade_date, host_cust_id from cm_trade_fact where trade_id
= 210960459
Return Status: 1
15-MAY-01, WA181862
BEGIN sp_exec_imm('drop index CMBUS1'); END;
DBD::Oracle::db do failed: (DBD INVALID_HANDLE: OCIStmtPrepare)
at test.pl line 109.
(DBD INVALID_HANDLE: OCIStmtPrepare) at test.pl line 109.
Finally, if I use a completely separate database handle for the 2nd SQL
statement,
(attached is the code used for this, called test2.pl) it works:
select trade_date, host_cust_id from cm_trade_fact where trade_id
= 210960459
Return Status: 1
15-MAY-01, WA181862
BEGIN sp_exec_imm('drop index CMBUS1'); END;
Return Status: 1
Thanks for taking a look!
-Dave-
-----Original Message-----
From: jurlwin
Sent: Saturday, February 02, 2002 3:42 PM
To: wrendave; dbi-users
Cc: jurlwin; Tim.Bunce; Wren, David
Subject: RE: DBI Oracle Database Handle trouble
David,
It would be helpful if you attached a small script which demonstrates
the
error.
I have had numerous instances using Oracle to have multiple sql
statements
executed and, in fact some executing concurrently. All using the same
connection.
Regards,
Jeff
>
> I am a novice Perl DBI programmer currently having trouble referencing
> the same
> database handle (in Main) from within REPEATED calls to a perl
> subroutine I wrote as an engine
> to process multiple SQL statements (one call per SQL statement).
>
> (I'm on Solaris 2.6.???, Oracle 8i, Perl5????)
>
> In other words, I get a "db prepare" error when I send the 2nd SQL
> statement from Main into
> the handle in the subroutine. The first statement executes fine and I
> get the results I want
> back from the database. It's just that 2nd pass to the same handle
that
> kills the program.
>
> I was able to fix the problem by passing the 2nd SQL statement
through a
> separate database
> handle (I pass both the database and statement handles to the
> subroutine). I have been
> playing with the object "finish" but haven't had any luck with it
> resolving the problem.
>
> But I thought Oracle DBI database handles were able to process
multiple
> statement handles??????? I'd really like to use only one database
> handle for all
> statement handles because I've got a lot of SQL statements to process.
> I think it is
> ridiculous that I would have to create so many database handles to
> access the same database.
>
> Any suggestions would be GREATLY appreciated. I'm sorry I didn't
supply
> more
> detail, but I'm not at my UNIX machine right now and am in a tight
pinch
> to get this done).
>
> THANKS A LOT for any help. Please reply to both my addresses:
> [EMAIL PROTECTED]
> and [EMAIL PROTECTED]
>
> -Dave-
>
>
test.pl
Description: Binary data
test2.pl
Description: Binary data
Visit our website at http://www.ubswarburg.com This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately by e-mail if you have received this e-mail by mistake and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be secure or error-free as information could be intercepted, corrupted, lost, destroyed, arrive late or incomplete, or contain viruses. The sender therefore does not accept liability for any errors or omissions in the contents of this message which arise as a result of e-mail transmission. If verification is required please request a hard-copy version. This message is provided for informational purposes and should not be construed as a solicitation or offer to buy or sell any securities or related financial instruments.
