Re: Calling procedure

From: Dan Blum <tool_at_panix.com>
Date: Tue, 26 Aug 2008 15:20:02 +0000 (UTC)
Message-ID: <g916v2$90s$1@reader1.panix.com>


Mtek <mtek_at_mtekusa.com> wrote:
> 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>

> Here is the call:

> CUSTOMER.INSERT_PORTFOLIO_ARR (
> p_user_id => p_customer_id,
> p_portfolio_id => p_portfolio_id,
> p_line => NULL,
> p_ticker => v_ticker_tab,
> p_price => NULL,
> p_shares => NULL,
> p_date => NULL,
> p_comm => NULL,
> p_action => v_action_tab,
> p_portfolio_name => 'Zacks Portfolio Widget',
> p_portfolio_desc => 'Zacks Portfolio Widget',
> product => NULL);

Clearly you have more than one call, since you have three errors.

Exactly how did you grant the right to execute the procedure to the calling schema, and how is the calling procedure defined? If you granted the right to a role, and the calling procedure uses definer's rights, it will not work.

-- 
_______________________________________________________________________
Dan Blum					         tool_at_panix.com	
"I wouldn't have believed it myself if I hadn't just made it up."
Received on Tue Aug 26 2008 - 10:20:02 CDT

Original text of this message