Section 10:

1. What is wrong with the following syntax for creating a package specification?
CREATE OR REPLACE mypack IS
g_constant1 NUMBER(6) := 100;
PROCEDURE proc1 (p_param1 IN VARCHAR2);
PROCEDURE proc2;
END mypack;

A package must contain at least one function.

The first line should be:
CREATE OR REPLACE PACKAGE SPECIFICATION mypack IS
Nothing is wrong, this code contains no errors.
The keyword PACKAGE is missing. (*)
You cannot declare constants in the specification.
Correct
(1/1) Points
2. Package MYPACK contains procedure MYPROC. You can see which parameters
MYPROC uses by executing: DESCRIBE mypack.myproc. True or False?
True
False (*)
Correct
(1/1) Points
3. Which of the following statements about packages is NOT true ?
All procedures and functions must be declared in the specification. (*)
Cursors can be declared in the specification.
The specification must be created before the body.
Variables can be declared in the body.
The body contains the detailed code of the subprograms.
Correct
(1/1) Points
4. Package EMP_PACK contains two procedures, DEL_EMP and SHOW_EMP. You
want to write an anonymous block which invokes these procedures but you have
forgotten which parameters they use. Which of the following will give you this
information?
DESCRIBE emp_pack(del_emp, show_emp)
DESCRIBE emp_pack.del_emp
DESCRIBE emp_pack.show_emp
DESCRIBE emp_pack (*)
DESCRIBE del_emp
DESCRIBE show_emp
None of these.
Correct
(1/1) Points
5. Every subprogram which has been declared in a package specification must
also be included in the package body. Triue or False?
True (*)
False
Correct

6. A package contains both public and private subprograms. Which one of the
following statements is true?
The public subprograms are all loaded into memory at the same time, but the
private subprograms are loaded into memory one at a time as they are invoked.
The whole package is loaded into memory when the first call is made to any
subprogram in the package. (*)
Each subprogram is loaded into memory when it is first invoked.
If three users invoke three different subprograms in the package, there will be
three copies of the code in memory.
Correct
(1/1) Points
7. We want to remove both the specification and the body of package CO_PACK
from the database. Which of the following commands will do this?
None of these.
DROP BOTH co_pack;
DROP PACKAGE co_pack; (*)
DROP PACKAGE BODY co_pack;
DROP PACKAGE SPECIFICATION co_pack;
Correct
(1/1) Points
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?
None of these. (*)
DROP PACKAGE HEADER bigpack;
DROP PACKAGE bigpack;
DROP PACKAGE SPECIFICATION bigpack;
DROP PACKAGE bigpack SPECIFICATION;
Correct
(1/1) Points
9. Examine the following package specification:
CREATE OR REPLACE PACKAGE taxpack IS
CURSOR empcurs IS SELECT * FROM employees;
PROCEDURE taxproc;
END mypack;

The package body of TAXPACK also includes a function called TAXFUNC. Which
one of the following statements is NOT true?

TAXPROC is public and TAXFUNC is private.

TAXPROC can invoke TAXFUNC if TAXPROC is coded before TAXFUNC.
The package will not compile because you cannot declare a cursor in the
specification. (*)
TAXPROC can open the cursor.
The procedure can be invoked by:
BEGIN
taxpack.taxproc;
END;
Correct
(1/1) Points
10. A local variable declared within a procedure in a package can be referenced
by any other component of that package. True or False?
True
False (*)
Correct

11. The following example shows a valid record data type and variable. True or
False?
TYPE DeptRecTyp
IS RECORD (deptid NUMBER(4) NOT NULL := 99,
dname departments.department_name%TYPE,
loc departments.location_id%TYPE,
region regions.region_id%TYPE );
dept_rec DeptRecTyp;

True (*)
False
Correct
(1/1) Points
12. 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;
The call to the function should be taxes_pkg.tax_salary.
The call to the package is valid and will execute without error. (*)
The call to the function should be taxes_pkg (tax.salary).
The data type of tax does not match that of salary.
Correct
(1/1) Points
13. An package initialization block automatically executes once and is used to
initialize public and private package variables. True or False?
True (*)
False
Correct
(1/1) Points
14. Functions called from a SQL query or DML statement must not end the
current transaction, or create or roll back to a savepoint. True or False?
True (*)
False
Correct
(1/1) Points
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;

mile_to_km.global_consts
global_consts.mile_to_km (*)
global_consts (mile_to_km)
mile_to_km (global_consts)
Incorrect. Refer to Section 10 Lesson 3.
Section 11:

1. A package's state is initialized when the package is first loaded. True or False?
True (*)
False
Correct
(1/1) Points
2. 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;

curs_pkg.close_curs;
curs_pkg.emp_curs%ISOPEN;
curs_pkg.open_curs; (*)
EXIT WHEN curs_pkg.emp_curs%NOTFOUND;
Correct
(1/1) Points
3. 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?

