ORACLE ACDEMY SEMISTER2

 TOTAL 50 QUESTIONS

Test: PL/SQL Database Programming Semester 1 Final Exam

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 5

(Answer all questions in this section)

1. Examine the following code. To display the salary of an employee, what must be coded at Point A?

DECLARE
    CURSOR emp_curs IS SELECT * FROM employees;
BEGIN
    FOR emp_rec IN emp_curs LOOP
       DBMS_OUTPUT.PUT_LINE( -- what goes here ? );
    END LOOP;
END;

Mark for Review

(1) Points

emp_rec.salary IN emp_curs

emp_curs.salary

salary

emp_rec.salary (*)

employees.salary

Correct

---

2. There are no employees in department_id 75.
Which of the following is NOT a valid cursor FOR loop with a subquery?

Mark for Review

(1) Points

FOR emp_rec IN
   (SELECT last_name, salary FROM employees) LOOP ...

None of these. They are all valid. (*)

FOR emp_rec IN
    (SELECT last_name, salary FROM employees ORDER BY last_name) LOOP ...

FOR emp_rec IN
    (SELECT * FROM employees) LOOP ...

FOR emp_rec IN
    (SELECT * FROM employees WHERE department_id = 75) LOOP ...

Correct

---

3. 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;

Mark for Review

(1) Points

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

The cursor DEPT_CURS has not been opened.

You cannot use %ROWCOUNT with a cursor FOR loop.

Nothing is wrong, this code will execute successfully.

The cursor DEPT_CURS has not been closed.

Correct

---

4. Examine the following code:

DECLARE
  CURSOR c IS SELECT * FROM employees FOR UPDATE;
  c_rec c%ROWTYPE;
BEGIN
  OPEN c;
  FOR i IN 1..20 LOOP
    FETCH c INTO c_rec;
     IF i = 6 THEN
     UPDATE employees SET first_name = 'Joe'
     WHERE CURRENT OF c;
    END IF;
  END LOOP;
  CLOSE c;
END;

Which employee row or rows will be updated when this block is executed?

Mark for Review

(1) Points

None of these.

The block will not compile because the cursor should have been declared .... FOR UPDATE WAIT 5;

The first 6 fetched rows will be updated.

No rows will be updated because you locked the rows when the cursor was opened.

The 6th fetched row will be updated. (*)

Correct

---

5. 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?

Mark for Review

(1) Points

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, and then raises an exception if MARY has not unlocked the row. (*)

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, then MARY's session is rolled back.

Correct

Test: PL/SQL Database Programming Semester 1 Final Exam

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 5

(Answer all questions in this section)

6. 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?

Mark for Review

(1) Points

True (*)

False

Correct

---

7. What is one of the advantages of using parameters with a cursor?

Mark for Review

(1) Points

You can use a single cursor to fetch a different set of rows each time the cursor is opened. (*)

You can use a cursor FOR loop.

You can declare the cursor FOR UPDATE.

You do not need to DECLARE the cursor at all.

It will execute much faster than a cursor without parameters.

Correct

---

8. There are 8 countries in REGION_ID 13 (Central America). What will happen when the following code is executed?

DECLARE
  CURSOR country_curs IS SELECT country_name FROM wf_countries
   WHERE region_id = 13;
  v_country_name wf_countries.country_name%TYPE;
BEGIN
  OPEN country_curs;
  WHILE country_curs%FOUND
  LOOP
   FETCH country_curs INTO v_country_name;
   DBMS_OUTPUT.PUT_LINE(v_country_name);
  END LOOP;
  CLOSE country_curs;
END;

Mark for Review

(1) Points

The block will execute, but no rows will be displayed. (*)

The block will fail because you can not use a WHILE loop with an explicit cursor.

Eight rows will be fetched and displayed successfully.

The last seven rows will be fetched and displayed.

None of the these.

Correct

---

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

Mark for Review

(1) Points

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

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

You can use ORDER BY when declaring an explicit cursor.

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

Correct

---

10. You have declared a cursor EMP_CURSOR to select many rows from the EMPLOYEES table. The following five statements will be in the executable section:

A  FETCH emp_cursor INTO v_empno,v_last_name;
B  OPEN emp_cursor;
C  END LOOP;
D  CLOSE emp_cursor;
E  LOOP

In which order should you code these statements?

Mark for Review

(1) Points

