SQL> create table table_w(name varchar2(100),sal number);
Table created.
SQL> insert into table_w values('A',5000);
1 row created.
SQL> insert into table_w values('B',4000);
1 row created.
SQL> insert into table_w values('A',3000);
1 row created.
SQL> insert into table_w values('C',3000);
1 row created.
SQL> insert into table_w values('B',2000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> CREATE TABLE TABLE_W_BKP AS SELECT * FROM TABLE_W;
Table created.
SET SERVEROUTPUT ON;
DECLARE
CURSOR C1 IS SELECT * FROM TABLE_W;
BEGIN
FOR I IN C1
LOOP
UPDATE TABLE_W SET SAL=SAL+1000 WHERE NAME=I.NAME;
COMMIT;
END LOOP;
END;
/
BEFORE :
SQL> SELECT * FROM TABLE_W;
NAME
SAL
--------------------------------------------------------------------
A
5000
B
4000
A
3000
C
3000
B
2000
AFTER :
SQL> SELECT * FROM TABLE_W;
NAME SAL
------------------------------------- ----------
A 7000
B 6000
A 5000
C 4000
B 4000
SET SERVEROUTPUT ON;
DECLARE
CURSOR C1 IS SELECT * FROM TABLE_W_BKP FOR UPDATE OF SAL NOWAIT;
BEGIN
FOR I IN C1
LOOP
UPDATE TABLE_W_BKP SET SAL=SAL+1000 WHERE CURRENT OF C1;
--OORA-01002: fetch out of sequence.-->If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued --will return the error. Do not issue a COMMIT inside a fetch loop for a cursor that has been opened FOR UPDATE.
--COMMIT;
END LOOP;
COMMIT;
END;
/
BEFORE :
SQL> SELECT * FROM TABLE_W_BKP;
NAME SAL
------------------------------------- ----------
A 6000
B 4000
A 3000
C 3000
B 2000
AFTER :
SQL> SELECT * FROM TABLE_W_BKP;
NAME SAL
------------------------------------- ----------
A 7000
B 5000
A 4000
C 4000
B 3000
Table created.
SQL> insert into table_w values('A',5000);
1 row created.
SQL> insert into table_w values('B',4000);
1 row created.
SQL> insert into table_w values('A',3000);
1 row created.
SQL> insert into table_w values('C',3000);
1 row created.
SQL> insert into table_w values('B',2000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> CREATE TABLE TABLE_W_BKP AS SELECT * FROM TABLE_W;
Table created.
SET SERVEROUTPUT ON;
DECLARE
CURSOR C1 IS SELECT * FROM TABLE_W;
BEGIN
FOR I IN C1
LOOP
UPDATE TABLE_W SET SAL=SAL+1000 WHERE NAME=I.NAME;
COMMIT;
END LOOP;
END;
/
BEFORE :
SQL> SELECT * FROM TABLE_W;
NAME
SAL
--------------------------------------------------------------------
A
5000
B
4000
A
3000
C
3000
B
2000
AFTER :
SQL> SELECT * FROM TABLE_W;
NAME SAL
------------------------------------- ----------
A 7000
B 6000
A 5000
C 4000
B 4000
SET SERVEROUTPUT ON;
DECLARE
CURSOR C1 IS SELECT * FROM TABLE_W_BKP FOR UPDATE OF SAL NOWAIT;
BEGIN
FOR I IN C1
LOOP
UPDATE TABLE_W_BKP SET SAL=SAL+1000 WHERE CURRENT OF C1;
--OORA-01002: fetch out of sequence.-->If the cursor has been opened with the FOR UPDATE clause, fetching after a COMMIT has been issued --will return the error. Do not issue a COMMIT inside a fetch loop for a cursor that has been opened FOR UPDATE.
--COMMIT;
END LOOP;
COMMIT;
END;
/
BEFORE :
SQL> SELECT * FROM TABLE_W_BKP;
NAME SAL
------------------------------------- ----------
A 6000
B 4000
A 3000
C 3000
B 2000
AFTER :
SQL> SELECT * FROM TABLE_W_BKP;
NAME SAL
------------------------------------- ----------
A 7000
B 5000
A 4000
C 4000
B 3000
