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.

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