CREATE TYPE occurrenceRow AS ROW (
	CLHO_OCC_CODE CHAR(2),
      	CLHO_OCC_FROM_DT DATE,
      	CLHO_OCC_TO_DT DATE)

CREATE TYPE occurrence_obj AS occurrenceRow ARRAY[4]

CREATE PROCEDURE ins_occurrence_code (IN ECTN CHAR(12), IN OCC_CODES occurrence_obj)
BEGIN
	INSERT INTO OCCURENCE_CODE
		SELECT ECTN, 
		T.OCC_CD,T.CLHO_OCC_FROM_DT,T.CLHO_OCC_TO_DT FROM UNNEST(OCC_CODES) 
		AS T(OCC_CD, CLHO_OCC_FROM_DT, CLHO_OCC_TO_DT);
END

begin 
	DECLARE VAR_OCCURENCE_CODES occurrence_obj;
	SET VAR_OCCURENCE_CODES[1] = (select '01','2010-06-01','2010-06-01' FROM SYSIBM.SysDummy1);
	SET VAR_OCCURENCE_CODES[2] = (select '02','2010-06-02','2010-06-02' FROM SYSIBM.SysDummy1);
	SET VAR_OCCURENCE_CODES[3] = (select '03','2010-06-03','2010-06-03' FROM SYSIBM.SysDummy1);
	SET VAR_OCCURENCE_CODES[4] = (select '04','2010-06-04','2010-06-04' FROM SYSIBM.SysDummy1);
	CALL ins_occurrence_code('0123456789AB', VAR_OCCURENCE_CODES);
end