1.

 Package OLDPACK is in your schema. What will happen when the

following statement is executed?
DROP PACKAGE oldpack;

Mark for Review

(1) Points
Both the specification and the body will be dropped. (*)
The body will be dropped but the specification will be retained.
The specification will be dropped but the body will be retained.
The statement will fail because you must drop the body before you can drop
the specification.
Correct

2. A local variable defined inside a package procedure is visible to the calling
environment. True or False?
Mark for Review

(1) Points
True
False (*)
Correct

3. A local variable declared within a procedure in a package can be

referenced by any other component of that package. True or False?
Mark for Review

(1) Points
True
False (*)
Correct

4. A public component declared in the package specification can be

referenced by a private component defined in the package body. True or
False?
Mark for Review

(1) Points
True (*)
False
Correct

5. SCOTT's schema contains a package EMP_PKG which contains a public

procedure EMP_SAL which accepts a NUMBER parameter. Which of the
following will invoke the procedure successfully?
Mark for Review

(1) Points
emp_pkg.emp_sal(101);
None of these.
scott.emp_pkg.emp_sal(101): (*)
emp_sal(101);
All of these.
Correct
6. We need to declare a package variable named MYVAR, which can be
referenced by any subprogram in the package but can NOT be referenced
from outside the package. In the following code, where should MYVAR be
declared?
CREATE OR REPLACE PACKAGE varpack IS
    -- Point A
...
END varpack;
CREATE OR REPLACE PACKAGE BODY varpack IS
    -- Point B
PROCEDURE varproc IS
    -- Point C
    BEGIN
       ...
    END varproc;
PROCEDURE ...
...
    -- Point D
END varpack;

Mark for Review

(1) Points
Point C
Point D
Point B (*)
Point B or Point C, they will both work
Point A
Correct

7. What will be displayed when a user executes the following statement?

SELECT object_name FROM user_objects
    WHERE object_type LIKE 'PACK%';

Mark for Review

(1) Points
The detailed code of all packages in the user's schema
The parameters which must be used when invoking all packaged
subprograms in the user's schema
The names of all package specifications and package bodies in the user's
schema (*)
The names of all package specifications in the user's schema
The names of all packages which can be invoked by the user
Correct

8. We want to remove the specification (but not the body) of package
BIGPACK from the database. Which of the following commands will do this?
Mark for Review

(1) Points
DROP PACKAGE HEADER bigpack;
DROP PACKAGE SPECIFICATION bigpack;
DROP PACKAGE bigpack SPECIFICATION;
None of these. (*)
DROP PACKAGE bigpack;
Correct

9. Package NEWPACK contains several procedures and functions, including

private function PRIVFUNC. From where can PRIVFUNC be invoked? (Choose
two.)
Mark for Review

(1) Points
From any function in NEWPACK (*)
From any procedure in NEWPACK (*)
From any private function in another package
From an anonymous block
From any public procedure in another package
Correct

10. When a change is made to the detailed code of a public procedure in a

