PL/SQL Section 5 Quiz

 1. Which of the following statements about the %ISOPEN cursor attribute is true?

Tandai untuk Ditinjau

(1) Point

If a cursor is open, then the value of %ISOPEN is false.

You can issue the %ISOPEN cursor attribute only when more than one record is returned.

You can issue the %ISOPEN cursor attribute only when a cursor is open.

You can issue the %ISOPEN cursor attribute when a cursor is open or closed. (*)

Correct

---

2. Assume that you have declared a cursor called C_EMP. Which of the following statements about C_EMP is correct? (Choose two.)

Tandai untuk Ditinjau

(1) Point

You can use c_emp%NOTFOUND to exit a loop.(*)

You can use c_emp%ROWCOUNT to return the number of rows returned by the cursor so far.(*)

You can fetch rows when c_emp%ISOPEN evaluates to FALSE.

You can use c_emp%FOUND after the cursor is closed.

Correct

---

3. You execute the following code:

DECLARE
  CURSOR emp_curs IS SELECT last_name FROM employees;
  v_last_name employees.last_name%TYPE;
BEGIN
  OPEN emp_curs;
  LOOP -- Point A
   FETCH emp_curs INTO v_last_name;
   EXIT WHEN emp_curs%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE(v_last_name);
  END LOOP;
  CLOSE emp_curs;
END;

At Point A (after you have OPENed the cursor) another user updates an employee's last_name from 'Smith' to 'Jones' and immediately COMMITs.

When your block FETCHes this row, which value will be fetched and displayed?

Tandai untuk Ditinjau

(1) Point

An INVALID_CURSOR exception will be raised when you try to FETCH the row.

Smith (*)

No value will be displayed

Smith and Jones (the row will be fetched twice)

Jones

Incorrect. Refer to Section 5 Lesson 1.

---

4. One (and only one) employee has LAST_NAME = 'Grant'. You need to code:
SELECT ... FROM employees WHERE last_name = 'Grant';

Which type of cursor should you use, and why?

Tandai untuk Ditinjau

(1) Point

An explicit cursor, because you can use an implicit cursor only for DML statements.

An implicit cursor, because there is only one 'Grant'.

An implicit cursor, because SELECT is a SQL statement and implicit cursors are always called "SQL".

An explicit cursor, because there could be more than one 'Grant' in the future. (*)

Incorrect. Refer to Section 5 Lesson 1.

---

5. You cannot OPEN or CLOSE an implicit cursor. Why not?

Tandai untuk Ditinjau

(1) Point

Because an implicit cursor is always called SQL.

Because an implicit cursor is OPENed and CLOSEd automatically by Oracle. (*)

Correct

6. Which one of the following statements is NOT true?

Tandai untuk Ditinjau

(1) Point

You can not use an INTO clause when declaring an explicit cursor.

An explicit cursor can select from only one table. No joins are allowed. (*)

An explicit cursor must be DECLAREd before it can be OPENed.

You can use ORDER BY when declaring an explicit cursor.

Correct

---

7. Examine the following code:

DECLARE
  CURSOR region_cur IS
   SELECT * FROM wf_world_regions;
  v_region_rec region_cur%ROWTYPE;
  CURSOR country_cur (p_region_id NUMBER) IS
   SELECT * FROM wf_countries
   WHERE region_id = p_region_id;
   v_country_rec country_cur%ROWTYPE;
BEGIN
  OPEN region_cur;
  LOOP
   FETCH region_cur INTO v_region_rec;
   EXIT WHEN region_cur%NOTFOUND;
   DBMS_OUTPUT.PUT_LINE
    (v_region_rec.region_name);
   -- Line A --
   LOOP
    FETCH country_cur INTO v_country_rec;
    EXIT WHEN country_cur%NOTFOUND;
     ......

What would you code at Line A?

Tandai untuk Ditinjau

(1) Point

OPEN country_cur (region_cur.region_id);

OPEN country_cur (v_region_rec.region_id); (*)

OPEN country_cur (p_region_id);

OPEN country_cur (wf_world_regions.region_id);

OPEN country_cur;

Incorrect. Refer to Section 5 Lesson 6.

---

8. What is wrong with the following code?

DECLARE
  CURSOR emp_curs(p_dept_id NUMBER) IS
  SELECT * FROM employees WHERE department_id = p_dept_id;
