Which statement is true about the outcome of the above code?

You create the following table and execute the following code:

You create the following table and execute the following code:

Which statement is true about the outcome of the above code?

A.
It executes successfully and all the rows are updated.

B.
It gives an error but saves the inserted rows and the update to the first row.

C.
It gives an error but saves the inserted rows; however, no rows are updated.

D.
It gives an error and all the data manipulation language (DML) statements are rolled back

Show Hint

← Previous question

Next question →

Leave a Reply 22

Uladzimir

error: variable buffer too small
length of “Bookkeeper (Senior)” = 19 and more than field size of emp_temp.job

piero

alex

B for sure.

pooja

create table p_temp (a number(2),job varchar2(18));

declare
type numlist is table of number;
depts numlist := numlist(10,20,30);

begin

insert into p_temp values(10,’Clerk’);

insert into p_temp values(20,’BClerk’);

insert into p_temp values(30,’AClerk’);

forall j in depts.first..depts.last
update p_temp set job= job||’ Senior’ where a = depts(j);

exception when others then
dbms_output.put_line(‘problem’);
commit;

end;
/

select * from p_temp;

nancy

ans b

Bogdan

Excactly,

A is he correct answer !!!

DECLARE
TYPE NumList IS TABLE OF NUMBER;
depts Numlist := NumList(10,20,30);
BEGIN
INSERT INTO emp_temp VALUES (10,’Clerk’);
INSERT INTO emp_temp VALUES (20,’Bookkeeper’);
INSERT INTO emp_temp VALUES (30,’Analyst’);
FORALL j IN depts.FIRST .. depts.LAST
UPDATE emp_temp SET job = job || ‘ Senior’ WHERE deptno = depts(j);
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(‘Problem in FORALL statement;’);
COMMIT;
END;
/
SELECT * FROM emp_temp

10 Clerk Senior
20 Bookkeeper Senior
30 Analyst Senior

pooja didn’t look at result he selected.

ANSWER IS B

Answer cannot be A, cause NOT ALL ROWS ARE UPDATED, oNLY 1st

result will be like this

DEPTNO | JOB
10 | Clerk (Senior)
20 | Bookkeeper
30 | Analyst

But cause of Exception handling we will see mistake in OUTPUT. Not one that oracle throw.

maybe it will help someone to understand.

select length(‘Bookkeeper (Senior)’) from dual;

result: 19
but column varchar2(18)

Andreea

The correct answer is A. I tested it

Adwanzo

What u mean u test it ?
U mean the exam and u recognized its a
Or u try it in ur PC

Andreea

I wrote the code in my PC

Bruno

You are missing a space in the ‘ (Senior)’

SQL> declare
2 type numlist is table of number;
3 depts numlist := numlist(10,20,30);
4 begin
5 insert into p_temp values(10,’Clerk’);
6 insert into p_temp values(20,’BOOK Keeper’);
7 insert into p_temp values(30,’Analyst’);
8 forall j in depts.first..depts.last
9 update p_temp set job= job||’ (Senior)’ where a = depts(j);
10 exception when others then
11 dbms_output.put_line(‘problem’);
12 commit;
13 end;
14 /
problem

PL/SQL procedure successfully completed.

SQL> select * from p_temp;

A JOB
———- ——————
20 BOOK Keeper
30 Analyst
10 Clerk (Senior)

Eduardo

Its C, In bulk if one fail then the operation of FORALL fails

Eduardo

Sorry im wrong

“If a FORALL statement fails, database changes are rolled back to an implicit savepoint marked before each execution of the SQL statement. Changes made during previous iterations of the FORALL loop are not rolled back.”