Re: Passing Parameters

From: Mtek <mtek_at_mtekusa.com>
Date: Tue, 26 Aug 2008 10:13:19 -0700 (PDT)
Message-ID: <e9785572-b39c-4873-a687-c54c8fc2c075@z6g2000pre.googlegroups.com>


On Aug 26, 12:00 pm, "Shakespeare" <what..._at_xs4all.nl> wrote:
> "Mtek" <m..._at_mtekusa.com> schreef in berichtnews:4d6e8daf-ac70-46c8-bca8-96b74711556d_at_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,
>
> Defining a type in two schema's does not make them the same type. Define the
> type in a package and refere to this type as packagename.<typename>
>
> Maybe referring to schema.type will work as well, no time to test this.
>
> Shakespeare

WTF?? So, I remove the IN_STR_ARR types in the other schemas and leave it in 1 schema. Then grant execute on that type to public and create a public synonym and everything works?????

How is that different than having the type in each schema and granting access to the user needed access???? Received on Tue Aug 26 2008 - 12:13:19 CDT

Original text of this message