MQSeries.net :: View topic - How to pass array values to oracle stored procedure in ESQL

sameetbhogi

Hi,

can you please let me know how to pass array values to a Oracle stored procedure in ESQL.

Array Definition in Oracle

create or replace TYPE ID_ARRAY
AS VARRAY(1000) OF VARCHAR2(10);

Oracle Stored Procedure

CREATE OR REPLACE FUNCTION GETEMPINFO
(
pResponseCode OUT NUMBER ,
empList IN ID_ARRAY
, RESULTCURSOR OUT SYS_REFCURSOR
) RETURN NUMBER AS
BEGIN

OPEN RESULTCURSOR FOR
SELECT * FROM EMP, TABLE(empList) LIST WHERE LIST.COLUMN_VALUE = EMP.EMPNO;
pResponseCode := 0;
RETURN pResponseCode;
END GETEMPINFO;