Re: Passing Parameters

From: gym dot scuba dot kennedy at gmail <kennedyii_at_verizon.net>
Date: Tue, 26 Aug 2008 21:36:42 GMT
Message-ID: <KH_sk.792$5C.208@trnddc02>

"Mtek" <mtek_at_mtekusa.com> wrote in message news:13cfb17c-fc05-49e3-9281-7919f9073f49_at_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.......
>

You didn't listen to Shakespear's explanation. You may wish for something to work a particular way, but it doesn't work that way. Again Oracle is following name scoping conventions that a lot of other languages also follow. Just because you don't like it doesn't mean Oracle is wrong. That is how it works. You can create a type in schema A and grant execute on a to schema b and then create a synonym in b for a. Then yo don't have to specify the schema name.
Jim Received on Tue Aug 26 2008 - 16:36:42 CDT

Original text of this message