Both queries will fail because the syntax of DBMS_OUTPUT.PUT_LINE is incorrect

Dick: 45, Hazel: null (*)
Dick: 45, Hazel: 0
Dick: 45, Hazel: 45
Dick: 0, Hazel: 0
Correct
(1/1) Points
4. Package CURSPACK declares a global cursor in the package specification. The
package contains three public procedures: OPENPROC opens the cursor;
FETCHPROC fetches 5 rows from the cursor's active set; CLOSEPROC closes the
cursor.
What will happen when a user session executes the following commands in the
order shown?
curspack.openproc; -- line 1
curspack.fetchproc; -- line 2
curspack.fetchproc; -- line 3
curspack.openproc; -- line 4
curspack.fetchproc; -- line 5
curspack.closeproc; -- line 6

The first 15 rows will be fetched.

An error will occur at line 2.
An error will occur at line 4. (*)
The first 5 rows will be fetched three times.
The first 10 rows will be fetched, then the first 5 rows will be fetched again.
Correct
(1/1) Points
5. A cursor's state is defined only by whether it is open or closed and, if open,
how many rows it holds. True or False?
True
False (*)
Correct

6. Which of the following procedures is not valid for the UTL_MAIL package
SEND
SEND_ATTACH_VARCHAR2
SEND_ATTACH_RAW
SEND_ATTACH_BOOLEAN (*)
All are valid.
Correct
(1/1) Points
7. The UTL_FILE package can be used to create binary files such as JPEGs as well
as text files. True or False?
True
False (*)
Incorrect. Refer to Section 11 Lesson 2.
(0/1) Points
8. Why is it better to use DBMS_OUTPUT only in anonymous blocks, not inside
stored subprograms such as procedures?
Because DBMS_OUTPUT cannot be used inside procedures
Because anonymous blocks display messages while the block is executing, while
procedures do not display anything until their execution has finished
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 (*)
Correct
(1/1) Points
9. 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;

I do liketo be (*)
I do like
to be
beside the seaside
I do like to be
beside the seaside
I do like to be beside the seaside
I do like to be
Correct
(1/1) Points
10. The UTL_FILE package can be used to read and write binary files such as
JPEGs as well as text files. True or False?
True
False (*)
Incorrect. Refer to Section 11 Lesson 2.
11. Which DBMS_OUTPUT package subprogram places text into the buffer at Line
1? (Choose one)

IF v_bool1 AND NOT v_bool2 AND v_number < 25 THEN

--Line 1
ELSE
...
END IF;
DBMS_OUTPUT.NEW_LINE;
DBMS_OUTPUT.PUT_LINE('IF branch was executed');
DBMS_OUTPUT.GET_LINE('IF branch was executed');
DBMS_OUTPUT.PUT('IF branch was executed'); (*)
DBMS_OUTPUT.NEW_LINE('IF branch was executed');
Correct
(1/1) Points
12. The DBMS_OUTPUT gives programmers an easy-to-use interface to see, for
instance, the current value of a loop counter, or whether or not a program
reaches a particular branch of an IF statement. (True or False?)
True (*)
False
Correct
(1/1) Points
13. Using the FOPEN function, you can do which actions with the UTL_FILE
package? (Choose 2)
(Choose all correct answers)
It is used to find out how much free space is left on an operating system disk.
It is used to manipulate large object data type items in columns.
It is used to append to a file until processing is complete. (*)
It is used to read and write text files stored outside the database. (*)
Correct
(1/1) Points
14. Which general exceptions may be handled by the UTL_FILE package? (Choose
2)
(Choose all correct answers)
NO_DATA_FOUND (*)
TOO_MANY_ROWS
VALUE_ERROR (*)
ZERO_DIVIDE
Incorrect. Refer to Section 11 Lesson 2.
(0/1) Points
15. The UTL_FILE package contains several exceptions exclusively used in this
package. Which are they? (Choose 3)
(Choose all correct answers)
INVALID_PATH (*)
NO_DATA_FOUND
INVALID_OPERATION (*)
WRITE_ERROR (*)
ZERO_DIVIDE
Correct

Section 12:

1. What will happen when the following procedure is invoked?

CREATE OR REPLACE PROCEDURE do_some_work IS
CURSOR c_curs IS SELECT object_name FROM user_objects
WHERE object_type = 'FUNCTION';
BEGIN
FOR v_curs_rec IN c_curs LOOP
EXECUTE IMMEDIATE 'ALTER FUNCTION ' || v_curs_rec.object_name || '
COMPILE';
EXIT WHEN c_curs%ROWCOUNT > 2;
END LOOP;
END;

The first two functions in the user's schema will be recompiled.

