Home » SQL & PL/SQL » SQL & PL/SQL » Display Collection
Display Collection [message #352138] Mon, 06 October 2008 05:23 Go to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

Hi,
I have one collection dept_rec of deptnos.Using this collection I need to populate the second collection emp_rec of enames.
I dont understand that how can I populate emp_rec using dept_rec.

My code is as follows:-

SQL> declare
  2     type dept_tab is table of dept.deptno%type index by binary_integer;
  3     dept_rec dept_tab;
  4     cnt integer := 0;
  5     type emp_tab is table of emp.ename%type index by binary_integer;
  6     emp_rec emp_tab;
  7  begin
  8    for i in (select deptno from dept)
  9    loop
 10       cnt:=cnt+1;
 11       dept_rec(cnt):= i.deptno;
 12    end loop;
 13    for i in dept_rec.first..dept_rec.last
 14    loop
 15       select ename into emp_rec(i) from emp where deptno = dept_rec(i);
 16    end loop;
 17    for i in 1..emp_rec.count
 18    loop
 19        dbms_output.put_line(emp_rec(i));
 20    end loop;
 21  end;
 22  /
declare
*
ERROR at line 1:
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 15


when I use bulk collect in SELECT statement. There is no display.

SQL> declare
  2     type dept_tab is table of dept.deptno%type index by binary_integer;
  3     dept_rec dept_tab;
  4     cnt integer := 0;
  5     type emp_tab is table of emp.ename%type index by binary_integer;
  6     emp_rec emp_tab;
  7  begin
  8    for i in (select deptno from dept)
  9    loop
 10       cnt:=cnt+1;
 11       dept_rec(cnt):= i.deptno;
 12    end loop;
 13    for i in dept_rec.first..dept_rec.last
 14    loop
 15       select ename bulk collect into emp_rec from emp where deptno = dept_rec(i);
 16    end loop;
 17    for i in 1..emp_rec.count
 18    loop
 19        dbms_output.put_line(emp_rec(i));
 20    end loop;
 21  end;
 22  /

PL/SQL procedure successfully completed.


Re: Display Collection [message #352140 is a reply to message #352138] Mon, 06 October 2008 05:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First error, a department can contain more than one employee.
Second error, the last department can contain no employee (each time you bulk collect, you reset the collection).

Regards
Michel
Re: Display Collection [message #352145 is a reply to message #352140] Mon, 06 October 2008 05:33 Go to previous messageGo to next message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

Thanks for the replay Michel.
How can I approach for the solution then?
Can You give me a Hint please.

Regards
HArshad
Re: Display Collection [message #352146 is a reply to message #352145] Mon, 06 October 2008 05:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use a second table emp_rec2, you will bulk collect into it and add its content to emp_rec for each department.

Regards
Michel
Re: Display Collection [message #352157 is a reply to message #352146] Mon, 06 October 2008 06:18 Go to previous message
harshadsp
Messages: 100
Registered: August 2008
Senior Member

Hi,

SQL> declare
  2  type dept_tab is table of dept.deptno%type index by binary_integer;
  3  dept_rec dept_tab;
  4  cnt integer := 0;
  5  type emp_tab is table of emp.ename%type index by binary_integer;
  6  emp_rec emp_tab;
  7  c1 sys_refcursor;
  8  begin
  9  for i in (select deptno from dept)
 10  loop
 11  cnt:=cnt+1;
 12  dept_rec(cnt):= i.deptno;
 13  end loop;
 14  
 15  dbms_output.put_line(dept_rec.last);
 16  for i in dept_rec.first..dept_rec.last
 17  loop
 18  select ename bulk collect into emp_rec from emp where deptno = dept_rec(i);
 19  dbms_output.put_line(i);
 20  
 21  for i in 1..emp_rec.count
 22  loop
 23  dbms_output.put_line(emp_rec(i));
 24  end loop;
 25  end loop;
 26  end;
 27  /
4
1
CLARK
KING
MILLER
2
SMITH
JONES
SCOTT
ADAMS
FORD
3
ALLEN
WARD
MARTIN
BLAKE
TURNER
JAMES
4

PL/SQL procedure successfully completed.


Regards
Harshad
Previous Topic: Need to select data from two partitions
Next Topic: Drop overflow table of IOT
Goto Forum:
  


Current Time: Wed Dec 07 08:55:06 CST 2016

Total time taken to generate the page: 0.09215 seconds