How to access a Row Type within an Array Type in DB2 SQL PL

Question

I have a java front end that has a table of data. We need to save this data to the Database via stored procedure. If the passed parameter is a just an array, I am able to access the contents easily. But if the contents of the array is a also a row type, thats the part im having trouble with.

I dont know how to access the contents of the array.

Im using DB2 10.1

CREATE TABLE "TEST"."CHART_ACCT" (
    "ACCT_NO" VARCHAR(10) NOT NULL, 
    "ACCT_DESC" VARCHAR(40) NOT NULL
)
ORGANIZE BY ROW
DATA CAPTURE NONE 
IN "USERSPACE1"
COMPRESS YES ADAPTIVE
VALUE COMPRESSION@

CREATE OR REPLACE TYPE TEST.ACCT AS ROW ANCHOR ROW OF TEST.CHART_ACCT@

CREATE OR REPLACE TYPE TEST.ACCT_ARR AS TEST.ACCT ARRAY[]@

CREATE OR REPLACE PROCEDURE TEST.TEST_ARRAY (IN P_ACCT_ARR TEST.ACCT_ARR)
P1: BEGIN
-- #######################################################################
-- #
-- #######################################################################
DECLARE i INTEGER;
SET i = 1;
WHILE i < 10 DO
    CALL DBMS_OUTPUT.PUT_LINE(P_GLACCT_ARR[i]);
set i = i + 1;
END WHILE;
END P1@

Answer 1

You need to declare a temporary variable of the row type and assign array elements to it in a loop:

CREATE OR REPLACE PROCEDURE TEST_ARRAY (IN P_ACCT_ARR ACCT_ARR)
P1: BEGIN
DECLARE i INTEGER;
DECLARE v_acct acct;
SET i = 1;
WHILE i < CARDINALITY(p_acct_arr) DO
    SET v_acct = p_acct_arr[i];
    CALL DBMS_OUTPUT.PUT_LINE('Account number = ' || v_acct.acct_no);
    set i = i + 1;
END WHILE;
END P1@

However, a more concise way to do that is to use the collection-derived table reference:

CREATE OR REPLACE PROCEDURE TEST_ARRAY (IN P_ACCT_ARR ACCT_ARR)
P1: BEGIN
FOR r AS (SELECT * FROM UNNEST(p_acct_arr) t ) DO
    CALL DBMS_OUTPUT.PUT_LINE('Account number = ' || r.acct_no);
END FOR;
END P1@