Re: Passing Parameters

From: Mtek <mtek_at_mtekusa.com>
Date: Tue, 26 Aug 2008 09:25:18 -0700 (PDT)
Message-ID: <4d6e8daf-ac70-46c8-bca8-96b74711556d@i20g2000prf.googlegroups.com>


On Aug 26, 11:06 am, "fitzjarr..._at_cox.net" <orat..._at_msn.com> wrote:
> On Aug 26, 10:21 am, Mtek <m..._at_mtekusa.com> wrote:
>
>
>
> > Hi,
>
> > I posted earlier about some permission issue. I somehow solved that,
> > but here is an interesting error. I've created a simple test
> > procedure that returns a different error. Please see below. I think
> > this has something to do with passing arrays. Funny thing though, it
> > works fine if both procedures are in the same schema. But if the
> > objects are in different schemas, then we get the error about
> > parameters........
>
> > CUSTOMER SCHEMA
> > ----------------------------------
> > CREATE OR REPLACE TYPE
> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> > /
>
> > CREATE OR REPLACE PROCEDURE test (
> > p_portfolio_id NUMBER DEFAULT NULL,
> > p_tickers IN_STR_ARR) AS
>
> > BEGIN
> > FOR v_rec IN 1..p_tickers.count LOOP
> > DBMS_OUTPUT.PUT_LINE('HERE: ' || p_tickers(v_rec));
> > END LOOP;
> > END;
> > /
>
> > NEW_CUSTOMER SCHEMA
> > --------------------------------------------
> > CREATE OR REPLACE TYPE
> > IN_STR_ARR AS VARRAY (100) OF VARCHAR2(100);
> > /
>
> > CREATE OR REPLACE PROCEDURE X AS
> > arr IN_STR_ARR := IN_STR_ARR(0);
>
> > BEGIN
> > arr.EXTEND;
> > arr(1) := ABC';
> > arr.EXTEND;
> > arr(2) := 'XYZ';
> > test(12345,arr);
> > END;
> > /
>
> > Warning: Procedure created with compilation errors.
>
> > Elapsed: 00:00:00.08
> > SQL>
> > SQL>
> > SQL> show errors
> > Errors for PROCEDURE X:
>
> > LINE/COL ERROR
> > --------
> > -----------------------------------------------------------------
> > 19/3 PL/SQL: Statement ignored
> > 19/3 PLS-00306: wrong number or types of arguments in call to
> > 'TEST'
>
> I would be describing TEST in the NEW_CUSTOMER schema. I see no
> grants to NEW_CUSTOMER for CUSTOMER.TEST nor do I see a synonym
> created to allow access without using the schema owner. So I expect
> you have a TEST procedure under NEW_CUSTOMER which isn't the same as
> the TEST procedure you just created under CUSTOMER.
>
> David Fitzjarrell

Hi David,

I think it has something to do with the fact that I'm trying to pass a VARRAY.......it looks correct, as the IN_STR_ARR type is defined in both schemas. And, if I run both procedures in the same schema, it works fine.

Since it says wrong number of parameters, then it must be finding the other procedure, or it would say it cannot find it. So, I'm thinking it is the array passing. Maybe my format is not correct or something........

Thanks, Received on Tue Aug 26 2008 - 11:25:18 CDT

Original text of this message