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
