PL/SQL: ORA-00947: not enough values

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_last(input in number,salary out vrec_type)
is
begin
select empno,sal BULK COLLECT INTO salary from (
select empno,sal from  
(select empno,sal,rank() over(order by sal asc) min_sal from emp5 where sal is not null) where min_sal <= 5
union all
select empno,sal from 
(select empno,sal,rank() over(order by sal desc) max_sal from emp5 where sal is not null) where max_sal <= 5);
for i in salary.first..salary.last
loop
dbms_output.put_line(salary(i).empno);
end loop;
end;
/

When i compiling the above procedure, im getting not enough values. I have also created the object with two columns and in select statement also im returning only two column. Could someone review and help me on this or provide some alternate solution.

score 1 · Accepted Answer · answered Sep 25 '19 at 05:12

1

You are directly adding empno, sal values into salary (vrec_type object, which can take the values of only object type vrec)

You need to create the object of vrec and then add it into salary as following:

create or replace procedure pro_first_last(input in number,salary out vrec_type)
is
begin
  select vrec(empno,sal) -- change in this line
         BULK COLLECT INTO salary from ( 
    select empno,sal from  
      (select empno,sal,rank() over(order by sal asc) min_sal from emp5 where sal is not null) where min_sal <= 5
    union all
    select empno,sal from 
     (select empno,sal,rank() over(order by sal desc) max_sal from emp5 where sal is not null) where max_sal <= 5);
    for i in salary.first..salary.last
      loop
        dbms_output.put_line(salary(i).empno);
    end loop;
end;

Cheers!!

answered Sep 25 '19 at 05:12

Popeye

35,427
4
10
31

Tejash Awesome!!!! Much thanks for your help. Are we able to create this procedure without using bulk collect. I facing an issues without using bulk collect. Could you able to help me with that too. – Raja sekar Sep 25 '19 at 07:44
Please create a new question to avoid the mess!! :) – Popeye Sep 25 '19 at 11:47
Tejash i have added a new code by creating the procedure and it got compiled successfully. But when i ran the block to see the output i got error like 'Reference to uninitialized collections' – Raja sekar Sep 25 '19 at 17:12
You should initialize salary to empty collection in procedure as the error will be thrown if there is no data found by query. – Popeye Sep 25 '19 at 18:10

PL/SQL: ORA-00947: not enough values

1 Answers1