Test: Section 10 Quiz Review your answers, feedback, and question scores below. A n asterisk (*) indicates a correct co rrect answer.
Section 10 Quiz (Answer all questions in this section)
1.
Every subprogram which has been declared in a package specification must
also be included in the package body. Triue or False?
Mark for Review
(1) Points
True (*)
False
Correct
Correct
2.
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; Mark for Review (1) Points
The keyword PACKAGE is missing. (*)
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.
You cannot declare constants in the specification.
Incorrect
Incorrect. Refer to Section 10 Lesson 1.
END mypack; Mark for Review (1) Points
The keyword PACKAGE is missing. (*)
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.
You cannot declare constants in the specification.
Incorrect
Incorrect. Refer to Section 10 Lesson 1.
3.
Package Specification DEPT_PACK was created by the following code:
CREATE OR REPLACE PACKAGE dept_pack IS PROCEDURE ins_dept(p_deptno IN NUMBER); FUNCTION get_dept(p_deptno IN NUMBER) RETURN VARCHAR2; END dept_pack;
Which of the following are correct syntax for invoking the pac kage subprograms? (Choose two.)
Mark for Review (1) Points
(Choose all correct answers)
CREATE PROCEDURE dept_proc IS v_deptname VARCHAR2(20); BEGIN v_deptname := dept_pack.get_dept(40); END; (*)
BEGIN dept_pack.get_dept(20); END;
BEGIN dept_pack.ins_dept(20); END; (*)
DECLARE v_deptname VARCHAR2(20); BEGIN v_deptname := get_dept(50); END;
BEGIN dept_pack(30); END;
Incorrect
Incorrect. Refer to Section 10 Lesson 1.
4.
The following package specification has been created:
CREATE OR REPLACE PACKAGE mypack IS FUNCTION myfunc(p_funcparam DATE) RETURN BOOLEAN;
PROCEDURE myproc(p_procparam IN NUMBER); END mypack;
Which of the following will correctly invoke the package subprograms? (Choose two.)
Mark for Review (1) Points
(Choose all correct answers)
v_num := mypack.myproc(22);
IF NOT mypack.myfunc(SYSDATE) THEN DBMS_OUTPUT.PUT_LINE('Message'); END IF; (*)
myproc(40);
mypack.myproc(35); (*)
mypack.myfunc('22-Jan-2007');
Incorrect
Incorrect. Refer to Section 10 Lesson 1.
5. written?
In which component of a package is the full definition of a public procedure
Mark for Review
(1) Points
Both the body and the specification
Body (*)
Neither the body nor the specification
Specification
Incorrect
Incorrect. Refer to Section 10 Lesson 1.
Page 1 of 3
Next
Summary
Test: Section 10 Quiz Review your answers, feedback, and question scores below. A n asterisk (*) indicates a correct answer.
Section 10 Quiz (Answer all questions in this section)
6.
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
Correct
7.
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? Mark for Review (1) Points
True (*)
False
Correct
Correct
8.
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 BY BINARY
INDEX ALL
Correct
Correct
9.
The following package is valid. True or False?
CREATE OR REPLACE PACKAGE exceptions_pkg IS e_cons_violation EXCEPTION; PRAGMA EXCEPTION_INIT (e_cons_violation, -2292); e_value_too_large EXCEPTION; PRAGMA EXCEPTION_INIT (e_value_too_large, -1438); END exceptions_pkg;
Mark for Review (1) Points
True (*)
False
Correct
Correct
10. three)
Which of the following are not allowed in a bodiless package? (Choose
Mark for Review
(1) Points
(Choose all correct answers)
User-defined exceptions
Global variables
Private variables (*)
DML statements (*)
Subprograms (*)
Incorrect
Previous
Incorrect. Refer to Section 10 Lesson 3.
Page 2 of 3
Next
Summary
Test: Section 10 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 10 Quiz (Answer all questions in this section)
11.
A local variable defined inside a package procedure is visible to the calling
environment. True or False? (1) Points
True
False (*)
Mark for Review
Incorrect
Incorrect. Refer to Section 10 Lesson 2.
12.
When one component of a package is called, all the package's components
are loaded into memory. True or False? Mark for Review (1) Points
True (*)
False
Correct
Correct
13.
A package contains both public and private subprograms. Which one of the
following statements is true?
Mark for Review
(1) Points
If three users invoke three different subprograms in the package, there will be three copies of the code in memory.
The whole package is loaded into memory when the first call is made to any subprogram in the package. (*)
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.
Each subprogram is loaded into memory when it is first invoked.
Incorrect
Incorrect. Refer to Section 10 Lesson 2.
14.
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 (*)
Incorrect
Incorrect. Refer to Section 10 Lesson 2.
15.
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 (0) Points
emp_sal(101);
None of these.
All of these.
scott.emp_pkg.emp_sal(101): (*)
emp_pkg.emp_sal(101);
Correct
Correct
Previous
Page 3 of 3
Summary
Test: Section 10 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 10 Quiz (Answer all questions in this section)
1.
To be able to invoke a package subprogram from outside the package, it
must be declared in the package: (1) Points
None of these.
Specification
Body and the specification (*)
Body
Mark for Review
Correct
Correct
2.
The following package specification has been created:
CREATE OR REPLACE PACKAGE mypack IS FUNCTION myfunc(p_funcparam DATE) RETURN BOOLEAN; PROCEDURE myproc(p_procparam IN NUMBER); END mypack;
Which of the following will correctly invoke the package subprograms? (Choose two.)
Mark for Review (1) Points
(Choose all correct answers)
myproc(40);
IF NOT mypack.myfunc(SYSDATE) THEN DBMS_OUTPUT.PUT_LINE('Message'); END IF; (*)
mypack.myproc(35);
(*)
v_num := mypack.myproc(22);
mypack.myfunc('22-Jan-2007');
Incorrect
Incorrect. Refer to Section 10 Lesson 1.
3. (1) Points
All of these. (*)
procedures
variables
Which of the following can be included in a package?
Mark for Review
Exceptions
PL/SQL types
Incorrect
Incorrect. Refer to Section 10 Lesson 1.
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? Review (1) Points
DESCRIBE emp_pack(del_emp, show_emp)
DESCRIBE del_emp DESCRIBE show_emp
DESCRIBE emp_pack.del_emp DESCRIBE emp_pack.show_emp
Mark for
None of these.
DESCRIBE emp_pack (*)
Incorrect
Incorrect. Refer to Section 10 Lesson 1.
5.
A number variable declared in a package is initialized to NULL unless
assigned another value. True or False? (1) Points
True (*)
False
Correct
Correct
Page 1 of 3
Next
Summary
Mark for Review
Test: Section 10 Quiz Review your answers, feedback, and question scores below. A n asterisk (*) indicates a correct answer.
Section 10 Quiz (Answer all questions in this section)
6.
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 (*)
forward declaration
None of these.
bodiless package
Incorrect
Incorrect. Refer to Section 10 Lesson 3.
7.
The following example package specification is valid to create a data type
ed_type that can be used in other subprograms. True or False? CREATE OR REPLACE PACKAGE emp_dept_pkg IS TYPE ed_type IS RECORD (f_name employees.first_name%TYPE, l_name employees.last_name%TYPE, d_name departments.department_name%TYPE); PROCEDURE sel_emp_dept (p_emp_id IN employees.employee_id%TYPE, p_emp_dept_rec OUT ed_type); END emp_dept_pkg;
Mark for Review (1) Points
True (*)
False
Correct
Correct
8.
What is the correct format to declare a variable using the following
emp_pkg package composite data type? TYPE emprec_type IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
Mark for Review
(1) Points
v_emp_table emprec_type.emp_pkg;
None of these.
emprec_type.emp_pkg;
emp_pkg.emprec_type;
v_emp_table emp_pkg.emprec_type; (*)
Incorrect
Incorrect. Refer to Section 10 Lesson 3.
9.
How would you invoke the constant km_to_mile from the global_consts
bodiless package at VARIABLE A?
SELECT trail_name, distance_in_km * VARIABLE A FROM trails WHERE park_name = 'YOSEMITE'; Mark for Review (1) Points
global_consts.km_to_mile (*)
km_to_mile.global_consts
global_consts (km_to_mile)
km_to_mile (global_consts)
Incorrect
Incorrect. Refer to Section 10 Lesson 3.
10.
When using a package function in DML statements, which rules must you
follow? (Choose three) Mark for Review (1) Points
(Choose all correct answers)
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 (*)
Can read or modify the table being changed by that DML statement
Incorrect
Previous
Incorrect. Refer to Section 10 Lesson 3.
Page 2 of 3
Next
Summary
Test: Section 10 Quiz Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 10 Quiz (Answer all questions in this section)
11.
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
Correct
12.
When one component of a package is called, all the package's components
are loaded into memory. True or False? Mark for Review (1) Points
True (*)
False
Correct
Correct
13.
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 (0) Points
All of these.
emp_sal(101);
None of these.
scott.emp_pkg.emp_sal(101): (*)
emp_pkg.emp_sal(101);
Correct
Correct
14.
Your schema contains a package called EMP_PKG. You want to remove the
package body but not the specification. The correct syntax to do this is: DROP BODY emp_pkg; True or False?
Mark for Review
(1) Points
True
False (*)
Incorrect
Incorrect. Refer to Section 10 Lesson 2.
15.
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 (*)
Incorrect
Incorrect. Refer to Section 10 Lesson 2.
Previous
Page 3 of 3
Summary
Test: Section 10 Quiz Review your answers, feedback, and question scores below. A n asterisk (*) indicates a correct answer.
Section 10 Quiz (Answer all questions in this section)
1.
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
None of these.
forward declaration
package initialization block (*)
bodiless package
Correct
Correct
2.
Which two of these declarations cannot be in the same package
specification? PROCEDURE myproc (p1 NUMBER, p2 VARCHAR2); PROCEDURE myproc (p1 VARCHAR2, p2 NUMBER); PROCEDURE myproc (p1 NUMBER, p2 CHAR); PROCEDURE myproc (p1 NUMBER); Mark for Review (1) Points
1 and 4
3 and 4
1 and 3 (*)
2 and 3
1 and 2
Correct
Correct
3.
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(pf_salary NUMBER);
PROCEDURE upd_emp (p_empno IN NUMBER, p_salary IN NUMBER);
Nothing is needed at Line A
FUNCTION sal_ok;
Correct
Correct
4.
A package initialization block is executed automatically every time a user
invokes any procedure or function in the package. True or False? (1) Points
Mark for Review
True
False (*)
Correct
Correct
5.
Which of the following best describes a package initialization block?
Mark for Review (1) Points
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.
It is a private function within the package body.
It is an anonymous block in the package specification.
Because it is an anonymous block, it cannot be invoked and therefore will never execute. It is treated as a set of comments.
Correct
Correct
Page 1 of 3
Next
Summary
Test: Section 10 Quiz Review your answers, feedback, and question scores below. A n asterisk (*) indicates a correct answer.
Section 10 Quiz (Answer all questions in this section)
6.
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? Review (1) Points
None of these.
Mark for
DESCRIBE emp_pack (*)
DESCRIBE emp_pack(del_emp, show_emp)
DESCRIBE emp_pack.del_emp DESCRIBE emp_pack.show_emp
DESCRIBE del_emp DESCRIBE show_emp
Correct
Correct
7.
Which of the following statements about packages is NOT true ? Mark for
Review (1) Points
The body contains the detailed code of the subprograms.
The specification must be created before the body.
Cursors can be declared in the specification.
All procedures and functions must be declared in the specification. (*)
Variables can be declared in the body.
Correct
Correct
8.
Package Specification DEPT_PACK was created by the following code:
CREATE OR REPLACE PACKAGE dept_pack IS PROCEDURE ins_dept(p_deptno IN NUMBER); FUNCTION get_dept(p_deptno IN NUMBER) RETURN VARCHAR2; END dept_pack;
Which of the following are correct syntax for invoking the pac kage subprograms? (Choose two.)
Mark for Review
(1) Points
(Choose all correct answers)
DECLARE v_deptname VARCHAR2(20); BEGIN v_deptname := get_dept(50); END;
BEGIN dept_pack.get_dept(20); END;
CREATE PROCEDURE dept_proc IS v_deptname VARCHAR2(20); BEGIN v_deptname := dept_pack.get_dept(40); END; (*)
BEGIN
dept_pack(30); END;
BEGIN dept_pack.ins_dept(20); END; (*)
Correct
Correct
9.
To be able to invoke a package subprogram from outside the package, it
must be declared in the package: (1) Points
Specification
Body and the specification (*)
Body
Mark for Review
None of these.
Correct
Correct
10. True or False?
The two parts of a package are stored as separate objects in the database.
Mark for Review
(1) Points
True (*)
False
Correct
Correct
Previous
Page 2 of 3
Test: Section 10 Quiz
Next
Summary
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.
Section 10 Quiz (Answer all questions in this section)
11.
A package contains both public and private subprograms. Which one of the
following statements is true?
Mark for Review
(1) Points
The whole package is loaded into memory when the first call is made to any subprogram in the package. (*)
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.
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
Correct
12.
Your schema contains a package called EMP_PKG. You want to remove the
package body but not the specification. The correct syntax to do this is: DROP BODY emp_pkg; True or False?
Mark for Review
(1) Points
True
False (*)
Correct
Correct
13.
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 (0) Points
scott.emp_pkg.emp_sal(101): (*)
None of these.
emp_pkg.emp_sal(101);
All of these.
emp_sal(101);
Correct
Correct
14.
Your schema contains four packages, each having a specification and a body.
You have also been granted privileges to access three packages (and their bodies) in other users' schemas. What will be displayed by the following query? SELECT COUNT(*) FROM ALL_OBJECTS WHERE object_type LIKE 'PACK%' AND owner <> USER; Mark for Review (1) Points
6 (*)
14
7
3
0
Correct
Correct
15.
Package OLDPACK is in your schema. What will happen when the following
statement is executed? DROP PACKAGE oldpack;
Mark for Review (1) Points
The body will be dropped but the specification will be retained.
Both the specification and the body will be dropped. (*)