BEGIN
  FOR dept_rec IN (SELECT * FROM departments) LOOP
    DBMS_OUTPUT.PUT_LINE(dept_rec.department_name);
    FOR emp_rec IN emp_curs(dept_rec.department_id) LOOP
      DBMS_OUTPUT.PUT_LINE(emp_rec.last_name);
    END LOOP;
  END LOOP;
END;

Tandai untuk Ditinjau

(1) Point

You cannot use two different kinds of loops in a single PL/SQL block.

Nothing is wrong. The block will execute successfully and display all departments and the employees in those departments. (*)

The DEPARTMENTS cursor must be declared with a parameter.

You cannot use a cursor with a subquery in nested loops.

EMP_CURS should not be DECLAREd explicitly; it should be coded as a subquery in a cursor FOR loop.

Incorrect. Refer to Section 5 Lesson 6.

---

9. There are 12 distinct JOB_IDs in the EMPLOYEES table. You need to write some PL/SQL code to fetch and display all the employees with a specific JOB_ID. The chosen JOB_ID can be different each time the code is executed.
What is the best way to do this?

Tandai untuk Ditinjau

(1) Point

Write 12 separate PL/SQL blocks, each declaring a cursor with a different JOB_ID in the WHERE clause.

Write a single PL/SQL block which declares one cursor using a parameter for the JOB_ID. (*)

Write a single PL/SQL block which declares 12 cursors, one for each distinct value of JOB_ID.

Write a single PL/SQL block which uses a cursor to fetch all the employee rows, with an IF statement to decide which of the fetched rows to display.

Incorrect. Refer to Section 5 Lesson 4.

---

10. Look at the following code:

DECLARE
  CURSOR emp_curs (p_dept_id employees.department_id%TYPE) IS
   SELECT * FROM employees
   WHERE department_id = p_dept_id;
  v_emp_rec emp_curs%ROWTYPE;
  v_deptid NUMBER(4) := 50;
BEGIN
  OPEN emp_curs( -- Point A --);
  ....

You want to open the cursor, passing value 50 to the parameter. Which of the following are correct at Point A?

Tandai untuk Ditinjau

(1) Point

100 / 2

50

v_deptid

All of these. (*)

11. What is wrong with the following code?

DECLARE
    CURSOR dept_curs IS SELECT * FROM departments;
BEGIN
    FOR dept_rec IN dept_curs LOOP
       DBMS_OUTPUT.PUT_LINE(dept_curs%ROWCOUNT || dept_rec.department_name):
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(dept_rec.department_id);
END;

Tandai untuk Ditinjau

(1) Point

Nothing is wrong, this code will execute successfully.

The cursor DEPT_CURS has not been closed.

The cursor DEPT_CURS has not been opened.

You cannot use %ROWCOUNT with a cursor FOR loop.

The implicitly declared record DEPT_REC cannot be referenced outside the cursor FOR loop. (*)

Correct

---

12. A cursor FOR loop using a subquery can shorten code length when compared to an explicit cursor declaration. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

13. What is the difference between the following two blocks of code?

--Block A
DECLARE
    CURSOR emp_cursor IS
    SELECT employee_id, last_name
    FROM employees
    WHERE department_id = 80
    FOR UPDATE OF salary;

--Block B
DECLARE
    CURSOR emp_cursor IS
    SELECT employee_id, last_name
    FROM employees
    WHERE department_id = 80
    FOR UPDATE OF salary
    NOWAIT;

Tandai untuk Ditinjau

(1) Point

In Block A, the program waits indefinitely until locked rows are available. In Block B, the program returns control immediately so that it can do other work. (*)

In Block A, the program waits indefinitely until locked rows are available. In Block B, control is returned to your program after 5 seconds so that it can do other work.

There is no difference; the programs behave exactly the same way.

Incorrect. Refer to Section 5 Lesson 5.

---

14. You have declared a cursor as SELECT .... FOR UPDATE; You have OPENed the cursor and locked the FETCHed rows. When are these row locks released?

Tandai untuk Ditinjau

(1) Point

When your block finishes executing

When another user tries to SELECT the rows

When you explicitly COMMIT or ROLLBACK your transaction (*)

When an UPDATE ... WHERE CURRENT OF cursor_name; is executed

When you CLOSE the cursor

Incorrect. Refer to Section 5 Lesson 5.

---

15. When can we use the WHERE CURRENT OF clause?

Tandai untuk Ditinjau

(1) Point

When the cursor is based on a single table (not on a join)

Only with a DELETE, but not with an UPDATE

When the cursor has not been OPENed

When the cursor is declared as SELECT ... FOR UPDATE ...; (*)

