Re: Oracle procedure raises exception but looks to be successful from DBI


Not that I am offering you a solution, but with Oracle I have found the
error messages are sometimes misleading. For example I was helping a
co-worker with code. He wanted to call a stored procedure, so I sent
him some example code that I had which called a function. The function
I was calling returns a number so I use the :4 return in the code below.

$cpyh = $self->{_Dbh}->prepare_cached( q{
BEGIN
:4 := dots_ems.copy_project(:1, :2, :3);
END;
}, undef, AllowActive)
or $self->__SetError("\nget_new_projnum() - prepare() failed : \n$self->{_Dbh}->errstr\n");

$cpyh->bind_param(1, $self->{_Projnum});
$cpyh->bind_param(2, 'NEWPRO');
$cpyh->bind_param(3, '');
$cpyh->bind_param_inout(4, \$proj_num, 10);
$cpyh->execute;

return $proj_num;

My co-worker left the :4 param in thinking it would return a status. When he ran the code
Oracle would return a error message indicating that there was not a function named (what ever
he named it). I kept glazing over the error message, ran his code in debugger, and couldn't
figure out why it wouldn't work. Then it "slapped" me in the head, he is calling a procedure
not a function, there are returns from procedures. As soon as the placeholder :4 was taken out
it worked. I guess my point is Oracle assumed it was function, since there was out bind variable.
I think Oracle is making this assumption. The error should have been

"blah is a procedure and any idiot knows procedures don't return values."

....or something like that and I would have known right away why the prepare was failing.






On Fri, 2008-01-25 at 17:47 +0000, Martin Evans wrote:
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
.

Relevant Pages

  • Re: ORA-03117 resulting from package procedure parameter record types
    ... I am at a client who has recently migrated from Oracle 7.3.4 to Oracle ... number of the package procedures, ... procedure proc1 is ... bind variables fails with the oracle error message "ORA-03117: ...
    (comp.databases.oracle.misc)
  • Re: Hooking up an Oracle Linked Server...
    ... I ran the DBCC statement and then ... this is the error message I got back: ... Set Up and Troubleshoot a Linked Server to Oracle in SQL ...
    (microsoft.public.sqlserver.setup)
  • Re: SQL Query Invalid operation
    ... > The tables are linked tables from an oracle database and there were ... I gather, then, that these are existing queries and you are setting ... control over the design of the Oracle tables. ... Do you get the error message when you open a recordset or otherwise run ...
    (microsoft.public.access.modulesdaovba)
  • Re: cannot convert between unicode and non-unicode data types
    ... Mine is also Char 9 in Oracle, using the data transformation of dstr does not ... product can't actually produce a meaningful error message. ... oracle and the sql database. ...
    (microsoft.public.sqlserver.dts)
  • UTL_FILE.OPEN
    ... I'm running an older version of Oracle 8.1.7 and I'm using the UTL_FILE ... package to create a csv file. ... 10 meg and I keep getting an error message after several rows of data have ...
    (comp.databases.oracle.server)