FORALL

View All Scripts Login to Run Script

Script Name FORALL
Description FORALL transfers data from a PL/SQL collection to the specified table using collections.
Area PL/SQL General
Contributor Damián Ribeiro
Created Tuesday September 26, 2017

Statement 1

create table servers (srvr_id int primary key, srvr_name CHAR(1))

Table created.

Statement 2

insert into servers values (1,'A')

1 row(s) inserted.

Statement 3

insert into servers values (2,'A')

1 row(s) inserted.

Statement 4

insert into servers values (3,'A')

1 row(s) inserted.

Statement 5

insert into servers values (4,'A')

1 row(s) inserted.

Statement 6

insert into servers values (5,'B')

1 row(s) inserted.

Statement 7

insert into servers values (6,'B')

1 row(s) inserted.

Statement 8

insert into servers values (7,'C')

1 row(s) inserted.

Statement 9

insert into servers values (8,'D')

1 row(s) inserted.

Statement 10

insert into servers values (9,'D')

1 row(s) inserted.

Statement 11

insert into servers values (10,'D')

1 row(s) inserted.

Statement 12

insert into servers values (11,'D')

1 row(s) inserted.

Statement 13

insert into servers values (12,'E')

1 row(s) inserted.

Statement 14

insert into servers values (13,'E')

1 row(s) inserted.

Statement 15

insert into servers values (14,'F')

1 row(s) inserted.

Statement 16
```
commit
```
```
Statement processed.
```

Statement 17

CREATE TABLE servers2 AS SELECT * FROM servers WHERE 1=2

Table created.

Statement 18

select a.srvr_id, a.srvr_name server_name, b.srvr_name server2_name from servers a left join servers2 b on a.srvr_id=b.srvr_id order by 1


SRVR_ID SERVER_NAME SERVER2_NAME
1 A  - 
2 A  - 
3 A  - 
4 A  - 
5 B  - 
6 B  - 
7 C  - 
8 D  - 
9 D  - 
10 D  - 
11 D  - 
12 E  - 
13 E  - 
14 F  - 

14 rows selected.

SRVR_ID	SERVER_NAME	SERVER2_NAME
1	A	-
2	A	-
3	A	-
4	A	-
5	B	-
6	B	-
7	C	-
8	D	-
9	D	-
10	D	-
11	D	-
12	E	-
13	E	-
14	F	-

Statement 19

FORALL Insert

DECLARE  
	CURSOR s_cur IS SELECT * FROM servers;  
	TYPE fetch_array IS TABLE OF s_cur%ROWTYPE;  
	s_array fetch_array;  
BEGIN  
	OPEN s_cur;  
	LOOP  
	FETCH s_cur BULK COLLECT INTO s_array LIMIT 3;  
		FORALL i IN 1..s_array.COUNT  
			INSERT INTO servers2 VALUES s_array(i);  
			EXIT WHEN s_cur%NOTFOUND;  
		  
		FOR j IN s_array.FIRST..s_array.LAST LOOP  
			DBMS_OUTPUT.put_line('Iteration #' || j || ' inserted ' || SQL%BULK_ROWCOUNT(j) || ' rows.');  
		END LOOP;  
	END LOOP;  
	CLOSE s_cur;  
	COMMIT;  
END;

Iteration #1 inserted 1 rows.
Iteration #2 inserted 1 rows.
Iteration #3 inserted 1 rows.
Iteration #1 inserted 1 rows.
Iteration #2 inserted 1 rows.
Iteration #3 inserted 1 rows.
Iteration #1 inserted 1 rows.
Iteration #2 inserted 1 rows.
Iteration #3 inserted 1 rows.
Iteration #1 inserted 1 rows.
Iteration #2 inserted 1 rows.
Iteration #3 inserted 1 rows.

Statement 20

select a.srvr_id, a.srvr_name server_name, b.srvr_name server2_name from servers a left join servers2 b on a.srvr_id=b.srvr_id order by 1


SRVR_ID SERVER_NAME SERVER2_NAME
1 A A
2 A A
3 A A
4 A A
5 B B
6 B B
7 C C
8 D D
9 D D
10 D D
11 D D
12 E E
13 E E
14 F F

14 rows selected.

SRVR_ID	SERVER_NAME	SERVER2_NAME
1	A	A
2	A	A
3	A	A
4	A	A
5	B	B
6	B	B
7	C	C
8	D	D
9	D	D
10	D	D
11	D	D
12	E	E
13	E	E
14	F	F

Statement 21

FORALL Update

DECLARE  
	TYPE myarray IS TABLE OF servers2.srvr_name%TYPE  
	INDEX BY BINARY_INTEGER;  
	s_array myarray;  
BEGIN  
	s_array(1) := 'B';  
	s_array(2) := 'D';  
	s_array(3) := 'F';  
  
	FORALL i IN s_array.FIRST..s_array.LAST  
		UPDATE servers2 SET srvr_name = 'U' WHERE srvr_name = s_array(i);  
		COMMIT;	  
	FOR j IN s_array.FIRST..s_array.LAST LOOP  
		DBMS_OUTPUT.put_line('Iteration #' || j || ' updated ' || SQL%BULK_ROWCOUNT(j) || ' rows.');  
	END LOOP;  
END;

Iteration #1 updated 2 rows.
Iteration #2 updated 4 rows.
Iteration #3 updated 1 rows.

Statement 22

select a.srvr_id, a.srvr_name server_name, b.srvr_name server2_name from servers a left join servers2 b on a.srvr_id=b.srvr_id order by 1


SRVR_ID SERVER_NAME SERVER2_NAME
1 A A
2 A A
3 A A
4 A A
5 B U
6 B U
7 C C
8 D U
9 D U
10 D U
11 D U
12 E E
13 E E
14 F U

14 rows selected.

SRVR_ID	SERVER_NAME	SERVER2_NAME
1	A	A
2	A	A
3	A	A
4	A	A
5	B	U
6	B	U
7	C	C
8	D	U
9	D	U
10	D	U
11	D	U
12	E	E
13	E	E
14	F	U

Statement 23

FORALL Delete

DECLARE  
	TYPE myarray IS TABLE OF servers2.srvr_name%TYPE  
	INDEX BY BINARY_INTEGER;  
	s_array myarray;  
BEGIN  
	s_array(1) := 'A';  
	s_array(2) := 'C';  
	s_array(3) := 'E';  
   
	FORALL i IN s_array.FIRST..s_array.LAST  
		DELETE servers2 WHERE srvr_name = s_array(i);  
		COMMIT;  
	FOR j IN s_array.FIRST..s_array.LAST LOOP  
		DBMS_OUTPUT.put_line('Iteration #' || j || ' deleted ' || SQL%BULK_ROWCOUNT(j) || ' rows.');  
	END LOOP;  
END;

Iteration #1 deleted 4 rows.
Iteration #2 deleted 1 rows.
Iteration #3 deleted 2 rows.

Statement 24

select a.srvr_id, a.srvr_name server_name, b.srvr_name server2_name from servers a left join servers2 b on a.srvr_id=b.srvr_id order by 1


SRVR_ID SERVER_NAME SERVER2_NAME
1 A  - 
2 A  - 
3 A  - 
4 A  - 
5 B U
6 B U
7 C  - 
8 D U
9 D U
10 D U
11 D U
12 E  - 
13 E  - 
14 F U

14 rows selected.

SRVR_ID	SERVER_NAME	SERVER2_NAME
1	A	-
2	A	-
3	A	-
4	A	-
5	B	U
6	B	U
7	C	-
8	D	U
9	D	U
10	D	U
11	D	U
12	E	-
13	E	-
14	F	U

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database