Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Collect (oracle 10g)
Bulk Collect [message #580041] Tue, 19 March 2013 12:43 Go to next message
jay_cooldude
Messages: 50
Registered: March 2013
Location: Hyd
Member
declare 
type t1 is table of emp1.ename%type;
v t1;
begin
update emp1 set sal=sal+1000 where deptno=20 returning ename bulk collect into v;
for i in v.first.. v.last loop
dbms_output.put_line('The sal is updated for'||v(i));
end loop;
end;   





20
20
1
20
20
1
20
20
1
20
20
1
20
20
1
The sal is updated forSMITH
The sal is updated forSCOTT
The sal is updated forADAMS
The sal is updated forFORD
The sal is updated forJONES

[/output]

Using this code i want know the employee names whoose sal is updated .
but i am getting some numbers with my ouput.
how to eliminate such data?

Re: Bulk Collect [message #580046 is a reply to message #580041] Tue, 19 March 2013 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Either you had these lines in dbms_output buffer either you have these rows in your table.
With a strict copy of scott's emp table you get:
SQL> declare 
  2  type t1 is table of emp1.ename%type;
  3  v t1;
  4  begin
  5  update emp1 set sal=sal+1000 where deptno=20 returning ename bulk collect into v;
  6  for i in v.first.. v.last loop
  7  dbms_output.put_line('The sal is updated for'||v(i));
  8  end loop;
  9  end;   
 10  /
The sal is updated forSMITH
The sal is updated forJONES
The sal is updated forSCOTT
The sal is updated forADAMS
The sal is updated forFORD

PL/SQL procedure successfully completed.


Regards
Michel
Re: Bulk Collect [message #580068 is a reply to message #580046] Tue, 19 March 2013 18:11 Go to previous messageGo to next message
cookiemonster
Messages: 10590
Registered: September 2008
Location: Rainy Manchester
Senior Member
There could be a trigger on the table with a dbms_output call in it.
Re: Bulk Collect [message #580071 is a reply to message #580068] Tue, 19 March 2013 18:35 Go to previous messageGo to next message
jay_cooldude
Messages: 50
Registered: March 2013
Location: Hyd
Member
Thank You.
I really appreciate your work.
Re: Bulk Collect [message #580078 is a reply to message #580071] Wed, 20 March 2013 01:16 Go to previous message
Michel Cadot
Messages: 57644
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So what was the problem?

Regards
Michel
Previous Topic: Previous day
Next Topic: Ref Cursors
Goto Forum:
  


Current Time: Thu Apr 24 02:22:23 CDT 2014

Total time taken to generate the page: 0.14737 seconds