B, E, A, D, C

E, B, A, C, D

B, E, A, C, D (*)

B, A, E, D, C

Incorrect. Refer to Section 5 Lesson 1.

 Page 2 of 10  

Test: PL/SQL Database Programming Semester 1 Final Exam

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 5

(Answer all questions in this section)

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

Mark for Review

(1) Points

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

They are declared automatically by Oracle for all DML statements.

They are opened and closed automatically by Oracle.

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

Correct

---

12. You have declared the following cursor:

CURSOR country_curs IS
  SELECT * FROM wf_countries
  ORDER BY country_name;

There are over 200 rows in the WF_COUNTRIES table, but you want to fetch and display only the first 25 rows.

How would you exit from the FETCH loop?

Mark for Review

(1) Points

EXIT WHEN country_curs%ROWCOUNT > 25; (*)

EXIT WHEN country_curs%FOUND(25);

EXIT WHEN ROWCOUNT > 25;

WHEN country_curs > 25 THEN EXIT; END IF;

Correct

---

13. Look at the following code:

DECLARE
  CURSOR emp_cursor IS
   SELECT employee_id, last_name, salary FROM employees;
  v_empcurs emp_cursor%ROWTYPE;

What is the data type of V_EMPCURS?

Mark for Review

(1) Points

Scalar

Row

Cursor

Record (*)

Correct

---

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

Mark for Review

(1) Points

True

False (*)

Correct

---

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

Mark for Review

(1) Points

Declaring a cursor FOR UPDATE

OPENing more than one cursor at the same time

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

Declaring a cursor based on a join

Using cursor FOR loops

Incorrect. Refer to Section 5 Lesson 6.

 Page 3 of 10  

Test: PL/SQL Database Programming Semester 1 Final Exam

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 5

(Answer all questions in this section)

16. Assume that table BIGDEPTS contains 100 rows, and table BIGEMPS contains 1000 rows, with 10 employees in each department. Consider the following code:

DECLARE
  CURSOR bigdept_cur IS
   SELECT * FROM bigdepts;
  CURSOR bigemp_cur IS
   SELECT * FROM bigemps;
BEGIN
  FOR dept_rec IN bigdept_cur LOOP
   DBMS_OUTPUT.PUT_LINE (dept_rec.department_name);
   FOR emp_rec IN bigemp_cur LOOP
    IF emp_rec.department_id=dept_rec.department_id
     THEN DBMS_OUTPUT.PUT_LINE (emp_rec.last_name);
    END IF;
   END LOOP;
  END LOOP;
END;

Why is this code inefficient?

Mark for Review

(1) Points

It is using two cursors when one cursor is enough.

It reads 1000 employee rows every time BIGEMP_CUR is OPENed, and then ignores 990 of them. (*)

It locks both tables unnecessarily.

It is doing a Cartesian Product, joining every employee with every department and displaying 1100 lines of output.

It is using cursor FOR loops, which are less efficient than OPENing and CLOSEing the cursors explicitly.

Correct

Section 6

(Answer all questions in this section)

17. Which of these PL/SQL data structures can NOT store a collection?

Mark for Review

(1) Points

An INDEX BY table indexed by PLS_INTEGER

An INDEX BY table indexed by BINARY_INTEGER

An INDEX BY table of records

A PL/SQL record (*)

Incorrect. Refer to Section 6 Lesson 2.

---

18. The following code declares a PL/SQL record with the same structure as a row of the departments table. True or False?

DECLARE
    v_dept_rec departments%ROWTYPE;
...

Mark for Review

(1) Points

True (*)

False

Correct

Section 7

(Answer all questions in this section)

19. Using two nested blocks, a TOO_MANY_ROWS exception is raised within the inner block. Which of the following exception handlers will successfully handle the exception?

Mark for Review

(1) Points

WHEN OTHERS in either block

WHEN TOO_MANY_ROWS in either block

WHEN OTHERS in the inner block

All of these. (*)

WHEN TOO_MANY_ROWS in the inner block

Correct

---

20. What will be displayed when the following code is executed?

<< outer>>
DECLARE
    v_myvar NUMBER;
BEGIN
    v_myvar := 25;
    DECLARE
       v_myvar NUMBER := 100;
    BEGIN
       outer.v_myvar := 30;
       v_myvar := v_myvar / 0;
       outer.v_myvar := 35;
    END;
    v_myvar := 40;
