Home » SQL & PL/SQL » SQL & PL/SQL » forall update with bulk collect for multiple columns (oracle 10g)
forall update with bulk collect for multiple columns [message #575645] Fri, 25 January 2013 02:06 Go to next message
rajmighty972
Messages: 11
Registered: December 2012
Junior Member
Hi I am trying to update a table column values if any change occurs using bulk collect and for all update not able to get idea.
below is the proc working out.it is for insert and update using the cursors.
CREATE OR REPLACE PROCEDURE PRC_INS(P_ID IN NUMBER,P_STAT OUT NUMBER) IS
  TYPE T_TEST_TAB IS TABLE OF T_DTLS%ROWTYPE;
  	V_PARAM 	 		T_TEST_TAB;
  	V_STATUS   		NUMBER;
  	V_BUS 		    VARCHAR2(20); 
  	V_UP          VARCHAR2(1);
  	V_Q	        VARCHAR2(50); 
   CURSOR C1 IS SELECT S_NO,MEM_ID,MEM_TITLE,MEM_FNAME,MEM_LNAME,F_DATE,N_DATE,MEM_RCODE,MEM_GEN_CODE,
                          FROM T_MEMBER_STAT  WHERE UPL_ID= P_ID AND FLAG='Y' AND MEM_ID IS NULL;
   CURSOR C2 IS SELECT S_NO,MEM_ID,MEM_TITLE,MEM_FNAME,MEM_LNAME,F_DATE,N_DATE,MEM_RCODE,MEM_GEN_CODE,
                          FROM T_MEMBER_STAT  WHERE UPL_ID= P_ID AND FLAG='Y' AND MEM_ID IS NOT NULL;
BEGIN
V_STATUS:=P_STAT;
 SELECT BUS_TYPE,UP_TYPE,QUOTE_EN_POL INTO V_BUS,V_UP,V_Q FROM T_PROCESS WHERE PROC_ID=P_ID;
  IF V_UP='NB' OR V_UP='EN' THEN
   OPEN C1;
      LOOP
         FETCH C1 BULK COLLECT INTO V_PARAM LIMIT 10000;
           EXIT WHEN C1%NOTFOUND;
               FORALL i in 1..V_PARAM.COUNT
                  -----INSERTING THE TABLE RECORDS FOR ENDO----
                  INSERT INTO T_DTLS VALUES V_PARAM(i);
       END LOOP;
       COMMIT;
   CLOSE C1;
 	   P_STAT:=0;
 	 ELSE V_UP='EN' 
 	       UPDATE T_MEMBER_STAT SET MEM_STAT ='1' WHERE UPL_ID=P_ID AND TYPE_OF='DEL';
 	  OPEN C2;
	    LOOP
	      FETCH C2 BULK COLLECT INTO V_PARAM LIMIT 10000;
	        EXIT WHEN C2%NOTFOUND;
	             FORALL i in 1..V_PARAM.COUNT
	                    -----INSERTING THE TABLE RECORDS FOR ENDO----
	              UPDATE T_DTLS SET   (NEEDED FOR MULTIPLE COLUMN VALUE UPDATES)
	               WHERE SR_NO=V_PARAM(i);
       END LOOP;
  COMMIT;
	 CLOSE C2;
	 P_STAT:=0;
END IF;
EXCEPTION
WHEN OTHERS THEN
	P_STAT:=-1;
END PRC_INS;
/

Re: forall update with bulk collect for multiple columns [message #575651 is a reply to message #575645] Fri, 25 January 2013 03:54 Go to previous messageGo to next message
sss111ind
Messages: 473
Registered: April 2012
Location: India
Senior Member

UPDATE T_DTLS SET ROW=V_PARAM(I) WHERE SR_NO=V_PARAM(I).SRNO;

http://atulley.wordpress.com/2009/01/18/using-rowtype-for-inserts-and-updates/

[Updated on: Fri, 25 January 2013 03:55]

Report message to a moderator

Re: forall update with bulk collect for multiple columns [message #575666 is a reply to message #575651] Fri, 25 January 2013 05:24 Go to previous message
rajmighty972
Messages: 11
Registered: December 2012
Junior Member
thanks
Previous Topic: Dynamic %rowtype
Next Topic: Sequence creation
Goto Forum:
  


Current Time: Thu Aug 21 19:58:50 CDT 2014

Total time taken to generate the page: 0.11972 seconds