Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Initializing a collection in oracle

 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?

like image 515
ramesh538 Avatar asked Dec 04 '25 08:12

ramesh538


1 Answers

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.

like image 135
Kombajn zbożowy Avatar answered Dec 06 '25 00:12

Kombajn zbożowy