1. The employees table contains 11 columns. The following block declares a cursor and a record based on the cursor:

DECLARE
    CURSOR emp_curs IS
       SELECT * FROM employees;
    v_emp_rec emp_curs%ROWTYPE;

A twelfth column is now added to the employees table. Which of the following statements is true?

Tandai untuk Ditinjau

(1) Point

The declaration of emp_rec must be changed to add an extra field.

The block will still work correctly without any changes to the PL/SQL code. (*)

The block will fail and an INVALID_CURSOR exception will be raised.

An extra scalar variable must be declared to correspond to the twelfth table column.

Incorrect. Refer to Section 5 Lesson 2.

---

2. You can reference explicit cursor attributes directly in a SQL statement. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 5 Lesson 2.

---

3. What is missing from the following cursor declaration?

  CURSOR emp_curs
IS
SELECT * FROM departments
  WHERE location_id = p_loc_id;

Tandai untuk Ditinjau

(1) Point

The declaration is invalid. You cannot reference a cursor parameter in a WHERE clause.

A parameter is missing. The parameter should be coded as: (p_loc_id NUMBER) (*)

A parameter is missing. The parameter should be coded as: (p_loc_id IN NUMBER)

Nothing is wrong; the cursor declaration is correct.

Correct

---

4. A cursor has been declared as:
CURSOR c_curs (p_param VARCHAR2) IS
   SELECT * FROM mytable
   WHERE mycolumn = p_param;
Which of the following will open the cursor successfully?

Tandai untuk Ditinjau

(1) Point

OPEN c_curs(p_param = "ABC");

OPEN c_curs('ABC'); (*)

OPEN c_curs USING ("ABC");

p_param := 'ABC';
OPEN c_curs(p_param);

Correct

---

5. Which statement correctly places the employee id and last name into the stated variables?

DECLARE
  CURSOR emp_cursor IS
  SELECT employee_id, last_name FROM employees
  WHERE department_id = 30;
  v_empno employees.employee_id%TYPE;
  v_lname employees.last_name%TYPE;
BEGIN
  OPEN emp_cursor;
-- Point A
...

Tandai untuk Ditinjau

(1) Point

GET emp_cursor INTO v_empno, v_lname;

GET emp_cursor.employee_id, emp_cursor.last_name INTO v_empno, v_lname;

FETCH emp_cursor.employee_id, emp_cursor.last_name INTO v_empno, v_lname;

FETCH emp_cursor INTO v_empno, v_lname; (*)

6. You must make sure you have the same number of variables in your INTO statement as you have in your SELECT list. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

7. You cannot OPEN or CLOSE an implicit cursor. Why not?

Tandai untuk Ditinjau

(1) Point

Because an implicit cursor is OPENed and CLOSEd automatically by Oracle. (*)

Because an implicit cursor is always called SQL.

Correct

---

8. Place the following statements in the correct sequence:

1. OPEN my_curs;
2. CLOSE my_curs;
3. CURSOR my_curs IS SELECT my_column FROM my_table;
4. FETCH my_curs INTO my_variable;

Tandai untuk Ditinjau

(1) Point

C,D,A,B

C,A,D,B (*)

C,A,B,D

A,C,D,B

Correct

---

9. Which of the following is NOT allowed when using multiple cursors with parameters?

Tandai untuk Ditinjau

(1) Point

Declaring a cursor FOR UPDATE

Declaring a cursor based on a join

OPENing more than one cursor at the same time

Using cursor FOR loops

None of these--they are all allowed. (*)

Incorrect. Refer to Section 5 Lesson 6.

---

10. You want to display each row from the DEPARTMENTS table, and immediately underneath it, a list of all EMPLOYEES in that department. Which of the following is a good way to do this?

Tandai untuk Ditinjau

(1) Point

Use a single cursor, declared as SELECT * FROM employees GROUP BY department_id;

Use two cursors, one for each of the two tables. Declare the EMPLOYEES cursor with a parameter for the DEPARTMENT_ID. (*)

Use a single cursor with a cursor FOR loop.

Write a SELECT statement which JOINs the two tables, and use CONNECT BY PRIOR and LEVEL to display the rows in the correct order.

Change the physical model so that all employee and department data is in a single table.

11. You want to fetch rows from the EMPLOYEES table. You want to lock the fetched rows to prevent other users from updating them. You declare the following cursor: CURSOR emp_curs IS
   SELECT employee_id, last_name, salary
   FROM employees
   -- Line A -- ;  What should you code at Line A?

Tandai untuk Ditinjau

(1) Point

FOR UPDATE OF employees

FOR UPDATE (employees)

FOR UPDATE (*)

FOR LOCK

Incorrect. Refer to Section 5 Lesson 5.

---

12. User MARY has locked a row of the EMPLOYEES table. Now, user SAEED tries to open the following cursor:

CURSOR c IS
  SELECT * FROM employees
  FOR UPDATE WAIT 5;

What will happen when SAEED's session tries to fetch the row that MARY has locked?

Tandai untuk Ditinjau

(1) Point

SAEED's session waits for 5 minutes, and then raises an exception if MARY has not unlocked the row.

SAEED's session waits for 5 seconds, and then raises an exception if MARY has not unlocked the row. (*)

SAEED's session waits for 5 seconds, then SAEED is disconnected from the database.

SAEED's session successfully fetches the first 5 rows and then waits indefinitely to fetch the 6th row.

SAEED's session waits for 5 seconds, then MARY's session is rolled back.

Correct

---

13. You declare a cursor as a join of two tables: CURSOR emp_dept_curs IS
   SELECT last_name, salary, department_name
   FROM employees e, departments d
   WHERE e.department_id = d.department_id
   -- Point A -- ; You want to lock fetched rows from EMPLOYEES, but NOT lock fetched rows from DEPARTMENTS. Which of the following is correct at Point A?

Tandai untuk Ditinjau

(1) Point

FOR UPDATE (last_name)

FOR UPDATE

FOR UPDATE OF employees

FOR UPDATE of salary (*)

Incorrect. Refer to Section 5 Lesson 5.

---

14. What is the DISadvantage of using a cursor FOR loop with a subquery?

Tandai untuk Ditinjau

(1) Point

You cannot use the cursor to join two or more tables.

The execution speed is slower.

You cannot declare the cursor in the declaration section.

There are no disadvantages.

You cannot reference cursor attributes such as %NOTFOUND. (*)

Incorrect. Refer to Section 5 Lesson 3.

---

15. What is wrong with the following piece of code?

BEGIN
  FOR emp_record IN emp_cursor LOOP
   DBMS_OUTPUT.PUT_LINE(emp_record.last_name);
  END LOOP;
  IF emp_record.last_name = 'Patel' THEN ...

Tandai untuk Ditinjau

(1) Point

Nothing is wrong; the code will execute correctly.

You cannot reference EMP_RECORD outside the loop. (*)

EMP_RECORD has not been explicitly declared.

It should read: DBMS_OUTPUT.PUT_LINE(emp_cursor.last_name);

The cursor has not been OPENed.

Oktober 14, 2020

 1. You cannot OPEN or CLOSE an implicit cursor. Why not?

Tandai untuk Ditinjau

(1) Point

Because an implicit cursor is OPENed and CLOSEd automatically by Oracle. (*)

Because an implicit cursor is always called SQL.

Correct

---

2. When must you declare and use an explicit cursor?

Tandai untuk Ditinjau

(1) Point

You want to be able to ROLLBACK a transaction if needed.

You need to UPDATE more than one row in a table.

You need to SELECT more than one row from a table. (*)

You want to use a MERGE statement.

Correct

---

3. Which of these statements about implicit cursors is NOT true?

Tandai untuk Ditinjau

(1) Point

They are declared automatically by Oracle for all DML statements.

They are opened and closed automatically by Oracle.

They are declared by the PL/SQL programmer. (*)

They are declared automatically by Oracle for single-row SELECT statements.

Correct

---

4. An implicit cursor can be used for a multiple-row SELECT statement. True or False?

Tandai untuk Ditinjau

(1) Point

TRUE

FALSE (*)

Correct

---

5. You have declared the following cursor: CURSOR country_curs IS
   SELECT country_id, country_name
   FROM wf_countries
   FOR UPDATE WAIT 10;  Another user updates a row in WF_COUNTRIES but does not COMMIT the update. What will happen when you OPEN country_curs; ?

Tandai untuk Ditinjau

(1) Point

The other user's transaction is automatically rolled back.

Your session waits for 10 seconds, and then returns control to your block so that it can continue to execute. (*)

A LOCKED_ROWS exception is raised immediately.

Your session waits indefinitely until the other user COMMITs.

Your block fails because you should have coded: FOR UPDATE WAIT (10);

6. You want to fetch rows from the EMPLOYEES table. You want to lock the fetched rows to prevent other users from updating them. You declare the following cursor: CURSOR emp_curs IS
   SELECT employee_id, last_name, salary
   FROM employees
   -- Line A -- ;  What should you code at Line A?

