pl sql function select into variable in loop on a returning table

Question

I have a type object as below Emptable type is empname,empid,rank

Then I have a Plsql function as below and this errors out. I need to run a sql select statement against the returned list of empids and load it the returned list. and below code keeps erroring..

create or replace function emp_details return emptable
  is
     l_result_col  emptable := emptable();
     n integer := 0;
     rxvalue number;
  begin
     for r in (select empname,empid from table)
     loop
        l_result_col.extend;
        n := n + 1;
        (select sum(xyz) into rxvalue from A inner join B on A.x=B.x and A.id=r.empid);
       l_result_col(n) := t_col(r.empname, r.empid,rxvalue);
     end loop;
     return l_result_col;
  end;
  /

Any Help is appreciated.. Thank you!

Saying what error you get might be helpful, no? And showing your type declarations. Do you get a compilation or runtime error? (It might just not like the brackets around the select, and I think your and should be a where). — Alex Poole
– Alex Poole, Commented Jul 22, 2013 at 20:38

Egor Skriptunoff · Accepted Answer · 2013-07-22 20:45:24Z

1

Why not do it straightforward without involving PL/SQL code?

select t_col(r.empname, r.empid, sum(xyz))
bulk collect into l_result_col
from table r
left join (A join B on A.x = B.x) on A.id = r.empid
group by r.empname, r.empid;

answered Jul 22, 2013 at 20:45

Egor Skriptunoff

23.8k2 gold badges38 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GolezTrol · Accepted Answer · 2013-07-22 20:41:42Z

0

I think your select shouldn't be in parentheses. In this context it is not a subselect but a separate PL/SQL statement.

answered Jul 22, 2013 at 20:41

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Collectives™ on Stack Overflow

pl sql function select into variable in loop on a returning table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related