CODEWITHSUNDEEP
sqloracle-databaseplsql
oradbanj
oradbanj

Reputation: 563

Error in Initializing oracle collection

I have following self contained code which gives following error. I don't seem to find where do I have to initialize the collection.

create or replace type address_type is object 
( 
  address_line varchar2(100),
  city    varchar2(100), 
  state   varchar2(100)
)
/
create or replace type emp_rec_type is object
        (
                emp_name        varchar2(100),
                addr_rec        address_type
        )
 /
 create or replace type emp_arr is table of emp_rec_type
 /
 create table employees
 (
        emp_name varchar2(100),
        addr_rec address_type
 )
 /

insert into employees values ( 'dave', address_type ( '30 br','br','nj'));
commit;
-- Create a function to return an array
create or replace function get_emp_rec ( p_name in varchar2 )
return
        emp_arr
is
    i       integer;
    l_arr   emp_arr := emp_arr();
begin
    i := 1;
    l_arr.extend;

    l_arr(l_arr.last).emp_name := 'a';
    l_arr(l_arr.last).addr_rec := address_type ( 'a','b','c');
    return l_arr;
end;
/

select emp_name from table ( get_emp_rec( 'dave' )) * ERROR at line 1: ORA-06530: Reference to uninitialized composite ORA-06512: at "DBADMIN.GET_EMP_REC", line 22

Upvotes: 1

Views: 141

Answers (1)

Arkadiusz Łukasiewicz
Arkadiusz Łukasiewicz

Reputation: 6366

You have to initialize emp_rec_type in the collection.

These two line

 l_arr(l_arr.last).emp_name := 'a';
 l_arr(l_arr.last).addr_rec := address_type ( 'a','b','c');

replace with this linie

l_arr(l_arr.last) := emp_rec_type('a', address_type ( 'a','b','c'));

Upvotes: 4

Related Questions