-- 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;
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.