David, It turns out to be relatively trivial. Take the disconnect out of the execsql subroutine. Pull the disconnect to the main subroutine and I bet it will work.
Jeff > -----Original Message----- > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] > Sent: Sunday, February 03, 2002 1:13 PM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Cc: [EMAIL PROTECTED] > Subject: RE: DBI Oracle Database Handle trouble > > > 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- > > > > > >
