Re: Calling procedure

From: Mtek <mtek_at_mtekusa.com>
Date: Tue, 26 Aug 2008 07:04:18 -0700 (PDT)
Message-ID: <924d1735-2423-466e-811a-09c7f28fa41a@v1g2000pra.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.

I might add, that from the schema that I am trying to compile the procedure in, when I describe the other procedure which it says some component is not found, it describes it perfectly:

SQL> desc INSERT_PORTFOLIO_ARR
PROCEDURE INSERT_PORTFOLIO_ARR

 Argument Name                  Type                    In/Out
Default?
  • ----------------------- ------
    P_USER_ID NUMBER IN P_PORTFOLIO_ID NUMBER IN DEFAULT P_LINE IN_STR_ARR IN P_TICKER IN_STR_ARR IN P_PRICE IN_STR_ARR IN P_SHARES IN_STR_ARR IN P_DATE IN_STR_ARR IN P_COMM IN_STR_ARR IN P_ACTION IN_STR_ARR IN P_PORTFOLIO_NAME VARCHAR2 IN DEFAULT P_PORTFOLIO_DES VARCHAR2 IN DEFAULT PRODUCT VARCHAR2 IN DEFAULT
LINE/COL ERROR


84/7 PL/SQL: Statement ignored
84/16 PLS-00302: component 'INSERT_PORTFOLIO_ARR' must be declared
149/3    PL/SQL: Statement ignored
149/12   PLS-00302: component 'INSERT_PORTFOLIO_ARR' must be declared
211/3    PL/SQL: Statement ignored
211/12   PLS-00302: component 'INSERT_PORTFOLIO_ARR' must be declared
SQL> Received on Tue Aug 26 2008 - 09:04:18 CDT

Original text of this message