Re: Oracle procedure raises exception but looks to be successful from DBI

Wow I go one right for once.

Not 100% sure on why that is? Me thinks when you use 'Begin End' it forces OCI to take it as an pseudo 'stored procedure' and runs in the current OCI client.

I think using call just executes as a thread off the present client and in the background someplace??

You might want to try 'exec proctest(?); '

I think it does it in the local client.

Will have to crack open the OCI docs to see and as I am not suffering from insomnia right now I have no real want to start digging though it right now.

Cheers

Martin Evans wrote:
John Scoles wrote:
That is an odd one, I will check with the DBAs here to see if this is normal Oracle behavior


Well the error id correct but and on my box an error is thrown that I catch

I did change my code over to

"begin track.proctest(?) end;"

what version of DBI and DBD are you using??

cheers



Thanks for the reply John.

I am using Oracle XE, DBI 1.59 and DBD::Oracle 1.19.

I changed my sql to "begin proctest(?); end;" and lo and behold I get an error:

$ perl procfail.pl
DBD::Oracle::st execute failed: ORA-01403: no data found
ORA-06512: at "XXX.PROCTEST", line 4
ORA-06512: at line 1 (DBD NO_DATA: OCIStmtExecute) [for Statement "begin proctest(?); end;" with ParamValues: :p1=99] at procfail.pl line 17.

Any idea why that is? Is it something to do with those tests in DBD::Oracle (dbdimp.c) for whether the statement is a select or not or perhaps because DBD::Oracle recognises this as a procedure call now.

I can change to put begin/end around procedure calls but I'd like to know why this is required.

Martin
.

Relevant Pages