Re: Passing Parameters

From: Mtek <mtek_at_mtekusa.com>
Date: Tue, 26 Aug 2008 13:14:29 -0700 (PDT)
Message-ID: <13cfb17c-fc05-49e3-9281-7919f9073f49@m73g2000hsh.googlegroups.com>


On Aug 26, 2:33 pm, "Shakespeare" <shakespe..._at_mi5.uk.com> wrote:
> "Mtek" <m..._at_mtekusa.com> schreef in berichtnews:e9785572-b39c-4873-a687-c54c8fc2c075_at_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????
>
> Hush hush...
>
> This is not uncommon, there are other programming languages that behave the
> same way (Pascal, Delphi, Fortran, Algol61, -- man I'm getting old...).
> Imagine an other user defining a new type with the same name... what would
> happen then?
> Or two schemas define the same datatype and one changes its definition....
> It is all a matter of scope. You leave the type DEFINITION in one schema
> (e.g. in a package definition, and grant the package to other users), and
> define variables of this type (schema.packagename.typename) in your own
> schema. What is the problem?
>
> Shakespeare
> (What's in a type?)

It just seems totally f**ked up. I mean, I should be able to create a type in every schema and have the database be smart and use the local type when needed, and when arrays are passed, they use the type in the receiving schema.......seems pretty straight forward to me....... Received on Tue Aug 26 2008 - 15:14:29 CDT

Original text of this message