How to retrieve object type data?

Question

My table is:

SQL> select * from fnd_user;

   USER_ID  EMPLOYEE_ID   USER_NAME
----------  -----------  ------------
         1         111    ric
         2         112    tom
         3         113    ravi

After creating the table I created an object type "ret_type" and then created a nested table type of "ret_type1".

CREATE OR REPLACE TYPE ret_type AS OBJECT(val1 NUMBER(15), val2 NUMBER(15));
/
CREATE OR REPLACE TYPE ret_type1 IS TABLE OF ret_type;
/

Then I created a function with the name A as given below:

  CREATE OR REPLACE FUNCTION A(in_login IN fnd_user.user_name%TYPE)
  RETURN ret_type1 AS
  out_var ret_type1;
  CURSOR buffer_cur IS
  SELECT f.user_id, f.employee_id FROM Fnd_User f WHERE f.user_name = in_login;
BEGIN 
    out_var.extend;
    OPEN buffer_cur;
    FETCH buffer_cur INTO out_var(1).val1, out_var(1).val2;
    dbms_output.put_line('hi');
    CLOSE buffer_cur;
    RETURN out_var;
END A;

So while I was trying to retrieve the data by using the select statement I faced a problem. The retrieval code is given below:

select out_2.val1,out_2.val2 from table(A('ric')) out_2;

The error is given below:

SQL> select out_2.val1,out_2.val2 from table(A('ric')) out_2;
select out_2.val1,out_2.val2 from table(A('ric')) out_2
                                        *
ERROR at line 1:
ORA-06531: Reference to uninitialized collection
ORA-06512: at "ANSHUMAN.A", line 7

What does this error mean? How can I fetch the data?

Answer 1

The code needs two changes to work. First, the collection must be initialized before it can be extended. Change the variable declaration from out_var ret_type1 to out_var ret_type1 := ret_type1();. Second, each object inside the collection must also be initialzied. After extending, add this line: out_var(out_var.last) := ret_type(null, null);.

CREATE OR REPLACE FUNCTION A(in_login IN fnd_user.user_name%TYPE)
RETURN ret_type1 AS
  out_var ret_type1 := ret_type1();
  CURSOR buffer_cur IS
  SELECT f.user_id, f.employee_id FROM Fnd_User f WHERE f.user_name = in_login;
BEGIN 
    out_var.extend;
    out_var(out_var.last) := ret_type(null, null);
    OPEN buffer_cur;
    FETCH buffer_cur INTO out_var(1).val1, out_var(1).val2;
    dbms_output.put_line('hi');
    CLOSE buffer_cur;
    RETURN out_var;
END A;
/