EXCEPTION
    WHEN ZERO_DIVIDE THEN
       DBMS_OUTPUT.PUT_LINE(v_myvar);
END;

Mark for Review

(1) Points

25

30 (*)

100

40

35

Incorrect. Refer to Section 7 Lesson 4.

 Page 4 of 10  

Test: PL/SQL Database Programming Semester 1 Final Exam

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 7

(Answer all questions in this section)

21. The following line of code is correct. True or False?
RAISE_APPLICATION_ERROR(-21001,'My error message');

Mark for Review

(1) Points

True

False (*)

Correct

---

22. The following three steps must be performed to use a user-defined exception: - Raise the exception - Handle the exception - Declare the exception In what sequence must these steps be performed?

Mark for Review

(1) Points

The steps can be performed in any order.

Handle, Raise, Declare

Raise, Handle, Declare

Declare, Raise, Handle (*)

Correct

---

23. User-defined exceptions must be declared explicitly by the programmer, but then are raised automatically by the Oracle Server. True or False?

Mark for Review

(1) Points

TRUE

FALSE (*)

Correct

---

24. Which of the following EXCEPTION sections is constructed correctly? (Choose three.)

Mark for Review

(1) Points

EXCEPTION
    WHEN OTHERS THEN statement_1;
END;(*)

EXCEPTION
    WHEN NO_DATA_FOUND THEN statement_1;
    WHEN NO_DATA_FOUND THEN statement_2;
    WHEN OTHERS THEN statement_3;
END;

EXCEPTION
    WHEN OTHERS THEN statement_1;
    WHEN NO_DATA_FOUND THEN statement_2;
END;

EXCEPTION
    WHEN NO_DATA_FOUND THEN statement_1;
    WHEN OTHERS THEN statement_2;
END;(*)

EXCEPTION
    WHEN TOO_MANY_ROWS THEN statement_1;
END;(*)

Correct

---

25. Which of these exceptions can be handled by an EXCEPTION section in a PL/SQL block?

Mark for Review

(1) Points

All of these. (*)

Any other kind of exception that can occur within the block

A SELECT statement returns no rows

None of these.

An attempt is made to divide by zero

Correct

 Page 5 of 10  

Test: PL/SQL Database Programming Semester 1 Final Exam

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 7

(Answer all questions in this section)

26. Which of the following is NOT an advantage of including an exception handler in a PL/SQL block?

Mark for Review

(1) Points

Code is more readable because error-handling routines can be written in the same block in which the error occurred

Avoids costly and time-consuming correction of mistakes

Prevents errors from being propagated back to the calling environment

Prevents errors from occurring (*)

Incorrect. Refer to Section 7 Lesson 1.

---

27. Which of the following is NOT a predefined Oracle Server error?

Mark for Review

(1) Points

NO_DATA_FOUND

TOO_MANY_ROWS

DUP_VAL_ON_INDEX

ZERO_DIVIDE

e_sal_too_high EXCEPTION; (*)

Correct

---

28. An ORA-1400 exception is raised if an attempt is made to insert a null value into a NOT NULL column. DEPARTMENT_ID is the primary key of the DEPARTMENTS table. What will happen when the following code is executed?

DECLARE
    e_not_null EXCEPTION;
BEGIN
    PRAGMA EXCEPTION_INIT(e_not_null, -1400);
    INSERT INTO departments (department_id, department_name)
       VALUES(null, 'Marketing');
EXCEPTION
    WHEN e_not_null THEN
       DBMS_OUTPUT.PUT_LINE('Cannot be null');
END;

Mark for Review

(1) Points

The exception will be raised and "Cannot be null" will be displayed.

The code will not execute because the syntax of the INSERT statement is wrong.

The code will not execute because PRAGMA EXCEPTION_INIT must be coded in the DECLARE section. (*)

The code will not execute because the syntax of PRAGMA EXCEPTION_INIT is wrong.

Incorrect. Refer to Section 7 Lesson 2.

---

29. What is the correct syntax to associate an exception named EXCEPNAME with the non-predefined Oracle Server error ORA-02292?

Mark for Review

(1) Points

RAISE_APPLICATION_ERROR (-2292, excepname);

SQLCODE (-2292, excepname);

WHEN (-2292, excepname) THEN …

PRAGMA EXCEPTION_INIT (excepname, -2292) (*)

