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
|
Download Printable PDF. VALID exam to help you PASS. |
 |
If you alter the table and set job varchar2(18) to (25) the code executes successfully (all inserts, updates). Therefore the correct answer is A
alter table emp_temp modify job varchar2(25)
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 the forall statement’);
commit;
end;
10 clerk(senior)
20 bookkeeper(senior)
30 analyst(senior)
Correct answer is B.
I agree with Canelas:
second update will raise exception because value length exceeds the column length.
create table emp_temp( deptno number(2), job varchar2(18));
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 the forall statement’);
commit;
end;
/
problem in the forall statement
PL/SQL procedure successfully completed.
SQL> select * from emp_temp;
DEPTNO JOB
———- ——————
10 clerk (senior)
20 bookkeeper
30 analyst
create table emp_temp( deptno number(2), job varchar2(18));
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 the forall statement’);
commit;
end;
/
Table created.
Statement processed.
CORRECT ANSWER ‘A’
B is correct.
second update raises exception because value length exceeds the column length.
Correct B.
Correct A
CREATE TABLE emp_temp(deptno NUMBER(2), job varchar2(18));
SELECT * FROM emp_temp;
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, ‘Book’);
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(‘cos z FORAL’);
COMMIT;
END;
/
–PL/SQL procedure successfully completed.
SELECT * FROM emp_temp;
/*
DEPTNO JOB
———- ——————
10 Clerk (senior)
20 Book (senior)
30 Analyst (senior)
*/