The first three functions in the user's schema will be recompiled. (*)
The procedure will not compile successfully because you cannot ALTER functions
using Dynamic SQL.
All functions in the user's schema will be recompiled.
The procedure will not compile successfully because the syntax of the ALTER
FUNCTION statement is incorrect.
Correct
(1/1) Points
2. 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?

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
Because you do not have the privilege needed to drop a table
Correct
(1/1) Points
3. 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.)

(Choose all correct answers)

EXECUTE 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; (*)
EXECUTE IMMEDIATE 'v_first_string' || p_col_name || 'v_second_string';
v_first_string || p_col_name || v_second_string;
Correct
(1/1) Points
4. Only one call to DBMS_SQL is needed in order to drop a table. True or False?
True
False (*)
Correct
(1/1) Points
5. When SQL statements are included within a procedure, the statements are
parsed when the procedure is compiled. True or False?
True (*)
False
Correct

6. For which of the following is it necessary to use Dynamic SQL? (Choose three.)
(Choose all correct answers)
ALTER (*)
UPDATE
DROP (*)
GRANT (*)
SAVEPOINT
Correct
(1/1) Points
7. Name two reasons for using Dynamic SQL.
(Choose all correct answers)
Provides the ability to execute SQL statements whose structure is unknown until
execution time. (*)
Allows fetch of data for DML statements.
Provides the ability to handle mutating rows when executing a statement
involving the same table.
Enables session-control statements to be written and executed from PL/SQL. (*)
Correct
(1/1) Points
8. What is the correct syntax to use the RETURNING phrase at Position A?
DECLARE
TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary
employees.salary%TYPE);
emp_info EmpRec;
emp_id NUMBER := 100;
BEGIN
UPDATE employees
SET salary = salary * 1.1 WHERE employee_id = emp_id
-- Position A
dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ', who
now makes ' || emp_info.salary);
END;

last_name, salary RETURNING INTO emp_info;

RETURNING last_name, salary INTO emp_info; (*)
RETURNING FROM emp_info;
RETURNING last_name, salary TO emp_info;
Correct
(1/1) Points
9. The following example code will compile successfully. True or False?
CREATE OR REPLACE PROCEDURE dept_proc IS
TYPE t_dept IS TABLE OF departments%ROWTYPE INDEX BY BINARY_INTEGER;
BEGIN
(p_small_arg IN NUMBER, p_big_arg OUT NOCOPY t_dept);
-- remaining code
END dept_proc;

True (*)
False
Incorrect. Refer to Section 12 Lesson 2.
(0/1) Points
10. 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;

Position A
Position B (*)
Position C
Correct

11. In the following example, where do you place the phrase DETERMINISTIC?
CREATE OR REPLACE FUNCTION total_sal
(p_dept_id IN -- Position A
employees.department_id%TYPE)
 RETURN NUMBER -- Position B
IS v_total_sal NUMBER;
BEGIN
SELECT SUM(salary) INTO v_total_sal
FROM employees WHERE department_id = p_dept_in;
RETURN v_total_sal -- Position C;
END total_sal;

Position A
Position B (*)
Position C
Correct
(1/1) Points
12. A function-based index may be made using your own functions, but only if
the function is created using the DETERMINISTIC clause. True or False?
True (*)
False
Correct
(1/1) Points
13. You want to take make a copy of all the cities in the world listed in the cities
table, which contains millions of rows. The following procedure accomplishes this
efficiently. True or False?
CREATE OR REPLACE PROCEDURE copy_cities IS
TYPE t_cities IS TABLE OF cities%ROWTYPE INDEX BY BINARY_INTEGER;
v_citiestab t_cities;
BEGIN
SELECT * BULK COLLECT INTO v_citiestab FROM cities;
FORALL i IN v_citiestab.FIRST..v_citiestab.LAST
INSERT INTO new_cities VALUES v_citiestab(i);
END copy_cities;

True (*)
False
Correct
(1/1) Points
14. Where would you place the BULK COLLECT statement in the following
example?
DECLARE
TYPE DeptRecTab IS TABLE OF departments%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT department_id, department_name, manager_id, location_id
-- Position A
FROM departments
WHERE department_id > 70;
BEGIN
OPEN c1
-- Position B;
FETCH c1
-- Position C
INTO dept_recs;
END;

Position A
Position B
Position C (*)
Correct
(1/1) Points
15. The following statement is a valid example of using the RETURNING clause.
True or False?
DECLARE
TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary
employees.salary%TYPE);
emp_info EmpRec;
emp_id NUMBER := 100;
BEGIN
UPDATE employees
SET salary = salary * 1.1 WHERE employee_id = emp_id;
RETURNING last_name, salary INTO emp_info;
dbms_output.put_line('Just gave a raise to ' || emp_info.last_name ||
', who now makes ' || emp_info.salary);
END;

True (*)
False
Incorrect. Refer to Section 12 Lesson 2.