Home » SQL & PL/SQL » SQL & PL/SQL » bulk update is not working.. (oracle10g)
bulk update is not working.. [message #420623] Tue, 01 September 2009 00:53 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
bulk update is not working..

declare 
type emplist is table of emp%rowtype;
allemp emplist;
emprec emp%rowtype;
cnt number:=1;
begin
select * bulk collect into allemp    from emp;
dbms_output.put_line(allemp.count);

while cnt < allemp.count loop
dbms_output.put_line(allemp(cnt).EMPNO);
emprec := allemp(cnt)  ;

--insert statment is working below
/*
emprec.EMPNO := emprec.EMPNO * 2 ;
insert into emp values  emprec;
*/

--each salary multiplying with 2.
emprec.SAL := emprec.SAL * 2 ;
--below update statment is not working.cant we update with single update statmenet.  
update  emp set (emp.* ) =   emprec where emp.empno = emprec.empno ; 
--update emp set emp = allemp(cnt) where emp.empno = allemp(cnt).EMPNO  ; 
 
cnt := cnt + 1;
end loop;


end;
Re: bulk update is not working.. [message #420625 is a reply to message #420623] Tue, 01 September 2009 00:57 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
my car is not working.

Tell me how to make my car go.
Re: bulk update is not working.. [message #420626 is a reply to message #420623] Tue, 01 September 2009 00:59 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
i explained to you with the code.
Re: bulk update is not working.. [message #420630 is a reply to message #420626] Tue, 01 September 2009 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As the code is wrong it does not explain anything.
Explain what you want to do with the code.
Use SQL*Plus and copy and paste your session.
And remove useless commented code.

Regards
Michel

[Updated on: Tue, 01 September 2009 01:25]

Report message to a moderator

Re: bulk update is not working.. [message #420636 is a reply to message #420626] Tue, 01 September 2009 02:49 Go to previous messageGo to next message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Bulk DMLs are performed through FORALL statement if you don't use it there's no bulk update.

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#sthref2181

Bye Alessandro
Re: bulk update is not working.. [message #420754 is a reply to message #420623] Wed, 02 September 2009 01:15 Go to previous messageGo to next message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
Where is bulk update man..
you need forall to do bulk update.
Your intention is also not clear..you make a select * from emp and then try to so update on same emp table..
Might get into mutating table error..
Re: bulk update is not working.. [message #420756 is a reply to message #420754] Wed, 02 September 2009 01:30 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
bishtoo wrote on Wed, 02 September 2009 08:15
you make a select * from emp and then try to so update on same emp table..
Might get into mutating table error..


A mutating table error can only occur in triggers.
Re: bulk update is not working.. [message #420763 is a reply to message #420623] Wed, 02 September 2009 01:47 Go to previous message
bishtoo
Messages: 20
Registered: August 2009
Junior Member
Sorry, I had a trigger on emp table in my local environment..
I missed it..
Previous Topic: Do we need to close Ref Cursors
Next Topic: condition
Goto Forum:
  


Current Time: Sun Dec 04 04:23:20 CST 2016

Total time taken to generate the page: 0.04097 seconds