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


.

Relevant Pages