Calling procedure

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???

Mtek wa:
>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???

If the procedure is in a different schema than the one you're trying to
execute it from, you will have to 1) grant execute permissions for the
procedure to the schema that's calling it and 2) preface the procedure
name with the schema name (e.g., SCHEMA.INSERT_PORTFOLIO_ARR [...]).


--
"Care must be exorcised when handring Opiticar System as it is apts to
be sticked by dusts and hand-fat." --Japanese Translators

"Keep your fingers off the lens." --Elton Byington, English Translator

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???

If we could see the CREATE PROCEDURE statement (just down to the IS) that
would help. Also, is the procedure in the same schema the calling code is in?

--
__________________________________________________ _____________________
Dan Blum tool-at-panix.com
"I wouldn't have believed it myself if I hadn't just made it up."

On Aug 25, 3:31 pm, t...@panix.com (Dan Blum) wrote:
> 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???
>
> If we could see the CREATE PROCEDURE statement (just down to the IS) that
> would help. Also, is the procedure in the same schema the calling code is in?
>
> --
> __________________________________________________ _____________________
> Dan Blum t...@panix.com
> "I wouldn't have believed it myself if I hadn't just made it up."


Sorry,

Actual procedure statement is below. and, it is not in the same
schema, but I grated execute privileges as well as created a public
synonym. 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

Mtek wrote:
> On Aug 25, 3:31 pm, t...@panix.com (Dan Blum) wrote:
> > 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???
> >
> > If we could see the CREATE PROCEDURE statement (just down to the IS) that
> > would help. Also, is the procedure in the same schema the calling code is in?
> >
> > --
> > __________________________________________________ _____________________
> > Dan Blum t...@panix.com
> > "I wouldn't have believed it myself if I hadn't just made it up."


> Sorry,

> Actual procedure statement is below. and, it is not in the same
> schema, but I grated execute privileges as well as created a public
> synonym. 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

Are you sure that INSERT_PORTFOLIO_ARR compiles? If it has an error,
that will result in the error you are seeing.

--
__________________________________________________ _____________________
Dan Blum tool-at-panix.com
"I wouldn't have believed it myself if I hadn't just made it up."

On Aug 25, 4:03 pm, t...@panix.com (Dan Blum) wrote:
> Mtek wrote:
> > On Aug 25, 3:31 pm, t...@panix.com (Dan Blum) wrote:
> > > 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???
>
> > > If we could see the CREATE PROCEDURE statement (just down to the IS) that
> > > would help. Also, is the procedure in the same schema the calling code is in?
>
> > > --
> > > __________________________________________________ _____________________
> > > Dan Blum t...@panix.com
> > > "I wouldn't have believed it myself if I hadn't just made it up."
> > Sorry,
> > Actual procedure statement is below. and, it is not in the same
> > schema, but I grated execute privileges as well as created a public
> > synonym. 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
>
> Are you sure that INSERT_PORTFOLIO_ARR compiles? If it has an error,
> that will result in the error you are seeing.
>
> --
> __________________________________________________ _____________________
> Dan Blum t...@panix.com
> "I wouldn't have believed it myself if I hadn't just made it up."


Yes, everything is compiled. It is something very funky I'm missing.
Because I coded some calls to other procedures in the same fashion and
such and no issues at all.....

Mtek schrieb:
> On Aug 25, 3:31 pm, t...@panix.com (Dan Blum) wrote:
>> 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???
>> If we could see the CREATE PROCEDURE statement (just down to the IS) that
>> would help. Also, is the procedure in the same schema the calling code is in?
>>
>> --
>> __________________________________________________ _____________________
>> Dan Blum t...@panix.com
>> "I wouldn't have believed it myself if I hadn't just made it up."
>
>
> Sorry,
>
> Actual procedure statement is below. and, it is not in the same
> schema, but I grated execute privileges as well as created a public
> synonym. 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

You have to pass *all* the parameters by calling the procedure which
don't have defaults, haven't you?

Best regards

Maxim

On Mon, 25 Aug 2008 14:07:34 -0700 (PDT), Mtek
wrote:

>Yes, everything is compiled. It is something very funky I'm missing.
>Because I coded some calls to other procedures in the same fashion and
>such and no issues at all.....

Only the formal parameters with a DEFAULT clause can be omitted from
an actual parameter list.
Meaning: you must be calling the procedure with too few parameters,
and actually there are parameters missing.

--
Sybrand Bakker
Senior Oracle DBA

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.

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...........