Quiz 3

-********************************************************- -
I create and populate the following table:

CREATE TABLE my_employees
(
last_name VARCHAR2 (100)
, salary NUMBER
)
/

BEGIN
INSERT INTO my_employees
VALUES ('Smith', 100);

INSERT INTO my_employees
VALUES ('Silva', 200);

INSERT INTO my_employees
VALUES ('Singh', 300);

INSERT INTO my_employees
VALUES ('Chen', 400);

INSERT INTO my_employees
VALUES ('Corvalis', 500);

INSERT INTO my_employees
VALUES ('Feuerstein', 600);

COMMIT;
END;
/
I then write the following block:

DECLARE
TYPE namelist_t IS TABLE OF my_employees.last_name%TYPE;

l_filters namelist_t := namelist_t ('S%', 'C%', 'F%');
BEGIN
FORALL indx IN 1 .. l_filters.COUNT
UPDATE my_employees
SET salary = salary * 1.1
WHERE UPPER (last_name) LIKE l_filters (indx);

/*COUNT*/
END;
/

Which of the following choices can be used in place of the /*COUNT*/ comment so that the following text is displayed on the screen:

3
2
1
Answer
SQL%BULK_ROWCOUNT is referred to as a "pseudo-collection" because it shares some, but not all, of the properties of a true, PL/SQL collection. Specifically, none of the standard methods of a collection work with SQL%BULK_ROWCOUNT. If you want to iterate through the elements of SQL%BULK_ROWCOUNT, then you must reconstruct the looping algorithm employed by FORALL to identify and execute the DML statements. With "FORALL indx IN low_value .. high_value", you can execute code like this:

FOR indx IN low_value .. high_value
LOOP
DBMS_OUTPUT.PUT_LINE (SQL%BULK_ROWCOUNT(indx));
END LOOP;
If, on the other hand, you used INDICES OF or VALUES OF, you may have to write a considerably more complicated looping algorithm.

Here is the code I used to check my answers:

DROP TABLE my_employees
/

CREATE TABLE my_employees
(
last_name VARCHAR2 (100)
, salary NUMBER (3)
)
/

BEGIN
INSERT INTO my_employees
VALUES ('Smith', 100);

INSERT INTO my_employees
VALUES ('Silva', 200);

INSERT INTO my_employees
VALUES ('Singh', 300);

INSERT INTO my_employees
VALUES ('Chen', 400);

INSERT INTO my_employees
VALUES ('Corvalis', 500);

INSERT INTO my_employees
VALUES ('Feuerstein', 600);

COMMIT;
END;
/


DECLARE
TYPE namelist_t IS TABLE OF my_employees.last_name%TYPE;

l_filters namelist_t := namelist_t ('S%', 'C%', 'F%');
BEGIN
dbms_output.put_line ( 'Use binding array index values' );

FORALL indx IN 1 .. l_filters.COUNT
UPDATE my_employees
SET salary = salary * 1.1
WHERE UPPER (last_name) LIKE l_filters (indx);

FOR indx IN 1 .. l_filters.COUNT
LOOP
DBMS_OUTPUT.put_line (SQL%BULK_ROWCOUNT (indx));
END LOOP;

ROLLBACK;
END;
/

DECLARE
TYPE namelist_t IS TABLE OF my_employees.last_name%TYPE;

l_filters namelist_t := namelist_t ('S%', 'C%', 'F%');
BEGIN
dbms_output.put_line ( 'Use COUNT method with pseudo-collection' );

FORALL indx IN 1 .. l_filters.COUNT
UPDATE my_employees
SET salary = salary * 1.1
WHERE UPPER (last_name) LIKE l_filters (indx);

FOR indx IN 1 .. SQL%BULK_ROWCOUNT.COUNT
LOOP
DBMS_OUTPUT.put_line (SQL%BULK_ROWCOUNT (indx));
END LOOP;

ROLLBACK;
END;
/

DECLARE
TYPE namelist_t IS TABLE OF my_employees.last_name%TYPE;

l_filters namelist_t := namelist_t ('S%', 'C%', 'F%');
BEGIN
dbms_output.put_line ( 'Use FIRST and LAST methods with pseudo-collection');

FORALL indx IN 1 .. l_filters.COUNT
UPDATE my_employees
SET salary = salary * 1.1
WHERE UPPER (last_name) LIKE l_filters (indx);

FOR indx IN SQL%BULK_ROWCOUNT.FIRST .. SQL%BULK_ROWCOUNT.LAST
LOOP
DBMS_OUTPUT.put_line (SQL%BULK_ROWCOUNT (indx));
END LOOP;

ROLLBACK;
END;
/

DECLARE
TYPE namelist_t IS TABLE OF my_employees.last_name%TYPE;

l_filters namelist_t := namelist_t ('S%', 'C%', 'F%');
BEGIN
dbms_output.put_line ( 'Use explicit index values with pseudo-collection');

FORALL indx IN 1 .. l_filters.COUNT
UPDATE my_employees
SET salary = salary * 1.1
WHERE UPPER (last_name) LIKE l_filters (indx);

DBMS_OUTPUT.put_line (SQL%BULK_ROWCOUNT (1));
DBMS_OUTPUT.put_line (SQL%BULK_ROWCOUNT (2));
DBMS_OUTPUT.put_line (SQL%BULK_ROWCOUNT (3));

ROLLBACK;
END;
/
********************End*********************************-