Oracle procedure raises exception but looks to be successful fromDBI

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

Re: Oracle procedure raises exception but looks to be successfulfrom 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

Re: Oracle procedure raises exception but looks to be successfulfrom DBI

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
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> 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
>
>

Re: Oracle procedure raises exception but looks to be successfulfrom 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

Re: Oracle procedure raises exception but looks to be successfulfrom DBI

John Scoles wrote:
> 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(?); '

Doesn't work:

DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD
ERROR: OCIStmtExecute) [for Statement "exec proctest(?)" with
ParamValues: :p1=99] at procfail.pl line 17.

> 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

Your solution with begin/end works for:

begin proctest(?); end;

but does not work for:

begin user.package.proctest(?); end;

DBD::Oracle::st execute failed: ORA-06550: line 1, column 11:
PLS-00302: component 'PACKAGE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored (DBD ERROR: error possibly near <*> indicator
at char 10 in 'begin user.<*>package.proctest(:p1); end;') [for
Statement "begin user.package.proctest(?); end;" with ParamValues:
:p1=99] at procfail.pl line 17.

Any idea how to call a procedure in a package in a particular uses
schema because that would be a complete solution for me then?

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> 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
>
>

Re: Oracle procedure raises exception but looks to be successfulfrom DBI

I sort if expected that for the exec it is usualy only used withing SP
perhaps

"begin; exec(user.package.proctest(?)); end; " ?? who knows


The next error is usually a permission problem on the Oracle side.
Either the use who is calling the SP dose not have execute permission
for the package or something in the package (hard to debug that) or the
present state of the package is stopping you from executing it.

you might want to try

GRANT execute to the package


The fact that DBD is returning the error from Oracle usually means that
DBD is running correctly.

Cheers

Martin Evans wrote:
> John Scoles wrote:
>> 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(?); '
>
> Doesn't work:
>
> DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD
> ERROR: OCIStmtExecute) [for Statement "exec proctest(?)" with
> ParamValues: :p1=99] at procfail.pl line 17.
>
>> 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
>
> Your solution with begin/end works for:
>
> begin proctest(?); end;
>
> but does not work for:
>
> begin user.package.proctest(?); end;
>
> DBD::Oracle::st execute failed: ORA-06550: line 1, column 11:
> PLS-00302: component 'PACKAGE' must be declared
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored (DBD ERROR: error possibly near <*>
> indicator at char 10 in 'begin user.<*>package.proctest(:p1); end;')
> [for Statement "begin user.package.proctest(?); end;" with
> ParamValues: :p1=99] at procfail.pl line 17.
>
> Any idea how to call a procedure in a package in a particular uses
> schema because that would be a complete solution for me then?
>
> Martin

Re: Oracle procedure raises exception but looks to be successfulfrom DBI

John Scoles wrote:
> I sort if expected that for the exec it is usualy only used withing SP
> perhaps
>
> "begin; exec(user.package.proctest(?)); end; " ?? who knows

same problem as without exec:

PLS-00302: component 'PACKAGE' must be declared

> The next error is usually a permission problem on the Oracle side.
> Either the use who is calling the SP dose not have execute permission
> for the package or something in the package (hard to debug that) or the
> present state of the package is stopping you from executing it.
>
> you might want to try
>
> GRANT execute to the package
>
>
> The fact that DBD is returning the error from Oracle usually means that
> DBD is running correctly.
>
> Cheers

It is not a permission issue.

call user.package.proctest(?)

works and

begin user.package.proctest(?); end;

fails with the error above.

Thanks for the help though - it almost got me there. I'll keep
investigating.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com

