Re: Calling procedure

On Aug 26, 5:47 pm, "gym dot scuba dot kennedy at gmail" <kenned..._at_verizon.net> wrote:
> "Mtek" <m..._at_mtekusa.com> wrote in message
>
> news:d16cf8eb-1824-4362-89ee-53179b5d43d5_at_k36g2000pri.googlegroups.com...
>
>
>
> > On Aug 26, 3:43 am, William Robertson <williamr2..._at_googlemail.com>
> > wrote:
> >> On Aug 25, 9:36 pm, Mtek <m..._at_mtekusa.com> wrote:
>
> >> > On Aug 25, 3:31 pm, t..._at_panix.com (Dan Blum) wrote:
>
> >> > I did try prefixing the schema name also:
>
> >> > INSERT_PORTFOLIO_ARR (
> >> >     p_user_id        => p_customer_id,
> >> >     p_portfolio_id   => p_portfolio_id,
> >> >     p_ticker         => v_ticker_tab,
> >> >     p_action         => v_action_tab);
>
> >> > CREATE OR REPLACE PROCEDURE CUSTOMER.INSERT_PORTFOLIO_ARR (
> >> >      p_user_id        NUMBER,
> >> >      p_portfolio_id   NUMBER DEFAULT NULL,
> >> >      p_line           IN_STR_ARR,
> >> >      p_ticker         IN_STR_ARR,
> >> >      p_price          IN_STR_ARR,
> >> >      p_shares         IN_STR_ARR,
> >> >      p_date           IN_STR_ARR,
> >> >      p_comm           IN_STR_ARR,
> >> >      p_action         IN_STR_ARR,
> >> >      p_portfolio_name VARCHAR2 DEFAULT NULL,
> >> >      p_portfolio_des  VARCHAR2 DEFAULT NULL,
> >> >      product          VARCHAR2 DEFAULT NULL) IS
>
> >> I think the suggestion was to prefix the schema name when calling the
> >> procedure, not when creating it.
>
> >> btw, calling an invalid procedure will give:
>
> >> PLS-00905: object [procedurename] is invalid
>
> >> Calling a procedure passing the wrong number of types of arguments
> >> will give:
>
> >> PLS-00306: wrong number or types of arguments in call to
> >> '[procedurename]'
>
> >> If you get "PLS-00302: component 'INSERT_PORTFOLIO_ARR' must be
> >> declared", it means INSERT_PORTFOLIO_ARR does not exist in the
> >> specified scope, that is, either it does not exist at all, or else it
> >> exists in some other schema and you didn't specify the schema or you
> >> don't have privileges to execute it, or both.
>
> > Thanks for all your suggestions.  I'll give some of them a try.  But
> > if the number of parameters was the problem, then it would have/should
> > have given the # of parameters incorrect error.  So, it must be
> > something else......but I know it exists.
>
> > I'll keep grasping for straws and post something else soon.
>
> > Thank everyone again...........
>
> Not necessarily.  Oracle tries to match up the procedure signatures by type
> and count.  If it cannot procedure signatures by type and count.  If it
> cannot find the matching procedure signature then it says I can't find it,
> it does not exist. For example, you can have procedure or function
> overloading and if you pass in something that isn't one of the choices then
> you will get this error message.
> Jim

I can't reproduce that in 10.2. I either get "PLS-00307: too many declarations of 'x' match this call" if the call is ambiguous, or "PLS-00306: wrong number or types of arguments in call to 'x'" if it doesn't match any. In any case, this is a standalone procedure so it can't be overloaded.

My money's on the missing direct grant theory. Received on Wed Aug 27 2008 - 10:04:07 CDT