Re: Passing Parameters

Mtek <mtek_at_mtekusa.com> writes:

> 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'
Check the Oracle documentation. You will find there is a warning in the pl/sql reference manual dealing with parameter passing and collection types.

Essentially, from PL/SQLs view, your two varray types are _different_ types despite having the same name and being made up of the same data types.

Tim

-- 
tcross (at) rapttech dot com dot au