Re: Calling procedure
Date: Wed, 27 Aug 2008 08:04:07 -0700 (PDT)
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
> > 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.
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