PL/SQL Section 10 Quiz

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

Tandai untuk Ditinjau

(1) Point

FUNCTION sal_ok(pf_salary NUMBER);

FUNCTION sal_ok;

PROCEDURE upd_emp (p_empno IN NUMBER, p_salary IN NUMBER);

Nothing is needed at Line A

FUNCTION sal_ok(pf_salary NUMBER) RETURN BOOLEAN; (*)

Correct

---

2. Examine the following package code:

CREATE OR REPLACE PACKAGE ol_pack IS
    PROCEDURE subprog (p1 IN VARCHAR2, p2 IN NUMBER);
    PROCEDURE subprog (param1 IN CHAR, param2 IN NUMBER);
    FUNCTION subprog (param1 IN VARCHAR2, param2 IN NUMBER) RETURN DATE;
END ol_pack;

Which of the following calls will be successful? (Choose two.)

Tandai untuk Ditinjau

(1) Point

ol_pack.subprog('Jane',30);(*)

ol_pack.subprog(param1=>'Jane',param2=>30);

v_number := ol_pack.subprog(p1=>'Jane');

v_date := ol_pack.subprog('Jane',30);(*)

Incorrect. Refer to Section 10 Lesson 3.

---

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

Tandai untuk Ditinjau

(1) Point

The call to the function should be taxes_pkg.tax_salary.

The call to the function should be taxes_pkg (tax.salary).

The call to the package is valid and will execute without error. (*)

The data type of tax does not match that of salary.

Correct

---

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

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

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

Tandai untuk Ditinjau

(1) Point

INDEX BY BINARY

INDEX BY BINARY_INTEGER (*)

INDEX ALL

INDEX BY INTEGER

6. A public component declared in the package specification can be referenced by a private component defined in the package body. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

7. Examine the following package specification:

CREATE OR REPLACE PACKAGE mypack IS
    percent_tax NUMBER := 20;
    PROCEDURE proc1;
END mypack;

The package body of mypack also includes a function called func1. Which of the following statements are true? (Choose three.)

Tandai untuk Ditinjau

(1) Point

The function can be invoked from outside the package.

The procedure can be invoked by:
BEGIN
    mypack.proc1;
END;(*)

proc1 is a public procedure and func1 is a private function.(*)

The variable can be modified by:
BEGIN
    mypack.percent_tax := 10;
END;(*)

The package will not compile because you cannot declare variables in the specification, only procedures and functions. .

Correct

---

8. When one component of a package is called, all the package's components are loaded into memory. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

9. In a package, public components are declared in the specification but private components are not. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

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

Tandai untuk Ditinjau

(1) Point

6 (*)

7

0

3

14

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

Tandai untuk Ditinjau

(1) Point

DESCRIBE del_emp
DESCRIBE show_emp

DESCRIBE emp_pack (*)

None of these.

DESCRIBE emp_pack.del_emp
DESCRIBE emp_pack.show_emp

DESCRIBE emp_pack(del_emp, show_emp)

Correct

---

12. Which of the following are good reasons to group a set of procedures and functions into a package?

Tandai untuk Ditinjau

(1) Point

If the detailed code is changed, applications which invoke the package do not need to be recompiled.

Related subprograms and variables can be grouped together for easier management and maintenance.

Application developers do not need to know the details of the package body code.

All of these. (*)

Correct

---

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

Tandai untuk Ditinjau

(1) Point

The keyword PACKAGE is missing. (*)

You cannot declare constants in the specification.

The first line should be:
CREATE OR REPLACE PACKAGE SPECIFICATION mypack IS

A package must contain at least one function.

Nothing is wrong, this code contains no errors.

Incorrect. Refer to Section 10 Lesson 1.

---

14. In which component of a package is the full definition of a public procedure written?

Tandai untuk Ditinjau

(1) Point

Both the body and the specification

Body (*)

Neither the body nor the specification

Specification

Correct

---

15. Which of the following statements about packages is NOT true ?

Tandai untuk Ditinjau

(1) Point

Variables can be declared in the body.

The body contains the detailed code of the subprograms.

The specification must be created before the body.

All procedures and functions must be declared in the specification. (*)

Cursors can be declared in the specification.

1. Every subprogram which has been declared in a package specification must also be included in the package body. Triue or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

2. In which component of a package is the full definition of a public procedure written?

Tandai untuk Ditinjau

(1) Point

Body (*)

Both the body and the specification

Neither the body nor the specification

Specification

Correct

---

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

Tandai untuk Ditinjau

(1) Point

DESCRIBE del_emp
DESCRIBE show_emp

DESCRIBE emp_pack.del_emp
DESCRIBE emp_pack.show_emp

DESCRIBE emp_pack (*)

DESCRIBE emp_pack(del_emp, show_emp)

None of these.

Incorrect. Refer to Section 10 Lesson 1.

---

4. Package MYPACK contains procedure MYPROC. You can see which parameters MYPROC uses by executing: DESCRIBE mypack.myproc. True or False?

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 10 Lesson 1.

---

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

Tandai untuk Ditinjau

(1) Point

myproc(40);

v_num := mypack.myproc(22);

mypack.myfunc('22-Jan-2007');

mypack.myproc(35);(*)

IF NOT mypack.myfunc(SYSDATE) THEN
    DBMS_OUTPUT.PUT_LINE('Message');
END IF;(*)

6. Examine the following package specification:

CREATE OR REPLACE PACKAGE mypack IS
    percent_tax NUMBER := 20;
    PROCEDURE proc1;
END mypack;

The package body of mypack also includes a function called func1. Which of the following statements are true? (Choose three.)

Tandai untuk Ditinjau

(1) Point

The function can be invoked from outside the package.

The variable can be modified by:
BEGIN
    mypack.percent_tax := 10;
END;(*)

proc1 is a public procedure and func1 is a private function.(*)

The procedure can be invoked by:
BEGIN
    mypack.proc1;
END;(*)

The package will not compile because you cannot declare variables in the specification, only procedures and functions. .

Incorrect. Refer to Section 10 Lesson 2.

---

7. Package NEWPACK contains several procedures and functions, including private function PRIVFUNC. From where can PRIVFUNC be invoked? (Choose two.)

Tandai untuk Ditinjau

(1) Point

From an anonymous block

From any procedure in NEWPACK(*)

From any private function in another package

From any function in NEWPACK(*)

From any public procedure in another package

Incorrect. Refer to Section 10 Lesson 2.

---

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

Tandai untuk Ditinjau

(1) Point

True

False (*)

Incorrect. Refer to Section 10 Lesson 2.

---

9. A public component declared in the package specification can be referenced by a private component defined in the package body. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

10. Which of the following will display the detailed code of the subprograms in package DEPTPACK in your schema ?

Tandai untuk Ditinjau

(1) Point

SELECT text FROM USER_SOURCE
WHERE object_name = 'DEPTPACK'
AND object_type = 'PACKAGE BODY'
ORDER BY line;

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'BODY'
ORDER BY line;

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE'
ORDER BY line;

SELECT text FROM USER_SOURCE
WHERE name = 'DEPTPACK'
AND type = 'PACKAGE BODY'
ORDER BY line; (*)

11. We never need to use a forward declaration when invoking a public subprogram. True or False?

Tandai untuk Ditinjau

(1) Point

True (*)

False

Correct

---

12. Which of the following statements about a package initialization block is true?

Tandai untuk Ditinjau

(1) Point

It is an anonymous block in the package specification.

It is an anonymous block at the end of a package body. (*)

It is executed automatically every time any global variable in the package is referenced.

It is a procedure in a package that must be invoked before the rest of the package can be used.

It cannot contain any SQL statements.

Incorrect. Refer to Section 10 Lesson 3.

---

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

Tandai untuk Ditinjau

(1) Point

The call to the package is valid and will execute without error. (*)

The call to the function should be taxes_pkg.tax_salary.

The call to the function should be taxes_pkg (tax.salary).

The data type of tax does not match that of salary.

Incorrect. Refer to Section 10 Lesson 3.

---

14. Which two of these functions could not be in the same package?

1. FUNCTION get_emp (p1 DATE) RETURN VARCHAR2;
2. FUNCTION get_emp (p1 DATE, p2 NUMBER) RETURN VARCHAR2;
3. FUNCTION get_emp (p1 DATE, p2 NUMBER) RETURN NUMBER;
4. FUNCTION get_emp (p1 NUMBER, p2 DATE) RETURN VARCHAR2;

Tandai untuk Ditinjau

(1) Point

1 and 4

3 and 4

2 and 3 (*)

2 and 4

1 and 2

Incorrect. Refer to Section 10 Lesson 3.

---

15. Which one of the following is NOT a restriction on a package function called from a SQL statement?

Tandai untuk Ditinjau

(1) Point

The function can include a COMMIT.

The function can return a BOOLEAN.

The function can include a ROLLBACK.

The function can be overloaded. (*)