Home » SQL & PL/SQL » SQL & PL/SQL » Bulk bind a single attribute of a collection (Oracle 11.2.0.3)
Bulk bind a single attribute of a collection [message #658451] Wed, 14 December 2016 20:33 Go to next message
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 #658452 is a reply to message #658451] Wed, 14 December 2016 20:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Do NOT do in PL/SQL that which can be done in plain SQL.

>at later stage wanted to load empname
What object_type is empname?
Re: Bulk bind a single attribute of a collection [message #658453 is a reply to message #658451] Wed, 14 December 2016 21:11 Go to previous message
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.
Previous Topic: Comparing CAST (multiset) with Bulk Collect
Next Topic: Update Vs Delete&insertion
Goto Forum:
  


Current Time: Fri Mar 29 09:00:21 CDT 2024