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 <[EMAIL PROTECTED]> 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
