PL/SQL Questions

Q1. 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 col_15 > 10 OR col_20 > 10;

......

A. The SELECT-INTO statement executes successfully.

B. A NO_DATA_FOUND exception is raised.

C. A TOO_MANY_ROWS exception is raised.

D. A ZERO_DIVIDE exception is raised.

Q2. In which section of a PL/SQL block is a WHEN TOO_MANY_ROWS THEN statement allowed (The TOO_MANY_ROWS is a predefined exception for ORA-01422)?

A. DECLARATION

B. EXECUTION

C. EXCEPTION

D. All of the above

Q3. Evaluate the following CASE statement:

CASE v_input

WHEN 10 THEN v_out := 100; v_input := 30;

WHEN 20 THEN v_out := 202; v_input := 40;

WHEN 30 THEN v_out := 606; v_input := 50;

WHEN 40 THEN v_out := 410; v_input := 60;

WHEN 50 THEN v_out := 881;

ELSE v_out := 991;

END CASE;

If v_input is 20, 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

Q4. In the DECLARATION section of a PL/SQL block, you declare these variables:

v_room_100, v_room_200 VARCHAR2(15);

Why does this statement cause an error?

A. The NOT NULL keyword is missing.

B. The size (15) of the data type VARCHAR2 cannot be specified.

C. Multiple variables cannot be declared in the same statement.

D. A default value must be assigned to each variable.

E. All of the above.

Q5. PL/SQL records of the same declared type can be compared for equality by using the equality operator (=).

DECLARE

TYPE t_type IS RECORD (p_id NUMBER, p_age NUMBER);

v_a t_type;

v_b t_type;

BEGIN

......

IF v_a = v_b THEN

DBMS_OUTPUT.PUT_LINE('v_a = v_b');

ELSE

DBMS_OUTPUT.PUT_LINE('v_a != v_b');

END IF;

END;

A. TRUE

B. FALSE

Q6. What is the value of v_found_flag when the following PL/SQL block is executed successfully?

DECLARE

v_n NUMBER;

v_found_flag BOOLEAN;

BEGIN

SELECT COUNT(*)

INTO v_n

FROM tab1

WHERE col_99 > 0;

v_found_flag := SQL%FOUND;

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.

Q7. Which guideline relates to a CURSOR FOR Loop?

FOR v_idx IN cursor_name LOOP

statement1;

statement2;

......

END LOOP;

A. The user must explicitly declare the v_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

Q8. Evaluate the following CURSOR statement:

DECLARE

CURSOR c_1 (p_max_num NUMBER(3) := 500) IS

SELECT col_1, col_2, col_3

FROM tab1

WHERE col1_5 = p_max_num;

Why will this statement cause an error?

A. The default value (500) cannot be assigned to the p_max_num parameter.

B. The size (3) of the p_max_num parameter cannot be specified.

C. The SELECT statement is missing the INTO clause.

D. All 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 col_1, col_3

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.col_1 >= 100 OR

v_rec.col_1 <= -50 OR

c_1%NOTFOUND;

......

DBMS_OUTPUT.PUT_LINE(v_rec%ROWCOUNT);

DBMS_OUTPUT.PUT_LINE(v_rec.col_3);

......

END LOOP;

CLOSE c_1;

END;

Why will the above block cause a syntax error?

A. The DBMS_OUTPUT.PUT_LINE(v_rec.col_3)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_20. To which of the following section will the exception propagate?

<>

DECLARE

v_4 NUMBER;

v_5 NUMBER;

BEGIN

v_4 := 10;

v_5 := v_4 + 80;

<>

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_20)!');

END BL_20;

v_4 := v_4 * v_4;

<>

DECLARE

v_2 NUMBER := 0;

v_5 NUMBER := v_4;

BEGIN

......

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR (BL_30)!');

END BL_30;

<>

DECLARE

v_1 NUMBER := 3;

BEGIN

......

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR (BL_40)!');

END BL_40;

......

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR (BL_10)!');

END BL_10;

A1. The BL_10 blocks DECLARATION section

A2. The BL_10 blocks EXECUTION section

A3. The BL_10 blocks EXCEPTION section

B1. The BL_20 blocks DECLARATION section

B2. The BL_20 blocks EXECUTION section

B3. The BL_20 blocks EXCEPTION section

C1. The BL_30 blocks DECLARATION section

C2. The BL_30 blocks EXECUTION section

C3. The BL_30 blocks EXCEPTION section

D1. The BL_40 blocks DECLARATION section

D2. The BL_40 blocks EXECUTION section

D3. The BL_40 blocks 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 + 11 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. 75

B. 76

C. 77

D. 78

E. 79

F 80