SQL & PL/SQL

Hello All,

I am trying to write a Procedure in whichIl accept as input an user defined type of array object and I want the output to be a user defined type of array object.

I have tried to write the procedure but got stuck while testing to see if works out or not...
Here is the package and the package body containg the Procedure and the UDT defination.

----------------------------------------------------------------------------------------
CREATE OR REPLACE
package RIT_POC_PKG
as
TYPE customer_id_tt IS TABLE OF cdt_all.customer_id%TYPE ;

PROCEDURE p_get_details_REV(pc_customer_id_in IN customer_id_tt,
pc_customer_id_out OUT customer_id_tt) ;

end RIT_POC_PKG;

----------------------------------------------------------------------------------------

CREATE OR REPLACE
PACKAGE BODY RIT_POC_PKG AS

PROCEDURE p_get_details_REV(pc_customer_id_in IN customer_id_tt,
pc_customer_id_out OUT customer_id_tt)
IS
BEGIN

FOR i IN pc_customer_id_in.FIRST .. pc_customer_id_in.LAST
LOOP

pc_customer_id_out(i) := pc_customer_id_in(i) + 600;

END LOOP;

END p_get_details_REV;

END RIT_POC_PKG;

-----------------------------------------------------------------------------------------------------------

Below is the Anonymous block that I have written to find if the above package works or not ...


DECLARE

customer_id_in_list RIT_POC_PKG.customer_id_tt := RIT_POC_PKG.customer_id_tt(2,4,6);

customer_id_out_list RIT_POC_PKG.customer_id_tt := RIT_POC_PKG.customer_id_tt();

BEGIN

RIT_POC_PKG.p_get_last_payment_details_REV(customer_id_in_list,customer_id_out_list);

FOR i IN customer_id_out_list.FIRST .. customer_id_out_list.LAST
LOOP

DBMS_OUTPUT.PUT_LINE(' '|| customer_id_out_list(i) || ' ');

END LOOP;

END;

------------------------------------------------------------------------------------------------------------------

I would appreciate if anybody helps me solve this... Also anyone who wants to suggest a change in the Procedure is also welcome to do so...

Thanks.....