Passing Parameters
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