Oracle Database: Program with PL/SQL — Question 14

In your schema, the DEPARTMENTS table contains the columns DEPARTMENT_ID and DEPARTMENT_NAME.
You want to display the department name for existing department id 10.
With SERVEROUTPUT enabled, which two blocks of code will give the required output?

Answer options

• A. DECLARE TYPE dept_cur IS REF CURSOR; cv1 dept_cur; v_dept_name departments. department_name%TYPE; BEGIN OPEN cv1 FOR SELECT department_name FROM departments WHERE department_id=10; IF cv1 IS NOT NULL THEN FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); END IF CLOSE cv1; END;
• B. DECLARE TYPE dept_cur IS REF CURSOR RETURN departments%ROWTYPE; cv1 dept_cur; v_dept_name departments.department_name%TYPE; BEGIN OPEN cv1 FOR SELECT * FROM departments WHERE department_id=10; FETCH cv1. department_name INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1; END;
• C. DECLARE TYPE names_t IS TABLE OF SYS_REFCURSOR INDEX BY PLS_INTEGER; cv1 names_t; v_dept_name departments.department_name%TYPE; BEGIN OPEN cv1 FOR SELECT department_name FROM departments WHERE department_id=10; FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1; END;
• D. DECLARE cv1 SYS_REFCURSOR; v_dept_name departments.department_name%TYPE; BEGIN EXECUTE IMMEDIATE BEGIN OPEN: cv1 FOR SELECT department_name FROM departmnets WHERE department_id=10: END; USING IN cv1; FETCH cv1 INTO v_dept_name; DBMS_OUTPUT.PUT_LINE (v_dept_name); CLOSE cv1;

Correct answer: C, D

Explanation

Option C correctly uses a PL/SQL block with a cursor to select the department name for the specified department ID and outputs it using DBMS_OUTPUT. Option D tries to execute an immediate block but contains syntax errors and misuses the cursor. Options A and B either incorrectly handle the cursor or do not conform to the requirements specified in the question.