Re: Passing Parameters

From: Shakespeare <shakespeare_at_mi5.uk.com>
Date: Tue, 26 Aug 2008 21:33:14 +0200
Message-ID: <48b45a72$0$188$e4fe514c@news.xs4all.nl>

"Mtek" <mtek_at_mtekusa.com> schreef in bericht news: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?) Received on Tue Aug 26 2008 - 14:33:14 CDT

Original text of this message