Oracle procedure raises exception but looks to be successful from DBI
- From: martin.evans@xxxxxxxxxxxx (Martin Evans)
- Date: Fri, 25 Jan 2008 15:25:58 +0000
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
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com
.
- Follow-Ups:
- Re: Oracle procedure raises exception but looks to be successful from DBI
- From: Charles Jardine
- Re: Oracle procedure raises exception but looks to be successful from DBI
- From: John Scoles
- Re: Oracle procedure raises exception but looks to be successful from DBI
- Prev by Date: New drawing tool: DeZign for Databases
- Next by Date: Re: compatibility matrix
- Previous by thread: New drawing tool: DeZign for Databases
- Next by thread: Re: Oracle procedure raises exception but looks to be successful from DBI
- Index(es):
Relevant Pages
|
