View the Exhibits and examine the structure of the EMPLOYEES, DEPARTMENTS AND EMP_BY_DEPT tables.
EMPLOYEES
DEPAERTMENT
EMP_BY_DEPT
Examine the following code:
What is the outcome on execution of the above code?
A.
It executes successfully but the output statements show different values.
B.
It executes successfully and both output statements show the same values.
C.
It gives an error because the SQL%ROWCOUNT attribute cannot be used with BULK COLLECT.
D.
It gives an error because the INSERT SELECT construct cannot be used with the FORALL
It should be B.
As per http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/tuning.htm
SQL%BULK_ROWCOUNT(i) stores the number of rows processed by the ith execution of an INSERT, UPDATE or DELETE statement.
SQL%ROWCOUNT returns useful information about the most recently executed DML statement.
No it should be A with the same explanation 😛
It should be B.
Right answer is A.
sql%Bulk_rowcount(deptnums.count) will return last batch of inserted rows and it is less than overall amount of inserted records, whicjh is sql%rowcount
A is correct
A is correct .
Explanation
i executed the above conde in HR schema
DECLARE
TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
deptnums dept_tab;
BEGIN
SELECT department_id BULK COLLECT INTO deptnums FROM departments;
FORALL i IN 1..deptnums.COUNT
INSERT INTO emp_by_dept
SELECT employee_id, department_id FROM employees
WHERE department_id = deptnums(i);
FOR i IN 1..deptnums.COUNT LOOP
— Count how many rows were inserted for each department; that is,
— how many employees are in each department.
DBMS_OUTPUT.PUT_LINE(‘Dept ‘||deptnums(i)||’: inserted ‘||
SQL%BULK_ROWCOUNT(i)||’ records ‘ || ‘and SQL%rowcount=’||SQL%rowcount);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total records inserted: ‘ || SQL%ROWCOUNT);
END;
/
Output :
Dept 10: inserted 1 records and SQL%rowcount=106
Dept 20: inserted 2 records and SQL%rowcount=106
Dept 30: inserted 6 records and SQL%rowcount=106
Dept 40: inserted 1 records and SQL%rowcount=106
Dept 50: inserted 45 records and SQL%rowcount=106
Dept 60: inserted 5 records and SQL%rowcount=106
Dept 70: inserted 1 records and SQL%rowcount=106
Dept 80: inserted 34 records and SQL%rowcount=106
Dept 90: inserted 3 records and SQL%rowcount=106
Dept 100: inserted 6 records and SQL%rowcount=106
Dept 110: inserted 2 records and SQL%rowcount=106
Dept 120: inserted 0 records and SQL%rowcount=106
Dept 130: inserted 0 records and SQL%rowcount=106
Dept 140: inserted 0 records and SQL%rowcount=106
Dept 150: inserted 0 records and SQL%rowcount=106
Dept 160: inserted 0 records and SQL%rowcount=106
Dept 170: inserted 0 records and SQL%rowcount=106
Dept 180: inserted 0 records and SQL%rowcount=106
Dept 190: inserted 0 records and SQL%rowcount=106
Dept 200: inserted 0 records and SQL%rowcount=106
Dept 210: inserted 0 records and SQL%rowcount=106
Dept 220: inserted 0 records and SQL%rowcount=106
Dept 230: inserted 0 records and SQL%rowcount=106
Dept 240: inserted 0 records and SQL%rowcount=106
Dept 250: inserted 0 records and SQL%rowcount=106
Dept 260: inserted 0 records and SQL%rowcount=106
Dept 270: inserted 0 records and SQL%rowcount=106
Total records inserted: 106
La respuesta correcta es la A, efectivamente ejecuta el código y se muestran 2 resultados diferentes ya que %ROWCOUNT nos muestra el total de filas del cursor abierto por la sentencia
SELECT employe_id, department_id
FROM employees
WHERE department_id=deptnums(i);
En la tabla employees tenemos 107 trabajadores, pero uno sin department_id
y el atributo SQL%BULK_ROWCOUNT(i) es una tributo que se comporta como un array associative, cuyo elemento i es el numero de filas afectadas por la sentencia DML i en la sentencia FORALL mas reciente.
Analizar el ejemplo posteado por MANJUSHA donde claramente vemos que por cada ves que la sentencia FORALL se completa se muestra el numero de filas afectadas por la sentencia SQL [inserted: valor]
NOTA:El tipo de dato del elemento i es de tipo PLS_INTEGER, si el numero de elementos excede el máximo valor permitido en PLS_INTEGER entonces este se vuelve negativo.
EJEMPLO
Mostrar el numero de filas afectadas por cada DELETE en FORALL
DROP TABLE emp_temp;
CREATE TABLE EMP_TEMP AS SELECT * FROM EMPLOYEES;
DECLARE
TYPE numlist IS TABLE OF NUMBER;
depts numlist :=numlist(30,50,60);
BEGIN
FORALL j IN depts.FIRST..depts.LAST
DELETE FROM emp_temp WHERE department_id=depts(j);
FOR i IN depts.FIRTS..depts.LAST LOOP
DBMS_OUTPUT.PUT_LINE(‘Statement #’||i|| ‘ deleted ‘||SQL%BULK_ROWCOUNT(i)||’ rows. ‘);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total rows deleted: ‘|| SQL%rowcount);
END;
/
A
test point is the SQL%BULK_ROWCOUNT/EXCEPTIONS like below, a) is correct
use the “SAVE EXCEPTIONS” in one LOOP to retrieve all the exceptions arising during the LOOP without halting the loop.
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1422998100346727312
C:\applications\PTY\Scripts\Utils\ProdSupport\02-Delete-Selected-Parties.sql
open c; — c is one cursor
loop
fetch c bulk collect into l_data limit 100; — retrieve the first 100 records?
begin
forall i in 1 .. l_data.count SAVE EXCEPTIONS — “SAVE EXCEPTIONS”: log all the exceptions without halting the proceeding of loop
insert into t2 values l_data(i);
exception
when DML_ERRORS then
l_errors := sql%bulk_exceptions.count; — sql%bulk_exceptions: the logged bulk_exceptions
for i in 1 .. l_errors
loop
l_errno := sql%bulk_exceptions(i).error_code;
l_msg := sqlerrm(-l_errno); — sqlerrm(errno)
l_idx := sql%bulk_exceptions(i).error_index;
end loop;
end;
exit when c%notfound;
end loop;
close c;
for FORALL, SAVE EXCEPTIONS and SQL%BULK_EXCEPTION could be the other testing points
A is correct:
– %bulk_rowcount will show records inserted where department_id = deptnums(i),
– %rowcount will show total inserted records.