package (but not to the procedure's name or parameters), both the
specification and the body must be recompiled. True or False?
Mark for Review

(1) Points
True
False (*)
Correct
Previous
11. Package TAXPACK declares a global variable G_TAXRATE NUMBER(2,2).
The value of the tax rate is stored in table TAXTAB in the database. You
want to read this value automatically into G_TAXRATE each time a user
session makes its first call to TAXPACK. How would you do this?
Mark for Review

(1) Points
Declare the global variable as:
g_taxrate NUMBER(2,2) := SELECT tax_rate FROM taxtab;
Create a database trigger that includes the following code:
SELECT tax_rate INTO taxpack.g_taxrate FROM taxtab;
Add a private function to the package body of TAXPACK, and invoke the
function from the user session.
Add a package initialization block to the package body of TAXPACK. (*)
Correct

12. Examine the following code:

CREATE OR REPLACE PACKAGE emppack IS
    PROCEDURE upd_emp (p_empno IN NUMBER, p_salary IN NUMBER);
END emppack;
CREATE OR REPLACE PACKAGE BODY emppack IS
    -- Line A
    PROCEDURE upd_emp (p_empno IN NUMBER, p_salary IN NUMBER) IS
       BEGIN
          IF NOT sal_ok(p_salary) THEN
             RAISE_APPLICATION_ERROR(-20201,'Invalid salary');
          END IF;
    END upd_emp;
    FUNCTION sal_ok(pf_salary NUMBER) RETURN BOOLEAN IS
       BEGIN
          IF pf_salary > 50000 THEN RETURN FALSE;
          ELSE RETURN TRUE;
          END IF;
    END sal_ok;
END emppack;

What must be coded at Line A for this package to compile successfully?

Mark for Review

(1) Points
FUNCTION sal_ok(pf_salary NUMBER) RETURN BOOLEAN; (*)
FUNCTION sal_ok;
FUNCTION sal_ok(pf_salary NUMBER);
Nothing is needed at Line A
PROCEDURE upd_emp (p_empno IN NUMBER, p_salary IN NUMBER);
Correct

13. Suppose you want to automatically execute some code every time you
make the first call to a package in your session? For example, you want to
automatically load a tax rate into a package variable.
Which of the following should you use?
Mark for Review

(1) Points
package initialization block (*)
None of these.
forward declaration
bodiless package
Correct

14. INDEX BY is missing from the emp_tab TYPE declaration. What is the

most efficient declaration?
CREATE OR REPLACE PACKAGE emp_pkg IS
 TYPE emp_tab IS TABLE OF employees%ROWTYPE;
 PROCEDURE get_employees(p_emp_table OUT emp_tab);
END emp_pkg;

Mark for Review

(1) Points
INDEX BY BINARY_INTEGER (*)
INDEX BY INTEGER
INDEX ALL
INDEX BY BINARY
Correct

15. How would you invoke the constant mile_to_km from the global_consts
bodiless package at VARIABLE A?
DECLARE
  distance_in_miles NUMBER(5) := 5000;
  distance_in_km NUMBER(6,2);
BEGIN
  distance_in_km :=
    distance_in_miles * VARIABLE A;
  DBMS_OUTPUT.PUT_LINE(distance_in_km);
END;

Mark for Review

(1) Points
global_consts.mile_to_km (*)
global_consts (mile_to_km)
mile_to_km (global_consts)
mile_to_km.global_consts
Correct
Previous
16. The following call to the function tax in the taxes_pkg package is invalid
for what reason?

SELECT taxes_pkg.tax(salary), salary, last_name

FROM   employees;
Mark for Review

(1) Points
The call to the function should be taxes_pkg.tax_salary.
The data type of tax does not match that of salary.
The call to the function should be taxes_pkg (tax.salary).
The call to the package is valid and will execute without error. (*)
Correct

17. A bodiless package contains what?

Mark for Review

(1) Points
Procedures only
Public variables only (*)
Private variables only
Functions only
Incorrect. Refer to Section 10 Lesson 3.

18. When using a package function in DML statements, which rules must you
follow? (Choose three)
Mark for Review

(1) Points
Can read or modify the table being changed by that DML statement
Cannot execute a DML statement or modify the database (*)
Changes to a package variable could have an impact on another stored
function (*)
Must not end the current transaction (*)
Correct

19. Which of the following best describes a package initialization block?

Mark for Review

(1) Points
It is a private function within the package body.
Because it is an anonymous block, it cannot be invoked and therefore will
never execute. It is treated as a set of comments.
It is an anonymous block in the package specification.
It is an anonymous block at the end of a package body which executes
automatically the first time each user session invokes a subprogram in the
package. (*)
It is a named procedure in a package which must be invoked by a user
before any other part of the package can be invoked.
Correct

20. The package name must be included when calling a package function

from a SELECT statement executed outside the package. True or False?
Mark for Review

(1) Points
True (*)
False
Correct
Previous
21. In which component of a package is the full definition of a public
procedure written?
Mark for Review

(1) Points
Neither the body nor the specification
Specification
Both the body and the specification
Body (*)
Correct

22. The two parts of a package are stored as separate objects in the

database. True or False?
Mark for Review

(1) Points
True (*)
False
Correct

23. Which of the following are good reasons to group a set of procedures

and functions into a package?
Mark for Review

(1) Points
Application developers do not need to know the details of the package body
code.
All of these. (*)
Related subprograms and variables can be grouped together for easier
management and maintenance.
If the detailed code is changed, applications which invoke the package do
not need to be recompiled.
Correct

24. A number variable declared in a package is initialized to NULL unless

assigned another value. True or False?
Mark for Review

(1) Points
True (*)
False
Incorrect. Refer to Section 10 Lesson 1.

25. Which part of a package must be created first, the specification or the

body?
Mark for Review

(1) Points
The specification and body must be created at the same time.
It does not matter which is created first.
The body
The body can be created first, but only if the package has no specification.

The specification (*)
Correct
Previous
26. Which of the following can be included in a package?
Mark for Review

(1) Points
Exceptions
procedures
PL/SQL types
All of these. (*)
variables
Correct

27. To be able to invoke a package subprogram from outside the package, it

must be declared in the package:
Mark for Review

(1) Points
Body
Specification
Body and the specification (*)
None of these.
Correct

28. Package MYPACK contains procedure MYPROC. You can see which

parameters MYPROC uses by executing: DESCRIBE mypack.myproc. True or
False?
Mark for Review

(1) Points
True
False (*)
Correct
Section 11
(Answer all questions in this section)
29. Why is it better to use DBMS_OUTPUT only in anonymous blocks, not
inside stored subprograms such as procedures?
Mark for Review

(1) Points
Because DBMS_OUTPUT cannot be used inside procedures
Because DBMS_OUTPUT can raise a NO_DATA_FOUND exception if used
inside a packaged procedure
Because DBMS_OUTPUT should be used only for testing and debugging
PL/SQL code (*)
Because anonymous blocks display messages while the block is executing,
while procedures do not display anything until their execution has finished
Correct
30. Which of the following best describes the purpose of the UTL_FILE
package?
Mark for Review

(1) Points
It is used to load binary files such as employees' photos into the database.

It is used to find out how much free space is left on an operating system
disk.
It is used to query CHAR and VARCHAR2 columns in tables.
It is used to read and write text files stored outside the database. (*)
Correct
Previous
31. The UTL_FILE package can be used to read and write binary files such as
JPEGs as well as text files. True or False?
Mark for Review

(1) Points
True
False (*)
Correct

32. DBMS_OUTPUT.PUT_LINE can be invoked from inside a private packaged

function. True or False?
Mark for Review

(1) Points
True (*)
False
Incorrect. Refer to Section 11 Lesson 2.

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

BEGIN
    DBMS_OUTPUT.PUT('I do like');
    DBMS_OUTPUT.PUT_LINE('to be');
    DBMS_OUTPUT.PUT('beside the seaside');
END;
Mark for Review

(1) Points
I do like to be
beside the seaside
I do like to be beside the seaside
I do like to be
I do like
to be
beside the seaside
I do liketo be (*)
Correct

34. Which of the following procedures is not valid for the UTL_MAIL package
Mark for Review

(1) Points
SEND
SEND_ATTACH_RAW
SEND_ATTACH_BOOLEAN (*)
All are valid.
SEND_ATTACH_VARCHAR2
Correct

35. Users A and B call the same procedure in a package to initialize a global

variable my_pkg.g_var. What will be the value of my_pkg.g_var for User A
at Point A?
User A: my_pkg.g_var is 10
User B: my_pkg.g_var is 10
User A: my_pkg.g_var is 50
User B: my_pkg.g_var is 25
Point A

Mark for Review

(1) Points
25
50 (*)
10
Correct
Previous
36. In the following example, which statement best fits in Line 1? (Choose 1)
DECLARE
 v_more_rows_exist BOOLEAN := TRUE;
BEGIN
 -- Line 1
 LOOP
   v_more_rows_exist := curs_pkg.fetch_n_rows(3);
  DBMS_OUTPUT.PUT_LINE('-------');
   EXIT WHEN NOT v_more_rows_exist;
 END LOOP;
 curs_pkg.close_curs;
END;

Mark for Review

(1) Points
curs_pkg.close_curs;
EXIT WHEN curs_pkg.emp_curs%NOTFOUND;
curs_pkg.emp_curs%ISOPEN;
curs_pkg.open_curs; (*)
Correct

37. A cursor is declared in a package specification. User SIOBHAN opens the

cursor and fetches the first three rows from the cursor's active set, but does
not close the cursor.
User FRED now connects to the database. FRED can immediately fetch the
next three rows without opening the cursor. True or False?
Mark for Review

(1) Points
True
False (*)
Correct

38. Package MULTIPACK declares the following global variable:

    g_myvar NUMBER;
User DICK executes the following:
    multipack.g_myvar := 45;

User HAZEL now connects to the database. Both users immediately execute:
BEGIN
    DBMS_OUTPUT.PUT_LINE(multipack.g_myvar);
END;

What values will Dick and Hazel see?

Mark for Review

(1) Points
Dick: 45, Hazel: 45
Dick: 45, Hazel: 0
Dick: 45, Hazel: null (*)
Dick: 0, Hazel: 0
Both queries will fail because the syntax of DBMS_OUTPUT.PUT_LINE is
incorrect
Correct
Section 12
(Answer all questions in this section)
39. Dynamic SQL enables data-definition, data-control, or session-control
statements to be written and executed from PL/SQL.
Mark for Review

(1) Points
True (*)
False
Correct

40. A SQL statement can pass through several stages. Which of the following
is NOT one of these stages?
Mark for Review

(1) Points
BIND
RETURN (*)
EXECUTE
PARSE
FETCH
Correct
Previous
41. The following procedure adds a column of datatype DATE to the
EMPLOYEES table. The name of the new column is passed to the procedure
as a parameter.
CREATE OR REPLACE PROCEDURE addcol
    (p_col_name IN VARCHAR2) IS
    v_first_string VARCHAR2(100) := 'ALTER TABLE EMPLOYEES ADD (';
    v_second_string VARCHAR2(6) := ' DATE)';
BEGIN
    ... Line A
END;

Which of the following will work correctly when coded at line A? (Choose
two.)

Mark for Review

(1) Points
v_first_string || p_col_name || v_second_string;
EXECUTE v_first_string || p_col_name || v_second_string;
EXECUTE IMMEDIATE v_first_string || p_col_name || v_second_string; (*)

v_first_string := v_first_string || p_col_name;

EXECUTE IMMEDIATE v_first_string || v_second_string; (*)
EXECUTE IMMEDIATE 'v_first_string' || p_col_name || 'v_second_string';
Correct

42. You want to create a function which drops a table. You write the
following code:
CREATE OR REPLACE FUNCTION droptab
    (p_tab_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
    DROP TABLE p_tab_name;
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
END;

Why will this procedure not compile successfully?

Mark for Review

(1) Points
Because you do not have the privilege needed to drop a table
Because the PL/SQL compiler cannot check if the argument of p_tab_name
is a valid table-name (*)
Because you cannot use RETURN in the exception section
Because you can never drop a table from inside a function
Correct

43. The DBMS_SQL package is easier to use than EXECUTE IMMEDIATE.

True or False?
Mark for Review

(1) Points
True
False (*)
Correct

44. When SQL statements are included within a procedure, the statements

are parsed when the procedure is compiled. True or False?
Mark for Review

(1) Points
True (*)
False
Correct

45. In the following example, where do you place the phrase BULK
COLLECT?
...
BEGIN
  SELECT -- Position A
     salary -- Position B
     INTO v_saltab -- Position C
     FROM employees WHERE department_id = 20 ORDER BY salary
     -- Position D
;
...

Mark for Review

(1) Points
Position A
Position B (*)
Position C
Position D
Correct
Previous
46. The following procedure compiles successfully. True or False?
CREATE OR REPLACE PACKAGE emp_pkg IS
  TYPE t_emp IS TABLE OF employees%ROWTYPE
    INDEX BY BINARY_INTEGER;
  PROCEDURE emp_proc
    (p_small_arg IN NUMBER, p_big_arg NOCOPY OUT t_emp);
...
END emp_pkg;

Mark for Review

(1) Points
True
False (*)
Correct

47. FORALL can only be used with the INSERT statement. True or False?
Mark for Review

(1) Points
True
False (*)
Correct

48. In the following example, where do you place the phrase BULK
COLLECT?
DECLARE
   TYPE NameList IS TABLE OF emp.ename%TYPE;
   names NameList;
   CURSOR c1 IS SELECT ename -- Position A
   FROM emp WHERE job = 'CLERK';
BEGIN
   OPEN c1;
   FETCH c1 -- Position B
INTO -- Position C
names;
   ...
   CLOSE c1;
END;

Mark for Review

(1) Points
Position A
Position B (*)
Position C
Correct

49. FORALL can be used with any DML statement. True or False?

Mark for Review

(1) Points
True (*)
False
Correct

50. What is wrong with this code example?

CREATE OR REPLACE PROCEDURE insert_emps IS
  TYPE t_emp IS TABLE OF employees%ROWTYPE INDEX BY
BINARY_INTEGER;
   v_emptab t_emp;
BEGIN
   FORALL i IN v_emptab.FIRST..v_emptab.LAST
     INSERT INTO employees VALUES v_emptab(i);
   END LOOP;
END insert_emps;

Mark for Review

(1) Points
Nothing is wrong; it will compile successfully.
FORALL does not require END LOOP. (*)
v_emptab is incorrectly typed.
The phrase should be FOR ALL.
Correct
Previous