Re: Passing Parameters

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 Received on Tue Aug 26 2008 - 11:06:09 CDT