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