Passing Parameters

From: Mtek <mtek_at_mtekusa.com>
Date: Tue, 26 Aug 2008 08:21:41 -0700 (PDT)
Message-ID: <d45c51d0-8b7a-410e-8735-7e171b4baf6a@w39g2000prb.googlegroups.com>

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' Received on Tue Aug 26 2008 - 10:21:41 CDT

Original text of this message