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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.