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@
You need to declare a temporary variable of the row type and assign array elements to it in a loop:
However, a more concise way to do that is to use the collection-derived table reference: