Re: Calling procedure

Comments embedded.

On Aug 26, 9:08 am, Mtek <m..._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
>

Most likely this user has access to the 'problem' procedure through a role, and that access doesn't usually extend through to PL/SQL. You can describe this procedure all day, and call it successfully from your SQL> prompt, but you can't find it from any PL/SQL block, function or procedure because normally privileges granted to a role don't extend to the PL/SQL layer. I expect you need a direct grant to this procedure before you can compile any code which uses it through another user account.

> 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);
>
>

David Fitzjarrell Received on Tue Aug 26 2008 - 10:20:10 CDT