SQL & PL/SQL

Hi All,

I have procedure with input and output values which will return Table Type Array as a output.
In that i am looping through the data in cursor and store that into array. In my below code i am looping through all the data and pass those data into one more procedure. Then from that procedure i am getting array output. I have to show all the array output one bye one in my output parameters. See my sample
code below.


CREATE OR REPLACE PROCEDURE SAMPLE_PKG
AS
PROCEDURE get_array_value
(
i_input_1 in number,
o_output_1 OUT NOCOPY FND_TABLE_OF_VARCHAR2_4000,
o_output_2 OUT NOCOPY FND_TABLE_OF_VARCHAR2_4000,
o_output_3 OUT NOCOPY FND_TABLE_OF_VARCHAR2_4000
)
IS
MOP FND_TABLE_OF_VARCHAR2_4000 := FND_TABLE_OF_VARCHAR2_4000 ();
MAWP_MSP FND_TABLE_OF_VARCHAR2_4000 := FND_TABLE_OF_VARCHAR2_4000 ();
ACT_IDENTIFIER FND_TABLE_OF_VARCHAR2_4000 := FND_TABLE_OF_VARCHAR2_4000 ();

TYPE sizing_line_type IS TABLE OF SAMPLE_LINE_INFO%ROWTYPE;
v_sizing_line_tbl_type sizing_line_type;

BEGIN
SELECT xxvsli.*
BULK COLLECT INTO v_sizing_line_tbl_type
FROM SAMPLE_LINE_INFO xxvsli,
SAMPLE_MODEL_INFO xxvmsi
WHERE xxvsli.QUOTE_HEADER_ID = xxvmsi.QUOTE_HEADER_ID
AND xxvmsi.QUOTE_HEADER_ID = i_input_1

FOR i IN v_sizing_line_tbl_type.first..v_sizing_line_tbl_type.last
LOOP
<< here i am calling one more proceudre. That procedure also will return the array output>>
<<output array name is MOP, MAWP_MSP, ACT_IDENTIFIER>>

FORALL j IN actuator_identifier.first..actuator_identifier.last
INSERT INTO xxcz_va_sizing_result (
ITEM_NUMBER,
MOP,
MAWP,
)
VALUES (
ACT_IDENTIFIER(j),
MOP(j),
MAWP_MSP(j),
);
END LOOP;

o_output_1 := ACT_IDENTIFIER;
o_output_2 := MOP;
o_output_3 := MAWP;

END get_array_value

END SAMPLE_PKG;

From the above procedure, i am looping through some five records. In that five, four i got four as the output one is no output. In that case my output parameter is giving no values but in my table it is inserted four records.

Can anyone share How to populate those array values in the output parameter?

Thanks

Edited by: orasuriya on Jul 8, 2009 10:19 AM

Edited by: orasuriya on Jul 8, 2009 10:34 AM