Re: Passing Parameters

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 26 Aug 2008 19:00:09 +0200
Message-ID: <48b4369f$0$201$e4fe514c@news.xs4all.nl>

"Mtek" <mtek_at_mtekusa.com> schreef in bericht news: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 Received on Tue Aug 26 2008 - 12:00:09 CDT