> Martin Evans wrote:
>> John Scoles wrote:
>>> 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(?); '
>>
>> Doesn't work:
>>
>> DBD::Oracle::st execute failed: ORA-00900: invalid SQL statement (DBD
>> ERROR: OCIStmtExecute) [for Statement "exec proctest(?)" with
>> ParamValues: :p1=99] at procfail.pl line 17.
>>
>>> 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
>>
>> Your solution with begin/end works for:
>>
>> begin proctest(?); end;
>>
>> but does not work for:
>>
>> begin user.package.proctest(?); end;
>>
>> DBD::Oracle::st execute failed: ORA-06550: line 1, column 11:
>> PLS-00302: component 'PACKAGE' must be declared
>> ORA-06550: line 1, column 7:
>> PL/SQL: Statement ignored (DBD ERROR: error possibly near <*>
>> indicator at char 10 in 'begin user.<*>package.proctest(:p1); end;')
>> [for Statement "begin user.package.proctest(?); end;" with
>> ParamValues: :p1=99] at procfail.pl line 17.
>>
>> Any idea how to call a procedure in a package in a particular uses
>> schema because that would be a complete solution for me then?
>>
>> Martin
>
>

Re: Oracle procedure raises exception but looks to be successful

Not that I am offering you a solution, but with Oracle I have found the
error messages are sometimes misleading. For example I was helping a
co-worker with code. He wanted to call a stored procedure, so I sent
him some example code that I had which called a function. The function
I was calling returns a number so I use the :4 return in the code below.

$cpyh = $self->{_Dbh}->prepare_cached( q{
BEGIN
:4 := dots_ems.copy_project(:1, :2, :3);
END;
}, undef, AllowActive)
or $self->__SetError("\nget_new_projnum() - prepare() failed : \n$self->{_Dbh}->errstr\n");

$cpyh->bind_param(1, $self->{_Projnum});
$cpyh->bind_param(2, 'NEWPRO');
$cpyh->bind_param(3, '');
$cpyh->bind_param_inout(4, \$proj_num, 10);
$cpyh->execute;

return $proj_num;

My co-worker left the :4 param in thinking it would return a status. When he ran the code
Oracle would return a error message indicating that there was not a function named (what ever
he named it). I kept glazing over the error message, ran his code in debugger, and couldn't
figure out why it wouldn't work. Then it "slapped" me in the head, he is calling a procedure
not a function, there are returns from procedures. As soon as the placeholder :4 was taken out
it worked. I guess my point is Oracle assumed it was function, since there was out bind variable.
I think Oracle is making this assumption. The error should have been

"blah is a procedure and any idiot knows procedures don't return values."

....or something like that and I would have known right away why the prepare was failing.






On Fri, 2008-01-25 at 17:47 +0000, Martin Evans wrote:
> John Scoles wrote:
> > I sort if expected that for the exec it is usualy only used withing SP
> > perhaps
> >
> > "begin; exec(user.package.proctest(?)); end; " ?? who knows
>
> same problem as without exec:
>
> PLS-00302: component 'PACKAGE' must be declared
>
> > The next error is usually a permission problem on the Oracle side.
> > Either the use who is calling the SP dose not have execute permission
> > for the package or something in the package (hard to debug that) or the
> > present state of the package is stopping you from executing it.
> >
> > you might want to try
> >
> > GRANT execute to the package
> >
> >
> > The fact that DBD is returning the error from Oracle usually means that
> > DBD is running correctly.
> >
> > Cheers
>
> It is not a permission issue.
>
> call user.package.proctest(?)
>
> works and
>
> begin user.package.proctest(?); end;
>
> fails with the error above.
>
> Thanks for the help though - it almost got me there. I'll keep
> investigating.
>
> Martin

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

------=_Part_3800_31612839.1201322647885
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

begin proctest(?); end;

is the more desirable syntax for calling oracle procedures. It's an
anonymous pl/sql block. I personally had never seen the "call proctest()"
syntax used in Oracle. It's valid syntax, but it doesn't appear to be doing
what you want it to do.

According to the Oracle documentation:

Using the BEGIN .. END block is recommended in several situations. Calling
the subprogram from a BEGIN .. END block allows named or mixed notation for
parameters which the CALL statement does not support. For information on
named parameters, see "Using Positional, Named, or Mixed Notation for
Subprogram Parameters".
In addition, using the CALL statement can suppress an ORA-01403: no data
found error that has not been handled in the PL/SQL subprogram.

