Re: Oracle procedure raises exception but looks to be successful from DBI
- From: jseger@xxxxxxxxx (Jeffrey Seger)
- Date: Fri, 25 Jan 2008 23:44:07 -0500
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"<http://localhost/oracle/B19306_01/appdev.102/b14261/subprograms.htm#i4072>.
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 <martin.evans@xxxxxxxxxxxx> wrote:
John Scoles wrote:
That is an odd one, I will check with the DBAs here to see if this iscatch
normal Oracle behavior
Well the error id correct but and on my box an error is thrown that I
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
- Follow-Ups:
- Re: Oracle procedure raises exception but looks to be successful from DBI
- From: Martin Evans
- Re: Oracle procedure raises exception but looks to be successful from DBI
- References:
- Oracle procedure raises exception but looks to be successful from DBI
- From: Martin Evans
- 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
- From: Martin Evans
- Oracle procedure raises exception but looks to be successful from DBI
- Prev by Date: Re: SSL connection with DBD::Oracle?
- Next by Date: Re: Oracle procedure raises exception but looks to be successful from DBI
- Previous by thread: Re: Oracle procedure raises exception but looks to be successful from DBI
- Next by thread: Re: Oracle procedure raises exception but looks to be successful from DBI
- Index(es):
Relevant Pages
|