Correct

Section 8

(Answer all questions in this section)

30. Which one of the following statements about formal and actual parameters is true?

Mark for Review

(1) Points

An actual parameter is declared within the called procedure.

A formal parameter is declared within the called procedure, while an actual parameter is declared in the calling environment. (*)

Formal and actual parameters must have different names.

Formal and actual parameters must have the same name.

Correct

 Page 6 of 10  

Test: PL/SQL Database Programming Semester 1 Final Exam

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 8

(Answer all questions in this section)

31. What is the correct syntax to create procedure MYPROC that accepts two number parameters X and Y?

Mark for Review

(1) Points

CREATE PROCEDURE IS myproc (x NUMBER, y NUMBER) …

CREATE PROCEDURE (x NUMBER, y NUMBER) myproc IS ...

CREATE PROCEDURE myproc IS (x NUMBER, y NUMBER) ...

CREATE PROCEDURE myproc (x NUMBER, y NUMBER) IS ... (*)

Correct

---

32. Which of the following keywords MUST be included in every PL/SQL procedure definition? (Choose three.)

Mark for Review

(1) Points

END(*)

IS or AS(*)

DECLARE

REPLACE

BEGIN(*)

Incorrect. Refer to Section 8 Lesson 1.

---

33. A stored procedure add_dept may be invoked by the following command in Application Express. True or False?

BEGIN
  add_dept;
END;

Mark for Review

(1) Points

True (*)

False

Correct

---

34. A programmer wants to create a PL/SQL procedure named EMP_PROC. What will happen when the following code is executed?

CREATE OR REPLACE PROCEDURE emp_proc IS
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary FROM employees
       WHERE employee_id = 999;
    DBMS_OUTPUT.PUT_LINE('The salary is: ' || v_salary);
END;

Mark for Review

(1) Points

The statement will fail because the procedure does not have any parameters.

The statement will fail because you cannot declare variables such as v_salary inside a procedure.

The statement will raise a NO_DATA_FOUND exception because employee_id 999 does not exist.

The statement will fail because the last line of code should be END emp_proc;

The procedure will be created successfully. (*)

Correct

---

35. Which parameter mode is the default?

Mark for Review

(1) Points

VARIABLE

NUMBER

CONSTANT

OUT

IN (*)

Correct

 Page 7 of 10  

Test: PL/SQL Database Programming Semester 1 Final Exam

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 8

(Answer all questions in this section)

36. Which kind of parameters cannot have a DEFAULT value?

Mark for Review

(1) Points

CONSTANT

W(rite)

R(ead)

OUT (*)

IN

Correct

Section 9

(Answer all questions in this section)

37. The function avg_ann_sal returns the average annual salary for a particular department. The example below is a valid use of this function. True or False?

SELECT first_name, last_name
FROM employees
WHERE avg_ann_sal(20) > 15000;

Mark for Review

(1) Points

True (*)

False

Correct

---

38. Which of the following is NOT a benefit of user-defined functions?

Mark for Review

(1) Points

They can add business rules to the database and can be reused many times.

They can often be used inside SQL statements.

They can do the same job as built-in system functions such as UPPER and ROUND. (*)

They can be used in a WHERE clause to filter data.

Correct

---

39. Which of the following is a benefit of user-defined functions? (Choose 3)

Mark for Review

(1) Points

They can do the same job as built-in system functions such as UPPER and ROUND.

They can often be used inside SQL statements.(*)

They can add business rules to the database and can be reused many times.(*)

They can be used in a WHERE clause to filter data and thereby increase efficiency.(*)

Correct

---

40. Which statement is true regarding the following subprogram?

PROCEDURE at_proc IS
 PRAGMA AUTONOMOUS_TRANSACTION;
 dept_id NUMBER := 90;
 BEGIN
   UPDATE ...
   INSERT ...
 END at_proc;

Mark for Review

(1) Points

The subprogram's success is independent of the calling program. (*)

The subprogram's success depends on the calling program.

The subprogram will fail because the RETURN is not specified.

The subprogram cannot do a COMMIT.

Incorrect. Refer to Section 9 Lesson 6.

 Page 8 of 10  

Test: PL/SQL Database Programming Semester 1 Final Exam

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 9

(Answer all questions in this section)

41. User BOB creates procedure MYPROC using the default Definer's Rights. BOB then executes:
    GRANT EXECUTE ON bob.myproc TO ted;
