name represents a PACKAGE which may also have a PACKAGE BODY In this case we

Name represents a package which may also have a

This preview shows page 194 - 197 out of 322 pages.

* name represents a PACKAGE, which may also * have a PACKAGE BODY. In this case, we can * only compile the complete package, but not * the specification or body as separate * components. */ SELECT object_type INTO proc_type FROM user_objects WHERE object_name = UPPER(name) AND ROWNUM = 1; RETURN proc_type; EXCEPTION WHEN NO_DATA_FOUND THEN RETURN NULL; END; PROCEDURE make(name VARCHAR2) IS stmt VARCHAR2(100); proc_type VARCHAR2(30) := get_type(name); BEGIN IF proc_type IS NOT NULL THEN stmt := 'ALTER '|| proc_type ||' '|| name ||' COMPILE'; execute(stmt); ELSE RAISE_APPLICATION_ERROR(-20001, 'Subprogram '''|| name ||''' does not exist'); END IF; END make;
Image of page 194
Oracle Database 10 g : Develop PL/SQL Program Units A-103 Practice 12: Solutions (continued) PROCEDURE make(name VARCHAR2, objtype VARCHAR2) IS stmt VARCHAR2(100); warn_value varchar2(200); BEGIN stmt := 'ALTER '|| objtype ||' '|| name ||' COMPILE'; warn_value := dbms_warning.get_warning_setting_string; dbms_warning.add_warning_setting_cat( 'PERFORMANCE', 'DISABLE', 'SESSION'); execute(stmt); dbms_warning.set_warning_setting_string( warn_value, 'SESSION'); END make; PROCEDURE regenerate (name VARCHAR2) IS file UTL_FILE.FILE_TYPE; filename VARCHAR2(100) := LOWER(USER ||'_'|| name ||'.sql'); proc_type VARCHAR2(30) := get_type(name); BEGIN IF proc_type IS NOT NULL THEN file := UTL_FILE.FOPEN(dir, filename, 'w'); UTL_FILE.PUT(file, DBMS_METADATA.GET_DDL(proc_type, UPPER(name))); UTL_FILE.FCLOSE(file); ELSE RAISE_APPLICATION_ERROR(-20001, 'Object with '''|| name ||''' does not exist'); END IF; END regenerate; END compile_pkg; / SHOW ERRORS Package body created. No errors.
Image of page 195
Oracle Database 10 g : Develop PL/SQL Program Units A-104 Practice 12: Solutions (continued) 3. Write a new PL/SQL package called TEST_PKG containing a procedure called GET_EMPLOYEES that uses an IN OUT argument. a. In the specification, declare the GET_EMPLOYEES procedure with two parameters, one input parameter specifying a department ID, and an IN OUT parameter specifying a PL/SQL table of employee rows. Hint: You have to declare a TYPE in the package specification for the PL/SQL table parameter’s data type. CREATE OR REPLACE PACKAGE test_pkg IS TYPE emp_tabtype IS TABLE OF employees%ROWTYPE; PROCEDURE get_employees(dept_id NUMBER, emps IN OUT emp_tabtype); END test_pkg; / SHOW ERRORS Package created. No errors. b. In the package body, implement the GET_EMPLOYEES procedure to retrieve all the employee rows for a specified department into the PL/SQL table IN OUT parameter. Hint: Use the SELECT … BULK COLLECT INTO syntax to simplify the code. CREATE OR REPLACE PACKAGE BODY test_pkg IS PROCEDURE get_employees(dept_id NUMBER, emps IN OUT emp_tabtype) IS BEGIN SELECT * BULK COLLECT INTO emps FROM employees WHERE department_id = dept_id; END get_employees; END test_pkg; / SHOW ERRORS Package body created. No errors. 4. Use the ALTER SESSION statement to set the PLSQL_WARNINGS so that all compiler warning categories are enabled.
Image of page 196
Image of page 197

You've reached the end of your free preview.

Want to read all 322 pages?

  • Fall '14
  • The Hours, Oracle Database, Oracle Corporation, PL/SQL program units

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture