Bulk bind a single attribute of a collection [message #658451] |
Wed, 14 December 2016 20:33 |
pointers
Messages: 451 Registered: May 2008
|
Senior Member |
|
|
Hi,
I would like to bulk bind a collection attribute. Is that possible.
I have a collection which takes two attributes let say, empno and empname.
I would like to bulk bind empno using a select bulk collect and at later stage wanted to load empname.
Could you please help me how to achieve this.
declare
type emp_rec is record (emp_no NUMBER, emp_name VARCHAR2(2000));
type emp_aat is table of emp_rec;
l_emp emp_aat;
begin
select empno bulk collect into l_emp.emp_no from emp; -- << I could declare two collections one for empno and other for empname
-- and then bulk bind it.
end;
/
Thank you in advance.
Regards,
Pointers
|
|
|
|
Re: Bulk bind a single attribute of a collection [message #658453 is a reply to message #658451] |
Wed, 14 December 2016 21:11 |
|
Barbara Boehmer
Messages: 9077 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
I don't know what your total process it, so this may not be an efficient method, but it can be done.
SCOTT@orcl_12.1.0.2.0> create or replace type emp_rec as object
2 (emp_no NUMBER, emp_name VARCHAR2(2000));
3 /
Type created.
SCOTT@orcl_12.1.0.2.0> declare
2 type emp_aat is table of emp_rec;
3 l_emp emp_aat;
4 begin
5 -- populate l_emp.emp_no:
6 select emp_rec (empno, null) bulk collect into l_emp from emp;
7 -- do some sort of processing
8 -- populate l_emp.emp_name:
9 for i in 1 .. l_emp.count loop
10 select ename into l_emp(i).emp_name from emp where empno = l_emp(i).emp_no;
11 end loop;
12 -- check results:
13 for i in 1 .. l_emp.count loop
14 dbms_output.put_line (l_emp(i).emp_no || ' ' || l_emp(i).emp_name);
15 end loop;
16 end;
17 /
7369 SMITH
7499 ALLEN
7521 WARD
7566 JONES
7654 MARTIN
7698 BLAKE
7782 CLARK
7788 SCOTT
7839 KING
7844 TURNER
7876 ADAMS
7900 JAMES
7902 FORD
7934 MILLER
PL/SQL procedure successfully completed.
|
|
|