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
