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


.

Relevant Pages

  • Nested packages not firing events when executed via .NET interop
    ... This works fine on individual packages, but when I execute a package ... void PackageEvents.OnError(string EventSource, int ErrorCode, ...
    (microsoft.public.sqlserver.dts)
  • Re: Oracle procedure raises exception but looks to be successful from DBI
    ... I sort if expected that for the exec it is usualy only used withing SP ... Either the use who is calling the SP dose not have execute permission for the package or something in the package or the present state of the package is stopping you from executing it. ... The fact that DBD is returning the error from Oracle usually means that DBD is running correctly. ...
    (perl.dbi.users)
  • Re: Using Python in ActiveX task - works when run as separate step
    ... when executing the package. ... Peter Yang, if you can detail some of what you did to get Perl working, I'd ... >> was trying to use Python in this case because I know it a little better than ... >>>You could execute the step in DTS package window. ...
    (microsoft.public.sqlserver.dts)
  • Re: alter index (Oracle Text index) rebuild on different schema => no rights
    ... I put the "alter index" statement in a stored procedure and put it in ... The execution of the package has to ... Execute it dynamically as a workaround. ... REBUILD statement without proper PARAMETERS clause (which ...
    (comp.databases.oracle.misc)
  • Not all steps in DTS package execute
    ... When run through DTS Designer, all steps execute except the last one (Step ... DTSRun OnFinish: DTSStep_DTSActiveScriptTask_2 ... Microsoft Data Transformation Services Package ...
    (microsoft.public.sqlserver.dts)