CREATE OR REPLACE TYPE TY_1 AS OBJECT
(
FN VARCHAR2(100),
SL NUMBER,
HD DATE
);
-- Example 1
CREATE OR REPLACE FUNCTION FN_RET_COL
RETURN TY_1
AS
OBJ_TY_1 TY_1 := TY_1(NULL,NULL,NULL);
BEGIN
SELECT FIRST_NAME,SALARY,HIRE_DATE
INTO OBJ_TY_1.FN, OBJ_TY_1.SL, OBJ_TY_1.HD
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 120;
RETURN OBJ_TY_1;
END;
-- Example 2
CREATE OR REPLACE FUNCTION FN_RET_COL
RETURN TY_1
AS
OBJ_TY_1 TY_1;
BEGIN
SELECT TY_1(FIRST_NAME,SALARY,HIRE_DATE)
INTO OBJ_TY_1
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;
RETURN OBJ_TY_1;
END;
-- Query
SELECT FN_RET_COL() FROM DUAL;
-- Modified Example 1(Throws Reference to uninitialized composite)
CREATE OR REPLACE FUNCTION FN_RET_COL
RETURN TY_1
AS
OBJ_TY_1 TY_1;
BEGIN
SELECT FIRST_NAME,SALARY,HIRE_DATE
INTO OBJ_TY_1.FN, OBJ_TY_1.SL, OBJ_TY_1.HD
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 120;
RETURN OBJ_TY_1;
END;
Both Example 1 and Example 2 works fine..Both does the same work almost. But My doubt is when the collection in Example 1 is not Initialized as like in Example 2.. it is throwing error. But in Example 2 it is not Initialized but works fine. So when to initialize a collection variable and when not needed?
You cannot reference fields of an uninitialized collection. In modified example 1 you are referring to OBJ_TY_1.FN, i.e. specific field, hence the error.
In original example 1 the collection is initialized, so you are allowed to reference the fields.
In example 2 the collection is initialized in the SELECT clause.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With