Assume that the tab1 table exists in the underlying database. Q1. What is the va
ID: 3832768 • Letter: A
Question
Assume that the tab1 table exists in the underlying database.
Q1. What is the value of v_flag when the following PL/SQL block is executed successfully?
DECLARE
v_n NUMBER;
v_flag BOOLEAN;
BEGIN
SELECT COUNT(*)
INTO v_n
FROM tab1;
v_flag := SQL%NOTFOUND;
END;
A. The value is always NULL.
B. The value is NULL if and only if the tab1 table is empty.
C. The value is NULL if and only if the tab1 table is not empty.
D. The value is always FALSE.
E. The value is FALSE if and only if the tab1 table is empty.
F. The value is FALSE if and only if the tab1 table is not empty.
G. The value is always TRUE.
H. The value is TRUE if and only if the tab1 table is empty.
I. The value is TRUE if and only if the tab1 table is not empty.
Q2. PL/SQL records of the same declared type can be compared for equality by using the equality operator (=).
DECLARE
TYPE t_type IS RECORD (s_id NUMBER, s_age NUMBER);
v_1 t_type;
v_2 t_type;
BEGIN
......
IF v_1 = v_2 THEN
DBMS_OUTPUT.PUT_LINE('v_1 = v_2');
ELSE
DBMS_OUTPUT.PUT_LINE('v_1 != v_2');
END IF;
END;
A. TRUE
B. FALSE
Q3. In the DECLARATION section of a PL/SQL block, you declare these variables:
v_room_100, v_room_200 NUBMER(3);
Why does this statement cause an error?
A. A default value must be assigned to each variable.
B. The NOT NULL keyword is missing.
C. The size (3) of the data type NUMBER cannot be specified.
D. Multiple variables cannot be declared in the same statement.
E. All of the above.
Q4. Consider the following SELECT-INTO statement in a PL/SQL block. What happens if there is NO row satisfying the WHERE condition?
......
SELECT COUNT(*)
INTO v_n
FROM tab1
WHERE c15 > 10 OR c20 > 10;
......
A. A NO_DATA_FOUND exception is raised.
B. A TOO_MANY_ROWS exception is raised.
C. A ZERO_DIVIDE exception is raised.
D. The SELECT-INTO statement executes successfully.
Q5. In which section of a PL/SQL block is a WHEN ZERO_DIVIDE THEN statement allowed (The ZERO_DIVIDE is a predefined exception for ORA-01476)?
A. DECLARATION
B. EXECUTION
C. EXCEPTION
D. All of the above
Q6. Which guideline relates to a CURSOR FOR Loop?
FOR idx IN cursor_name LOOP
statement1;
statement2;
......
END LOOP;
A. The user must explicitly declare the idx in the DECLARATION section.
B. The cursor must return at least one row.
C. It does not require a FETCH statement.
D. All of the above
Q7. Evaluate the following CURSOR statement:
DECLARE
CURSOR c_1 (p_max_num NUMBER(3) := 500) IS
SELECT col1, col2, col3
FROM tab1
WHERE col15 <= p_max_num;
Why will this statement cause an error?
A. The SELECT statement is missing the INTO clause.
B. The default value (500) cannot be assigned to the p_max_num parameter.
C. The size (3) of the p_max_num parameter cannot be specified.
D. All of the above.
Q8. Evaluate the following CASE statement:
CASE v_input
WHEN 1 THEN v_out := 100; v_input := 3;
WHEN 2 THEN v_out := 202; v_input := 4;
WHEN 3 THEN v_out := 606; v_input := 5;
WHEN 4 THEN v_out := 410; v_input := 5;
WHEN 5 THEN v_out := 881;
ELSE v_out := 991;
END CASE;
If v_input is 3, which value would be assigned to v_out?
A. 100
B. 202
C. 410
D. 606
E. 881
F. 991
G. None of the above
Q9. In a PL/SQL block, when a variable is declared as NOT NULL, you must initialize the variable when it is declared.
A. TRUE
B. FALSE
Q10. Evaluate the following PL/SQL block:
DECLARE
CURSOR c_1 IS SELECT c1, c3 FROM tab1 ORDER BY c1;
v_rec c_1%ROWTYPE;
BEGIN
OPEN c_1;
LOOP
FETCH c_1 INTO v_rec;
EXIT WHEN v_rec.c1 >= 100 OR v_rec.c1 <= -50
OR c_1%NOTFOUND;
......
DBMS_OUTPUT.PUT_LINE(v_rec%ROWCOUNT);
DBMS_OUTPUT.PUT_LINE(v_rec.c3);
......
END LOOP;
CLOSE c_1;
END;
Why will the above block cause a syntax error?
A. The DBMS_OUTPUT.PUT_LINE(v_rec.c3)statement is illegal.
B. The DBMS_OUTPUT.PUT_LINE(v_rec%ROWCOUNT)statement is illegal.
C. The EXIT-WHEN statement is illegal.
D. The FETCH statement is illegal.
E. The %ROWTYPE attribute can only be used in reference to actual tables.
Q11. An exception will be raised in the DECLARATION section of the block BL_2. To which of the following section will the exception propagate?
<<BL_1>>
DECLARE
v_4 NUMBER;
v_5 NUMBER;
BEGIN
v_4 := 10;
v_5 := v_4 + 80;
<<BL_2>>
DECLARE
v_3 NUMBER := 90;
v_4 NUMBER := v_3 / (v_3 - v_5);
-- Run-time error, propagate to?
BEGIN
v_3 := v_3 * v_4 + 1;
v_4 := v_3 + v_4;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR (BL_2)!');
END BL_2;
v_4 := v_4 * v_4;
<<BL_3>>
DECLARE
v_2 NUMBER := 0;
v_5 NUMBER := v_4;
BEGIN
......
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR (BL_3)!');
END BL_3;
<<BL_4>>
DECLARE
v_1 NUMBER := 3;
BEGIN
......
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR (BL_4)!');
END BL_4;
......
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR (BL_1)!');
END BL_1;
A1. The BL_1 block’s DECLARATION section
A2. The BL_1 block’s EXECUTION section
A3. The BL_1 block’s EXCEPTION section
B1. The BL_2 block’s DECLARATION section
B2. The BL_2 block’s EXECUTION section
B3. The BL_2 block’s EXCEPTION section
C1. The BL_3 block’s DECLARATION section
C2. The BL_3 block’s EXECUTION section
C3. The BL_3 block’s EXCEPTION section
D1. The BL_4 block’s DECLARATION section
D2. The BL_4 block’s EXECUTION section
D3. The BL_4 block’s EXCEPTION section
E. None of the above
Q12. How many rows will be inserted into the tab1 table after the following PL/SQL block has been executed successfully (no runtime error)?
DECLARE
v_count NUMBER := 1;
v_1 NUMBER := 1;
BEGIN
DELETE FROM tab1;
COMMIT;
FOR i IN REVERSE 6..11 LOOP
INSERT INTO tab1 VALUES (i, i*2, i+3);
END LOOP;
SELECT COUNT(*)
INTO v_count
FROM tab1;
FOR i IN 3..v_count + 10 LOOP
INSERT INTO tab1 VALUES (i, i+10, i+20);
END LOOP;
INSERT INTO tab1 VALUES (99, 205, 306);
SELECT COUNT(*)
INTO v_1
FROM tab1;
WHILE v_1 >= 7 LOOP
IF v_1 = 10 OR v_1 = 25 OR v_1 = 35 OR v_1 = 45 THEN
INSERT INTO tab1 VALUES (v_1*20, v_1*30, v_1*40);
ELSE
v_1 := v_1 - 1;
END IF;
v_1 := v_1 - 2;
END LOOP;
FOR i IN 2..48 LOOP
IF i = 4 OR i = 16 OR i= 32 OR i = 48 THEN
INSERT INTO tab1 VALUES (i*20, i*30, i*40);
END IF;
INSERT INTO tab1 VALUES (i*21, i*31, i*41);
END LOOP;
INSERT INTO tab1 VALUES (616, 222, 243);
INSERT INTO tab1 VALUES (77, 88, 99);
INSERT INTO tab1 VALUES (77, 88, 55);
INSERT INTO tab1 VALUES (66, 88, 99);
INSERT INTO tab1 VALUES (66, 88, 91);
COMMIT;
END;
A. 74
B. 75
C. 76
D. 77
E. 78
F. None of the above
Explanation / Answer
1 . H - valjrbis true if and only if thr tab1 is empty.
2. False - used to check values data of scalar data type.
3 . All thr above
A default value must be assigned or nentioned as not null. The number size will not be 3 and declaration must contain single variable.
4 . No data found exception is raised
5) declaration
6) all the above
It needs fetch statement and must return a row.
7) C -the of p_max is 3 cannot be declared so
8) 606
9) true
10) DBMS_OUTPUT.PUT_line(v_rec) is illegal declaration.
11) bl_declaration
12)77
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.