Oracle PL/SQL Collections and Objects, cannot initialize.

I am trying to create a collection of objects so I can do sql on it. But I keep getting initialize errors.

Here is the code I am using, notice I have put the collection creation in remarks. At the end is what I get as a result.
create or replace procedure sample as

/*
These statements were executed in SQL
prior to compiling this procecure.

create or replace type r_rec as object
(
    employee_id   number,
    first_name   varchar2(20),
    job_id     varchar2(10)
);

create or replace type t_rec as table of r_rec;

*/

cnt NUMBER;

dat_rec t_rec;

-- Declare a collection (array) of 1 column.
-- Index is the subscript.
-- Binary integer is the numeric data type.
TYPE doc_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_doc_id doc_id;

BEGIN
        
    dat_rec := t_rec();
    dbms_output.PUT_LINE('Here 1 dat_rec : ' || dat_rec.count);
    
    select employee_id BULK COLLECT INTO v_doc_id from hr.employees;
    dbms_output.PUT_LINE('Here 2 v_doc : ' || v_doc_id.count);
    
    FOR i IN v_doc_id.FIRST..v_doc_id.LAST LOOP
        dbms_output.PUT_LINE('Here 3 i = : ' || i);
        dat_rec.extend;
        dbms_output.PUT_LINE('Here 4 dat_rec = : ' || dat_rec.count);
        select 
                employee_id, 
                first_name, 
                job_id 
        into
                dat_rec(i).employee_id,
                dat_rec(i).first_name,
                dat_rec(i).job_id      
        from 
                hr.employees
        where 
                employee_id = v_doc_id(i);
                
        select count(*) into cnt from table(dat_rec);
        dbms_output.PUT_LINE('Here 5 : ' || cnt);
        dbms_output.PUT_LINE('Here 6 : ' || dat_rec.Count);
    END LOOP;
end;

END;

Open in new window


Here are my results as executed in SQL PLUS.

SQL> exec sample();
Here 1 dat_rec : 0
Here 2 v_doc : 107
Here 3 i = : 1
Here 4 dat_rec = : 1
BEGIN sample(); END;

*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at "HR.SAMPLE", line 40
ORA-06512: at line 1
bobcaputoAsked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
Forget the first post.

After you EXTEND, you need to initialize the new record to select into.

I ran the code below using 10.2.0.3
drop type t_rec;
drop type r_rec;

create type r_rec as object
(
    employee_id   number,
    first_name   varchar2(20),
    job_id     varchar2(10)
);
/

show errors

create type t_rec as table of r_rec;
/

show errors

create or replace procedure sample as

cnt NUMBER;

dat_rec t_rec;

-- Declare a collection (array) of 1 column.
-- Index is the subscript.
-- Binary integer is the numeric data type.
TYPE doc_id IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
v_doc_id doc_id;
BEGIN
    dat_rec := t_rec();
    dbms_output.PUT_LINE('Here 1 dat_rec : ' || dat_rec.count);
    
    select employee_id BULK COLLECT INTO v_doc_id from hr.employees;
    dbms_output.PUT_LINE('Here 2 v_doc : ' || v_doc_id.count);
    
    FOR i IN v_doc_id.FIRST..v_doc_id.LAST LOOP
        dbms_output.PUT_LINE('Here 3 i = : ' || i);
        dat_rec.extend;
	dat_rec(i) := r_rec(null,null,null);

        dbms_output.PUT_LINE('Here 4 dat_rec = : ' || dat_rec.count);
        select 
                employee_id, 
                first_name, 
                job_id 
        into
                dat_rec(i).employee_id,
                dat_rec(i).first_name,
                dat_rec(i).job_id      
        from 
                hr.employees
        where 
                employee_id = v_doc_id(i);
                
        select count(*) into cnt from table(dat_rec);
        dbms_output.PUT_LINE('Here 5 : ' || cnt);
        dbms_output.PUT_LINE('Here 6 : ' || dat_rec.Count);
    END LOOP;
END;
/

show errors

exec sample

Open in new window

0
 
slightwv (䄆 Netminder) Commented:
On mobile right now so cannot conform.  I'll try to get to a real computer later but try changing:

    FOR i IN v_doc_id.FIRST..v_doc_id.LAST LOOP

To:
    For i in 1..v_doc_id.count loop

I'm thinking the first..last loop doesn't return a 'number' for use as the index.
0
 
bobcaputoAuthor Commented:
I gave it a try and no go. Actuallyl it does if you look at my output list. I have put some output lines in.

Here is the error msg from before and with your idea.

ORA-06530: Reference to uninitialized composite
0
 
bobcaputoAuthor Commented:
Wow!!!!  I have been dancing around that all day, so close, but I never thought to initialize every loop.

Thanks so much!!!
0
 
slightwv (䄆 Netminder) Commented:
Glad to help.  I hate all the little tricks with pl/sql tables versus varrays.

I honestly had to fight with it a while (at home, no canned scripts).
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.