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

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



Martin Evans wrote:
Hi,

I've just spent a long time tracking down a bug in an Oracle procedure because DBI's execute method returned success even though the procedure raised an exception. Shouldn't exceptions raised in procedures cause execute to fail?

Here is an example:

use DBI;
use strict;
use warnings;

my $h = DBI->connect("xxx","xxx","xxx",{RaiseError=>1,PrintError=>1});
eval {$h->do("drop table test");};
$h->do("create table test (a integer)");
$h->do(q{create or replace procedure proctest(vv integer) as
x integer;
begin
select a into x from test where a = vv;
end;});

my $s = $h->prepare("call proctest(?)");
$s->bind_param(1, 99);
my $y = $s->execute;
print "$y\n";
print $s->err(), $s->errstr(), $s->state(), "\n";

which outputs:

0E0
Use of uninitialized value in print at procfail.pl line 19.
Use of uninitialized value in print at procfail.pl line 19.

If I run exactly the same procedure from sqlplus I get an error:

SQL> execute proctest(99);
BEGIN proctest(99); END;

*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at "BET.PROCTEST", line 4
ORA-06512: at line 1

If I replace the procedure with a single call to raise_application_error execute does fail.

I admit there is a bug in the procedure causing the exception which should have been caught but I was very surprised to find an exception in this procedure did not cause execute to fail.

Any ideas or suggestions.

Martin
.

Relevant Pages

  • Re: Oracle procedure raises exception but looks to be successful from DBI
    ... I am using Oracle XE, ... I've just spent a long time tracking down a bug in an Oracle procedure because DBI's execute method returned success even though the procedure raised an exception. ... If I replace the procedure with a single call to raise_application_error execute does fail. ...
    (perl.dbi.users)
  • Oracle procedure raises exception but looks to be successful from DBI
    ... I've just spent a long time tracking down a bug in an Oracle procedure because DBI's execute method returned success even though the procedure raised an exception. ... If I replace the procedure with a single call to raise_application_error execute does fail. ...
    (perl.dbi.users)
  • Re: System.Security.SecurityException was unhandled
    ... The exception gave you the CLSID. ... the first thing to check might be whether the COM server ... assembly actually has the permission in question. ... When I execute the application I received and error message. ...
    (microsoft.public.dotnet.security)
  • Re: collecting results in threading app
    ... but execute one at the time'. ... Could a Queue help me there? ... You can take a look at papyros, a small package I wrote for hiding ... # some exception was raised when executing this job ...
    (comp.lang.python)
  • Re: CLI Exception Handling
    ... the exception, but not on the same instruction that actually generated the ... functionality does exist in win32 SEH. ... handler executes all finally blocks that were lower on the stack have ... >> really can do is make a decision to continue searching or execute the ...
    (microsoft.public.dotnet.framework.clr)