Section 9: Using and Managing Packages
9.01. Creating Packages
♦False (*)
2. To be able to invoke a package subprogram from outside the package, it must be declared in the package:
♦Body
♦Specification
♦Both the body and the specification (*)
♦Neither the body nor the specification
3. Which of the following are good reasons to group a set of procedures and functions into a package?
♦Application developers do not need to know the details of the package body code.
♦Related subprograms and variables can be grouped together for easier management and maintenance.
♦If the detailed code is changed, aplications which invoke the package do not need to be recompiled.
♦All of the above. (*)
♦Specification
♦Both the body and the specification
♦Neither the body nor the specification
5. Which of the following can be included in a package?
♦procedures
♦variables
♦PL/SQL types
♦Exceptions
♦All of the above (*)
6. The two parts of a package are stored as separate objects in the database. True or False?
♦True (*)
♦False
7. 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 del_emp
♦DESCRIBE show_emp
♦DESCRIBE emp_pack.del_emp
♦DESCRIBE emp_pack.show_emp
8. Package Specification DEPT_PACK was created by the following code:
PROCEDURE ins_dept(p_deptno IN NUMBER);
FUNCTION get_dept(p_deptno IN NUMBER) RETURN VARCHAR2;
END dept_pack;
dept_pack.ins_dept(20);
END;
(*)
♦BEGIN
dept_pack.get_dept(20);
END;
v_deptname VARCHAR2(20);
BEGIN
v_deptname := get_dept(50);
END;
v_deptname VARCHAR2(20);
BEGIN
v_deptname := dept_pack.get_dept(40);
END;
(*)
♦BEGIN
dept_pack(30);
END;
9.02. Managing Package Concepts
1. 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 function successfully?
♦emp_pkg.emp_sal(101);
♦scott.emp_pkg.emp_sal(101): (*)
♦emp_sal(101);
♦None of the above.
♦All of the above.
2. A local variable defined inside a package procedure has persistence for the database session. True or False?
♦True
♦False (*)
3. Examine the following package specification:
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.)
♦proc1 is a public procedure and func1 is a private function. (*)
♦The package will not compile because you cannot declare variables in the specification, only procedures and functions. .
mypack.percent_tax := 10;
END;
♦The function can be invoked from outside the package.
♦The procedure can be invoked by:
mypack.proc1;
END;
4. 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?
♦DROP BOTH co_pack;
♦DROP PACKAGE BODY co_pack;
♦DROP PACKAGE co_pack; (*)
♦DROP PACKAGE SPECIFICATION co_pack;
♦None of the above
5. What will be displayed when a user executes the following statement?
WHERE object_type LIKE ‘PACK%’;
♦The names of all package specifications and package bodies in the user’s schema (*)
♦The parameters which must be used when invoking all packaged subprograms in the user’s schema
♦The detailed code of all packages in the user’s schema
♦The names of all packages which can be invoked by the user
6. When one component of a package is called, all the package’s components are loaded into memory. True or False?
♦True (*)
♦False
7. 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 (*)
8. Which one of the following queries would you use to see the detailed code of a package called EMP_PKG?
♦SELECT text
FROM user_source
WHERE name = ‘EMP_PKG’ AND type = ‘PACKAGE’
ORDER BY line;
FROM user_packages
WHERE name = ‘EMP_PKG’ AND type = ‘PACKAGE BODY’
ORDER BY line;
FROM all_source
WHERE name = ‘EMP_PKG’ AND type = ‘PACKAGE’
ORDER BY line;
FROM user_source
WHERE name = ‘EMP_PKG’ AND type = ‘PACKAGE BODY’
ORDER BY line;
♦False
10. 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?
♦True
♦False (*)
9.03. Advanced Package Concepts
♦False
♦False (*)
3. Which two of these functions could not be in the same package?
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;
♦1 and 4
♦2 and 4
♦2 and 3 (*)
♦3 and 4
4. Which of the following best describes a package initialization block?
♦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 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 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.
5. If a subprogram is public (declared in the package specification), its detailed code can be written anywhere in the package body without the need to use forward declarations. True or False?
♦True (*)
♦False
6. Package FORWARD_PACK contains two procedures: PROC1 is public while PROC2 is private (not declared in the package specification). These procedures have no parameters. Which of the following package bodies will NOT compile successfully? (Choose two.) )
♦CREATE OR REPLACE PACKAGE BODY forward_pack IS
PROCEDURE proc1 IS
BEGIN
proc2;
END;
PROCEDURE proc2 IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Any message’);
END;
END forward_pack;
(*)
♦CREATE OR REPLACE PACKAGE BODY forward_pack IS
PROCEDURE proc2 IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Any message’);
END;
PROCEDURE proc1 IS
BEGIN
proc2;
END;
END forward_pack;
♦CREATE OR REPLACE PACKAGE BODY forward_pack IS
PROCEDURE proc2;
PROCEDURE proc1 IS
BEGIN
proc2;
END;
PROCEDURE proc2 IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Any message’);
END;
END forward_pack;
♦CREATE OR REPLACE PACKAGE BODY forward_pack IS
PROCEDURE proc1;
PROCEDURE proc1 IS
BEGIN
proc2;
END;
PROCEDURE proc2 IS
proc1;
END;
END forward_pack;
(*)
♦CREATE OR REPLACE PACKAGE BODY forward_pack IS
PROCEDURE proc2;
PROCEDURE proc1 IS
BEGIN
proc2;
END;
PROCEDURE proc2 IS
BEGIN
proc1;
END;
END forward_pack;
PROCEDURE do_work1 (p1 IN VARCHAR2, p2 IN NUMBER);
PROCEDURE do_work2 (p1 IN VARCHAR2, p2 IN NUMBER);
PROCEDURE do_work1 (param1 IN CHAR, param2 IN NUMBER);
FUNCTION do_work2 (param1 IN VARCHAR2, param2 IN NUMBER) RETURN DATE;
END over_pack;
Which of the following calls will be successful? (Choose three.)
♦over_pack.do_work1(‘Smith’,20);
♦v_date := over_pack.do_work2(‘Smith’,20); (*)
♦over_pack.do_work2(‘Smith’,20); (*)
♦over_pack.do_work1(p1=>’Smith’,p2=>20); (*)
♦over_pack.do_work1(param1=>’Smith’);
9.04. Persistent State of Package Variables
1. 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
♦10
♦50 (*)
♦25
2. 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 (*)
3. A package’s state is initialized when the package is first loaded. True or False?
♦True (*)
♦False
4. 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.emp_curs%ISOPEN;
♦curs_pkg.close_curs;
♦curs_pkg.open_curs; (*)
♦EXIT WHEN curs_pkg.emp_curs%NOTFOUND;
9.05. Using Oracle-Supplied Packages
1. Using the FOPEN function, you can do which actions with the UTL_FILE package? (Choose 2)
♦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. (*)
♦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.
2. 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 (*)
3. The DBMS_OUTPUT gives programmers an easy-to-use interface to see for instance the current value of a loop counter or if a program makes it to a particular branch of an IF statement. (True or False?)
♦True (*)
♦False
4. The DBMS_OUTPUT package is useful for which of the following activities? (Choose two)
♦Interact with a user during execution of a function or procedure.
♦Display results to the developer during testing for debugging purposes. (*)
♦Trace the code execution path for a function or procedure. (*)
♦Write operating system text files to the user’s screen.
5. The UTL_FILE package contains several exceptions exclusively used in this package. Which are they? (Choose 3)
♦INVALID_PATH (*)
♦NO_DATA_FOUND
♦WRITE_ERROR (*)
♦INVALID_OPERATION (*)
♦ZERO_DIVIDE
6. Which DBMS_OUTPUT package subprogram places text into the buffer at Line 1?
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.NEW_LINE(‘IF branch was executed’);
TOO_MANY_ROWS
VALUE_ERROR (*)
ZERO_DIVIDE
NO_DATA_FOUND (*)
9.06. Dynamic SQL
♦False
♦False (*)
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.
♦The procedure will not compile successfully because the syntax of the ALTER FUNCTION statement is incorrect
4. What happens when a SQL statement is parsed? (Choose three.)
♦The user’s required privileges are checked. (*)
♦The syntax of the statement is checked. (*)
♦The statement is executed.
♦The results of the statement are returned to the user.
♦Oracle queries the Data Dictionary to make sure that the tables referenced in the SQL statement exist. (*)
5. For which of the following is it necessary to use Dynamic SQL? (Choose three.)
♦ALTER (*)
♦GRANT (*)
♦SAVEPOINT
♦UPDATE
♦DROP (*)
6. The DBMS_SQL package is easier to use than EXECUTE IMMEDIATE. True or False?
♦True
♦False (*)
7. A programmer wants to code a procedure which will create a table with a single column. The datatype of the column will be chosen by the user who invokes the procedure. The programmer writes the following code:
(p_col_datatype IN VARCHAR2) IS
BEGIN
CREATE TABLE newtab (only_col p_col_datatype);
END;
♦Because you cannot create a table inside a procedure
♦Because the invoking user may not have CREATE TABLE privilege
♦Because when the procedure is compiled, Oracle cannot check if the parameter value passed into the procedure is a valid column datatype (*)
♦Because table NEWTAB may already exist
♦None of the above; the procedure will compile successfully
♦Create a SQL statement with varying column data, or different conditions. (*)
♦Enables data-definition statements to be written and executed from PL/SQL. (*)
♦Enables system control statements to be written and executed from PL/SQL.
9.07. Improving PL/SQL
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;
♦v_emptab is incorrectly typed.
♦FORALL does not require END LOOP. (*)
♦Nothing is wrong; it will compile successfully.
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;
♦False
♦False
4. What are benefits of using the NOCOPY hint? (Choose two)
♦Safer because it uses passing by value.
♦Efficient since it uses less memory. (*)
♦Uses a larger block of server memory for faster access.
♦Faster because a single copy of the data is used. (*)
5. 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 B (*)
♦Position C
6. What is the main purpose for using the RETURNING clause?
♦Improve performance by returning a single value.
♦Improve performance by minimizing the number of statements.
♦Improve performance by making one call to the SQL engine. (*)
♦Return more readily any exceptions that are raised by the statement.
7. 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
;
…
♦Position B (*)
♦Position C
♦Position D
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;
♦False (*)
9. 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 B (*)
♦Position C
InfoCad Blog 
This actually answered my drawback, thanks!
LikeLike