I got the error ORA-06531: Reference to uninitialized collection

Question

Im creating a procedure to display the n number of maximum and minimum salary for an employee. If i ll give 5 as input, the query will get me 5 maximum and minimum salary for an employee.

For the above scenario, I have created an object with two columns like below

create type vrec as object(
empno number,
sal number
);
/

Then i created nested table with the help of object type, so that i can use the nested table as out parameter to return all the rows at one short.

create type vrec_type is table of vrec;
/

After the data type creation, im creating a procedure like below

create or replace procedure pro_first_last1(input in number,salary out vrec_type)
as
n number:=0;
begin
salary.extend;
select vrec(empno,sal) into salary(n) from (
select * from (select empno,sal,rank() over(order by sal asc) min_sal from emp5) where min_sal <= input
union all
select * from (select empno,sal,rank() over(order by sal asc) max_sal from emp5) where max_sal <= input);
n:=n+1;
for i in 1..salary.count
loop
dbms_output.put_line(salary(i).empno||'    '||salary(i).sal);
end loop;
end;
/

Also the procedure compiled successfully.

But when i ran the below block to get the output, i got the error "Reference to uninitialized collection"

declare
input number:=5;
salary vrec_type;
begin
pro_first_last1(input,salary);
end;
/

score 3 · Answer 1 · answered Sep 25 '19 at 17:49

3

In your procedure, the salary variable is a nested table which needs to be initialized before you can call salary.extend;

You can initialize it with an empty constructor like this:

salary := vrec_type();
salary.extend;

answered Sep 25 '19 at 17:49

kfinity

8,581
1
13
20

If i made the changes you have specified and try to run the below block, i am getting exact fetch returns more than requested number of rows error. declare input number:=5; salary vrec_type; begin pro_first_last1(input,salary); end; / – Raja sekar Sep 25 '19 at 19:06

score 0 · Answer 2 · answered Sep 25 '19 at 18:33

You don't have to run a loop. A quicker and shorter version would be this:

select vrec(empno,sal) 
BULK COLLECT into salary 
from 
   (select * from (select empno,sal,rank() over(order by sal asc) min_sal from emp5) 
    where min_sal <= input
    union all
    select * from (select empno,sal,rank() over(order by sal asc) max_sal from emp5)
    where max_sal <= input);

btw, your query does not make much sense. The expression rank() over(order by sal asc) is used twice. Once you use it as min_sal, once as max_sal - for the same expression?

In case you run Oracle 12.2 or newer you can also use the Row Limiting Clause

with minSal as 
   (select empno,sal
    from emp5
    order by sal ASC
    FETCH FIRST input ROWS WITH TIES),
maxSal as
    (select empno,sal
    from emp5
    order by sal DESC
    FETCH FIRST input ROWS WITH TIES),
allSal as
    (select * from minSal 
    UNION ALL
    select * from minSal) 
select vrec(empno,sal) 
BULK COLLECT into salary 
from allSal;

I can do it with bulk collect but i cannot able to do without bulk collect. Without using bulk collect i cannot able to successfully get the desired results — Raja sekar, Sep 25 '19 at 19:05

I got the error ORA-06531: Reference to uninitialized collection

2 Answers2