Pretty specific.

As far as

"begin user.package.proctest(11); end;"

failing....well it's right there in the error message. proctest is not in a
package called "package". In fact, there is no package named "package".

user.proctest(11) will fail as well since there is no user named "user".
user is a variable containing the name of the currently signed in user.

Try just username.proctest(11) where username is the user you are connecting
as.



On Jan 25, 2008 11:42 AM, 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
> --
> Martin J. Evans
> Easysoft Limited
> http://www.easysoft.com
>
> > 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
> >
> >
>



--
They who would give up an essential liberty for temporary security, deserve
neither liberty or security.
Benjamin Franklin

Our lives begin to end the day we become silent about things that matter.
Martin Luther King

The right of the people to be secure in their persons, houses, papers, and
effects, against unreasonable searches and seizures, shall not be violated,
and no warrants shall issue, but upon probable cause, supported by oath or
affirmation, and particularly describing the place to be searched, and the
persons or things to be seized.

Amendment IV to the Constitution of the United States

"I am not going to answer any questions as to my association, my
philosophical or religious beliefs or my political beliefs, or how I voted
in any election, or any of these private affairs. I think these are very
improper questions for any American to be asked, especially under such
compulsion as this."
Pete Seeger before the House Un-American Activities Comittee

------=_Part_3800_31612839.1201322647885--

Re: Oracle procedure raises exception but looks to be successfulfrom DBI

Jeffrey Seger wrote:
> begin proctest(?); end;
>
> is the more desirable syntax for calling oracle procedures. It's an
> anonymous pl/sql block. I personally had never seen the "call proctest()"
> syntax used in Oracle. It's valid syntax, but it doesn't appear to be doing
> what you want it to do.
>
> According to the Oracle documentation:
>
> Using the BEGIN .. END block is recommended in several situations. Calling
> the subprogram from a BEGIN .. END block allows named or mixed notation for
> parameters which the CALL statement does not support. For information on
> named parameters, see "Using Positional, Named, or Mixed Notation for
> Subprogram Parameters".
> In addition, using the CALL statement can suppress an ORA-01403: no data
> found error that has not been handled in the PL/SQL subprogram.
>
> Pretty specific.

Thanks for that, I did not see that.

> As far as
>
> "begin user.package.proctest(11); end;"
>
> failing....well it's right there in the error message. proctest is not in a
> package called "package". In fact, there is no package named "package".
>
> user.proctest(11) will fail as well since there is no user named "user".
> user is a variable containing the name of the currently signed in user.

I know that, it was just an example. Assuming there is a user called
fred and a package called test containing a procedure called proctest:

begin fred.test.proctest(11); end;

does not work where:

call fred.test.proctest(11)

does work.

> Try just username.proctest(11) where username is the user you are connecting
> as.

Thanks for documentation pointer.

Martin
--
Martin J. Evans
Easysoft Limited
http://www.easysoft.com


> On Jan 25, 2008 11:42 AM, 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
>> --
>> Martin J. Evans
>> Easysoft Limited
>> http://www.easysoft.com
>>
>>> 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
>>>
>
>
>

Re: Oracle procedure raises exception but looks to be successfulfrom DBI

On 25/01/08 15:25, 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?

[ snip ]

> my $s = $h->prepare("call proctest(?)");

This is nothing to do with Perl. It is native Oracle behaviour.

If you use the SQL CALL statement to call a procedure,
"No data found" exceptions will be quietly ignored.

This is specific to CALL and to the "No data found" exception.

Oracle support have insisted that it is not a bug. It is part
of the same mechanism by which

select (select * from dual where 0=1) from dual

returns a null value rather than an exception.

I have been severely burnt buy this, and now avoid CALL altogether.

--
Charles Jardine - Computing Service, University of Cambridge
cj10@cam.ac.uk Tel: +44 1223 334506, Fax: +44 1223 334679