Calling procedure

On Aug 26, 3:43 am, William Robertson
wrote:
> On Aug 25, 9:36 pm, Mtek wrote:
>
>
>
> > On Aug 25, 3:31 pm, t...@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>

On Aug 26, 3:43 am, William Robertson
wrote:
> On Aug 25, 9:36 pm, Mtek wrote:
>
>
>
> > On Aug 25, 3:31 pm, t...@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);

Mtek wrote:
> On Aug 26, 3:43 am, William Robertson
> wrote:
> > On Aug 25, 9:36 pm, Mtek wrote:
> >
> >
> >
> > > On Aug 25, 3:31 pm, t...@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."

Comments embedded.

On Aug 26, 9:08*am, Mtek wrote:
> On Aug 26, 3:43 am, William Robertson
> wrote:
>
>
>
>
>
> > On Aug 25, 9:36 pm, Mtek wrote:
>
> > > On Aug 25, 3:31 pm, t...@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

"Mtek" wrote in message
news:d16cf8eb-1824-4362-89ee-53179b5d43d5-at-k36g2000pri.googlegroups.com...
> On Aug 26, 3:43 am, William Robertson
> wrote:
>> On Aug 25, 9:36 pm, Mtek wrote:
>>
>>
>>
>> > On Aug 25, 3:31 pm, t...@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

On Aug 25, 4:20*pm, Mtek wrote:
> Hi,
>
> I'm trying to call a procedure like this:
>
> 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);
>
> There are a bunch more parameters, but I only have to pass those. *I
> get this error when trying to compile the code:
>
> 84/7 * * PL/SQL: Statement ignored
> 84/16 * *PLS-00302: component 'INSERT_PORTFOLIO_ARR' must be declared
>
> Any thoughts? *Does this have to be a packaged procedure???

Just a small issue here:

When executing a procedure the proper way to do it is like this:

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

Unless you're inside a PL/SQL block, in which case your way is
correct.

Do you get this when you run it from sqlplus??

Roger Gorden

The Definer = Daffy_Duck schema

A Definer's Rights Subprogram is a definer subprogram created with
AUTHID DEFINER syntax or no "AUTHID" clause at all.

The Invoker = Donald_Duck schema

An Invoker's Rights Subprogram is a definer subprogram created with
AUTHID CURRENT_USER syntax.

Using Roles:

The use of roles in a subprogram depends on whether it executes with
definer's rights (Daffy's rights) or invoker's rights (Donald's
rights). Within a Daffy's (Definer's) rights subprogram, all roles are
disabled. Roles are not used for privilege checking, and you cannot
set roles.

Within a Donald's (Invoker's) rights subprogram, roles are enabled
(unless the subprogram was called directly or indirectly by a Daffy's
(Definer's) rights subprogram). Roles are used for privilege checking,
and you can use native dynamic SQL to set roles for the session.
However, you cannot use roles to grant privileges on template objects
because roles apply at run time, not at compile time.

Not sure if this helps or not. Good luck on you debugging efforts.