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;






























How to Invoke the Database Object Types in Anonymous Blocks

 -- Create DB Object Types and Collections

CREATE OR REPLACE TYPE XX_SYNC_ORG_OBJ AS OBJECT(
   ORG_ID                   VARCHAR2(50)
 , STATIC FUNCTION GET_OBJECT
      RETURN XX_CC_SYNC_ORG_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_SYNC_ORG_OBJ
IS
   STATIC FUNCTION GET_OBJECT
      RETURN XX_SYNC_ORG_OBJ
   IS
   BEGIN
      RETURN XX_SYNC_ORG_OBJ(NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_SYNC_ORG_TBL AS TABLE OF XX_SYNC_ORG_OBJ;
/
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE TYPE XX_EBS_SYNC_OBJ AS OBJECT(
   INSTANCE_REFERENCE       VARCHAR2(50)
 , SERIAL_NUMBER            VARCHAR2(240)
 , SERVICE_START_DATE       DATE
 , SERVICE_END_DATE         DATE
 , SUPPORT_LEVEL            VARCHAR2(50)
 , PARTNER                  VARCHAR2(1)
 , NODE_CATEGORY            VARCHAR2(50)
 , ORG_ID                   VARCHAR2(50)
 , STATIC FUNCTION GET_OBJ
      RETURN XX_EBS_SYNC_OBJ);
/
CREATE OR REPLACE TYPE BODY XX_EBS_SYNC_OBJ
IS
   STATIC FUNCTION GET_OBJ
      RETURN XX_EBS_SYNC_OBJ
   IS
   BEGIN
      RETURN XX_EBS_SYNC_OBJ(NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL
                                , NULL);
   END;
END;
/
CREATE OR REPLACE TYPE XX_EBS_SYNC_TBL AS TABLE OF XX_EBS_SYNC_OBJ;
/

--  Invoke it in anonymous Block...

DECLARE
   p_cc_org_tbl   xx_cc_sync_org_tbl := xx_cc_sync_org_tbl();
   x_ebs_sync_tbl xx_ebs_cc_sync_tbl;
   l_index           NUMBER := 1;
BEGIN

   -- Use Below Method When Using Hard coding
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 1):= xx_cc_sync_org_obj( '1212121');
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 2):= xx_cc_sync_org_obj( '123123213213');
   -- p_cc_org_tbl.EXTEND;
   -- p_cc_org_tbl( 3):= xx_cc_sync_org_obj( '1231232132131');


   -- Use Below Method When Using Dynamic fetch
    FOR i IN (SELECT DISTINCT attribute21
               FROM csi_item_instances
              WHERE NVL(active_end_date, SYSDATE) >= SYSDATE
                AND attribute21 IS NOT NULL)
   LOOP
     p_cc_org_tbl.EXTEND;
     p_cc_org_tbl( l_index) := xx_cc_sync_org_obj( i.attribute21);
     l_index                                   := l_index + 1;
   END LOOP;

   DBMS_OUTPUT.put_line(   'Table Count : '
                        || p_cc_org_tbl.COUNT);
 

   ebs_ks_cc_sync(p_cc_org_tbl, x_ebs_cc_sync_tbl);
END;