Home » SQL & PL/SQL » SQL & PL/SQL » Bulk Update (Oracle 9i)
Bulk Update [message #348711] Wed, 17 September 2008 14:48 Go to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
Hello Folks,
Need your help in Writing Bulk Update for my following procedure.

Declare

Bulk_Err Exception ;
Pragma Exception_Init (Bulk_Err,-24381);

Cursor C1 Is
	Select Col1,col2,col3,col4....col100
	  From TableA,TblB,TblC ;

TYPE ti_values_tab IS TABLE OF c1%ROWTYPE;
values_tab      TI_values_tab    := ti_values_tab ();   

Begin
    Open C1;
    Loop
      Exit When C1% Notfound;
      Fetch C1 
         Bulk Collect Into values_tab Limit 1000;
        Begin
           Forall i IN Values_tab.First..values_tab.Last Save Exceptions
		
		--How to Write Update statement
		--I have 100 colums to be Updated  

	  EXCEPTION
               WHEN bulk_errors
               THEN
                  FOR indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
                  LOOP
                     DBMS_OUTPUT.put_line
                                     ('While Inserting:'||SQL%BULK_EXCEPTIONS (indx).ERROR_CODE);
                  END LOOP;
          END;
     END LOOP;

    CLOSE c1;

 COMMIT;
END; 


Can anyone help me to write Update statement?
Re: Bulk Update [message #348712 is a reply to message #348711] Wed, 17 September 2008 14:56 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Nowhere in your post is any qualification about how to know which rows get which values.

What is wrong with
UPDATE DIFF_TABLE SET (col1, col2, ..., col100) SELECT .....

[Updated on: Wed, 17 September 2008 14:56] by Moderator

Report message to a moderator

Re: Bulk Update [message #348715 is a reply to message #348711] Wed, 17 September 2008 15:14 Go to previous messageGo to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
Can we write the update statement of yours in PL/Sql Block without Exec Immediate?
Re: Bulk Update [message #348717 is a reply to message #348711] Wed, 17 September 2008 15:18 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96624/11_dynam.htm#8074
Re: Bulk Update [message #348829 is a reply to message #348715] Thu, 18 September 2008 00:52 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
victory_nag wrote on Wed, 17 September 2008 22:14
Can we write the update statement of yours in PL/Sql Block without Exec Immediate?

Yes.

Regards
Michel
Previous Topic: display all dates between two dates
Next Topic: count(1) a bug
Goto Forum:
  


Current Time: Sat Dec 10 14:39:45 CST 2016

Total time taken to generate the page: 0.08600 seconds