Friday, April 8, 2016

How to assign the values to a DB Collection (Array ) When it is defined as Out Parameter in the Procedure/Functions

-- Define the Object Types , Collections as Below 

CREATE OR REPLACE TYPE XX_SERIAL_NUMBER_OBJ AS OBJECT(
   SERIAL_NUMBER                   VARCHAR2(240)
 , STATIC FUNCTION GET_SERIAL_NUMBER
      RETURN XX_SERIAL_NUMBER_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_SERIAL_NUMBER_OBJ
IS
   STATIC FUNCTION GET_SERIAL_NUMBER
      RETURN XX_SERIAL_NUMBER_OBJ
   IS
   BEGIN
      RETURN XX_SERIAL_NUMBER_OBJ(NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_SERIAL_NUMBER_TBL AS TABLE OF XX_SERIAL_NUMBER_OBJ;
/
--+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE XX_ORG_REASSIGN_STATUS_OBJ AS OBJECT(
   ORG_ID                   VARCHAR2(50)
 , SERIAL_NUMBER           VARCHAR2(240)
 , PROCESS_STATUS       VARCHAR2(40)
 , ERROR_ID               VARCHAR2(240)
 , ERROR_MESSAGE           VARCHAR2(2000)
 , STATIC FUNCTION ORG_REASSIGN_STATUS_OBJ
      RETURN XX_ORG_REASSIGN_STATUS_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_ORG_REASSIGN_STATUS_OBJ
IS
   STATIC FUNCTION ORG_REASSIGN_STATUS_OBJ
      RETURN XX_ORG_REASSIGN_STATUS_OBJ
   IS
   BEGIN
      RETURN XX_ORG_REASSIGN_STATUS_OBJ(NULL
                                        , NULL
                                        , NULL
                                        , NULL
                                        , NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_ORG_REASSIGN_STATUS_TBL AS TABLE OF XX_ORG_REASSIGN_STATUS_OBJ;


--Define Procedure to assign the values to the Collection type

PRocedure xx_test_proc (
  p_serial_number_tbl IN xxrb_serial_number_tbl
, x_org_reassignment_status_tbl OUT NOCOPY xxrb_org_reassign_status_tbl)
IS 
--Define Local variable for the Object Type as below
org_reassign_status_obj xx_org_reassign_status_obj;

--Define Local variable for the Collection Type as below
xx_org_reassign_sts_tbl xx_org_reassign_status_tbl;
BEGIN
-- Initialize the local object type and DB table type 
org_reassign_status_obj := 

xx_org_reassign_status_obj.org_reassign_status_obj;

xx_org_reassign_sts_tbl:= xx_org_reassign_status_tbl( org_reassign_status_obj);

For i in p_serial_number_tbl.FIRST..p_serial_number_tbl.LAST 
LOOP

-- Write your core logic which will derive the values for the Columns which needs to assign to the object type as shown below
org_reassign_status_obj.org_id       := 102;
org_reassign_status_obj.serial_number:= 'XYZ';
org_reassign_status_obj.process_status := 'S';
org_reassign_status_obj.error_id       := 'ORA-123';


-- Use the extend Function foe the Array as shown below
 
xx_org_reassign_sts_tbl.EXTEND;

-- Assign the Local record type declared in the declare section as -- below 

xx_org_reassign_sts_tbl( xx_org_reassign_sts_tbl.COUNT) := org_reassign_status_obj;

END LOOP;

x_org_reassignment_status_tbl :=
xx_org_reassign_sts_tbl

EXCEPTION
WHEN OTHERS 
THEN
DBMS_OUTPUT.PUT_LINE('UNEXP_ERROR : '||SUBSTR(SQLERRM, 1, 250));
END;






























No comments:

Post a Comment

Note: Only a member of this blog may post a comment.