Tandai untuk Ditinjau

(1) Point

FOR LOCK

FOR UPDATE (employees)

FOR UPDATE (*)

FOR UPDATE OF employees

Correct

---

7. You declare a cursor as a join of two tables: CURSOR emp_dept_curs IS
   SELECT last_name, salary, department_name
   FROM employees e, departments d
   WHERE e.department_id = d.department_id
   -- Point A -- ; You want to lock fetched rows from EMPLOYEES, but NOT lock fetched rows from DEPARTMENTS. Which of the following is correct at Point A?

Tandai untuk Ditinjau

(1) Point

FOR UPDATE (last_name)

FOR UPDATE of salary (*)

FOR UPDATE OF employees

FOR UPDATE

Correct

---

8. The following cursor has been declared:

    CURSOR emp_curs IS
       SELECT first_name, last_name, job_id, salary
       FROM employees;

Which of the following correctly declares a composite record with the same structure as the cursor?

Tandai untuk Ditinjau

(1) Point

emp_rec emp_curs%TYPE;

emp_rec cursor%ROWTYPE;

emp_rec emp_rec%ROWTYPE;

emp_rec emp_curs%ROWTYPE; (*)

Correct

---

9. Which of the following statements about the %ISOPEN cursor attribute is true?

Tandai untuk Ditinjau

(1) Point

If a cursor is open, then the value of %ISOPEN is false.

You can issue the %ISOPEN cursor attribute when a cursor is open or closed. (*)

You can issue the %ISOPEN cursor attribute only when a cursor is open.

You can issue the %ISOPEN cursor attribute only when more than one record is returned.

Correct

---

10. Which one of the following is a valid cursor FOR loop with a subquery?

Tandai untuk Ditinjau

(1) Point

FOR emp_rec IN (SELECT last_name |and first_name FROM employees) LOOP ...

FOR emp_rec IN SELECT last_name, salary*12 "ANNSAL" FROM employees LOOP ...

None of these.

FOR emp_rec IN (SELECT last_name, salary*12 "ANNSAL" FROM employees) LOOP ... (*)

11. Look at the following code:

DECLARE
  CURSOR emp_cursor IS SELECT * FROM employees;
BEGIN
  FOR emp_record IN emp_cursor LOOP
   DBMS_OUTPUT.PUT_LINE( --Point A -- );
  END LOOP;
END;

To display the salary of an employee, what code should you write at Point A?

Tandai untuk Ditinjau

(1) Point

emp_record.salary (*)

employees.salary

emp_cursor.salary

emp_record.employees.salary

TO_CHAR(salary)

Incorrect. Refer to Section 5 Lesson 3.

---

12. Using parameters with a cursor, you can open and close the cursor several times in a block, returning a different active set each time. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

13. You want to use explicit cursors to fetch and display all the countries in a specific region. There are 19 rows in the WF_WORLD_REGIONS table. You want to use a different region each time the cursor is opened. How many cursors should you declare?

Tandai untuk Ditinjau

(1) Point

20 cursors, in case an extra row is inserted into WF_WORLD_REGIONS later.

19 cursors in 19 PL/SQL blocks (one in each block).

One cursor with a parameter in the WHERE clause. (*)

19 cursors, all in the same PL/SQL block.

None of the these.

Correct

---

14. You want to produce a report which displays each department and (immediately after each department) a list of employees who work in that department. You declare a DEPARTMENTS cursor as:

CURSOR dept_curs IS
  SELECT * FROM departments
  ORDER BY department_id;

How could you declare the EMPLOYEES cursor? (Choose two).

Tandai untuk Ditinjau

(1) Point

CURSOR emp_curs IS SELECT * FROM employees;

CURSOR emp_curs (p_dept_id NUMBER) IS SELECT * FROM employees WHERE department_id = p_dept_id;(*)

CURSOR emp_curs IS SELECT * FROM employees ORDER BY department_id;

CURSOR emp_curs IS SELECT * FROM employees WHERE department_id = departments.department_id;

CURSOR emp_curs (p_dept_id departments.department_id%TYPE) IS SELECT * FROM employees WHERE department_id = p_dept_id;(*)

Incorrect. Refer to Section 5 Lesson 6.

---

15. How many explicit cursors can be declared and used in a single PL/SQL block?

Tandai untuk Ditinjau

(1) Point

Up to eight cursors

As many as needed (*)