When TED invokes BOB.MYPROC, whose privileges are checked?

Mark for Review

(1) Points

SYSTEM's privileges

BOB's privileges (*)

PUBLIC's privileges

ORACLE's privileges

TED's privileges

Correct

---

42. Your schema contains two procedures named CHILD1 and CHILD2. You now create a third procedure by executing:

CREATE OR REPLACE PROCEDURE parent IS
BEGIN
child1;
child2;
END;
You now want user JOE to be able to invoke PARENT. Which of the following gives JOE the privileges he needs, but no unnecessary privileges?

Mark for Review

(1) Points

GRANT EXECUTE ON parent TO joe; (*)

GRANT EXECUTE ON parent, child1, child2 TO joe;

GRANT EXECUTE ON parent TO joe;
GRANT EXECUTE ON child1 TO joe;
GRANT EXECUTE ON child2 TO joe;

GRANT EXECUTE ON parent TO joe WITH ADMIN OPTION;

GRANT EXECUTE ON * TO joe;

Correct

---

43. USERB creates a function called SEL_PROC which includes the statement:

SELECT ... FROM usera.employees ...;

USERC needs to execute UserB's procedure. What privileges are needed for this to work correctly? (Choose two.)

Mark for Review

(1) Points

UserA needs EXECUTE on userB.sel_proc

UserB needs SELECT on userA.employees(*)

UserC needs EXECUTE on Userb

UserC needs SELECT on userA.employees

UserC needs EXECUTE on userB.sel_proc(*)

Incorrect. Refer to Section 9 Lesson 5.

---

44. You want to display the names of all tables in your schema, but you have forgotten which Dictionary view to query. Which of the following will remind you of the name of the correct Dictionary view?

Mark for Review

(1) Points

HELP DICTIONARY

SELECT *
    FROM DICTIONARY
    WHERE table_name LIKE 'USER%TAB%'; (*)

SELECT *
    FROM USER_OBJECTS
    WHERE table_name LIKE '%TABLE%';

SELECT *
    FROM DICTIONARY
    WHERE table_name LIKE 'user%table%';

SELECT *
    FROM DICTIONARY
    WHERE table_name LIKE 'DBA%TABLE%';

Correct

---

45. What is one of the main purposes of the Data Dictionary?

Mark for Review

(1) Points

To translate data from one language to another

To provide a structured list of all objects in the database (*)

To prevent users from accidentally dropping tables

To ensure correct spelling of the values in VARCHAR2 table columns

To provide a list of all objects in your schema, but not in other users' schemas

Correct

 Page 9 of 10  

Test: PL/SQL Database Programming Semester 1 Final Exam

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 9

(Answer all questions in this section)

46. To create a function successfully,the first step is to test the code in an anonymous block.

Mark for Review

(1) Points

True

False (*)

Correct

---

47. To create a function successfully, the following steps should be performed.

A   Re-execute the code until it compiles correctly
B   Write the code containing the CREATE or REPLACE FUNCTION followed by the function code
C   Test the function from a SQL statement or an anonymous block
D   If the function fails to compile, correct the errors
E   Load the code into Application Express
F   Execute the code in Application Express

What is the correct order to perform these steps?

Mark for Review

(1) Points

A,B,E,F,D,C

B,C,E,F,D,A

D,B,E,F,A,C

B,E,F,D,A,C (*)

Correct

---

48. Function GET_JOB accepts an employee id as input and returns that employee's job id. Which of the following calls to the function will NOT work?

Mark for Review

(1) Points

v_job_id := get_job(100);

DBMS_OUTPUT.PUT_LINE(get_job(100));

get_job(100,v_job_id); (*)

IF get_job(100) = 'IT_PROG' THEN ...

Incorrect. Refer to Section 9 Lesson 1.

---

49. Which view would you query to see the detailed code of a procedure?

Mark for Review

(1) Points

user_dependencies

user_source (*)

user_errors

user_objects

user_procedures

Correct

---

50. You want to remove the procedure NO_NEED from your schema. You execute:
DROP PROCEDURE no_need;

Which Data Dictionary views are updated automatically?

Mark for Review

(1) Points

USER_OBJECTS

USER_PROCEDURES

All of the above (*)

USER_SOURCE

None of these

Incorrect. Refer to Section 9 Lesson 4.

 Page 10 of 10