be happy and make others to be happy: Overview of Procedures, Functions, and Packages

Overview of Procedures, Functions, and Packages

Oracle Database XE offers the capability to store programs in the database. This functionality enables commonly required code to be written and tested once and then accessed by any application that requires the code. Database-resident program units also ensure that the same processing is applied to the data when the code is invoked, making the development of applications easier and providing consistency between developers.
You can write database-resident programs in PL/SQL, and can use Object Browser to manage sou rce types such as procedures, functions, and packages. The actions include creating, compiling, creating synonyms for, granting privileges on, and showing dependencies for these source types.
This chapter describes the main types of program units you can create with PL/SQL: procedures, functions, and packages. Procedures, functions, and packages are saved and stored in the database, and can be used as building blocks for applications.
For information about the features of the PL/SQL language,

Stored Procedures and Functions

Stored procedures and functions (subprograms) can be compiled and stored in an Oracle Database XE, ready to be executed. Once compiled, it is a schema object known as a stored procedure or stored function, which can be referenced or called any number of times by multiple applications connected to Oracle Database XE. Both stored procedures and functions can accept parameters when they are executed (called). To execute a stored procedure or function, you only need to include its object name.
Procedures and functions that are created outside of a package are called stored or standalone subprograms. Procedures and functions defined within a package are known as packaged subprograms. Procedures and functions nested inside other subprograms or within a PL/SQL block are known as local subprograms, which cannot be referenced by other applications and exist only inside of the enclosing block. For information about subprograms in PL/SQL blocks, see "Using Local PL/SQL Procedures and Functions in PL/SQL Blocks".
Stored procedures and functions are the key to modular, reusable PL/SQL code. Wherever you might use a JAR file in Java, a module in Perl, a shared library in C++, or a DLL in Visual Basic, you can use PL/SQL stored procedures, stored functions, and packages.
You can call stored procedures or functions from a database trigger, another stored subprogram, or interactively from SQL Command Line (SQL*Plus). You can also configure a Web server so that the HTML for a Web page is generated by a stored subprogram, making it simple to provide a Web interface for data entry and report generation.
Procedures and functions are stored in a compact compiled form. When called, they are loaded and processed immediately. Subprograms take advantage of shared memory, so that only one copy of a subprogram is loaded into memory for execution by multiple users.

Packages

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (called the spec) and a body; sometimes the body is unnecessary. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside of the package. The body defines the queries for the cursors and the code for the subprograms.
You can think of the specification as an interface and the body as a black box. You can debug, enhance, or replace a package body without changing the package specification.
The specification holds public declarations, which are visible to stored procedures and other code outside of the package. You must declare subprograms at the end of the specification.
The package body holds implementation details and private declarations, which are hidden from code outside of the package. Following the declarative part of the package body is the optional initialization part, which holds statements that initialize package variables and do any other one-time setup steps.
Applications that call the subprograms in a package only need to know the names and parameters from the package specification. You can change the implementation details inside the package body without affecting the calling applications.

Managing Stored Procedures and Functions

You can create, modify, run, and drop stored procedures and functions with the SQL Commands page, the Object Browser page, the Script Editor page, or SQL Command Line (SQL*Plus). You can view existing functions and procedures in Object Browser.
The SQL CREATE PROCEDURE statement is used to create stored procedures that are stored in the database. The SQL CREATE FUNCTION statement is used to create stored functions that are stored in an Oracle database.
A procedure or function is similar to a miniature program. It has an optional declarative part, an executable part, and an optional exception-handling part. A procedure is a subprogram that performs a specific action. You specify the name of the procedure, its parameters, its local variables, and the BEGIN-END block that contains its code and handles any exceptions. A function is a subprogram that computes and returns a value. Functions and procedures are structured alike, except that functions return a value. See "Using the PL/SQL Block Structure".
When passing parameters to functions and procedures, the parameters can be declared as IN or OUT or IN OUT parameters. For a description of these parameter declarations, see "Using Local PL/SQL Procedures and Functions in PL/SQL Blocks".

Creating a Procedure or Function With the SQL Commands Page

You can use the SQL Commands page to create stored procedures or functions.
To create a procedure or function with the SQL Commands page:

Log in to the Database Home Page. See "Logging in to the Database Home Page".
On the home page, click the SQL icon to display the SQL page.
Click the SQL Commands icon to display the SQL Commands page.
On the SQL Commands page, enter the PL/SQL code for the PL/SQL procedure or function. You can use the code in Example 5-1.
Select (highlight) the code for creating the procedure or function, then click the Run button to create the procedure or function.

Description of the illustration xe_create_proc_sql.gif
Select (highlight) the code for calling the procedure or function, then click the Run button to call the procedure or function.

Description of the illustration xe_run_proc_sql.gif
If you want to save the PL/SQL code for future use, click the Save button.
In the Name field, enter a name for the saved PL/SQL code. You can also enter an optional description. Click the Save button to save the code.
To access saved PL/SQL code, click the Saved SQL tab and select the name of the saved PL/SQL code that you want to access.

In the previous steps you created a procedure. For information about how to execute or call a procedure, see "Calling Stored Procedures or Functions".

Creating a Procedure or Function With the Object Browser Page

You can use the Object Browser page to create stored procedures or functions. This section explains how to create a procedure.
To create a procedure:

Log in to the Database Home Page. See "Logging in to the Database Home Page". To run the examples in this guide, log in as user HR with your password for the HR account.
On the Database Home Page, click the Object Browser icon.
In the object list under Create, select Procedure.
Enter the procedure name (award_bonus), check the Include Arguments box, and then click the Next button.
Enter information for the arguments, then click Next. Use the arguments in Example 5-2. For example:

Name  IN/OUT Type
emp_id     IN     NUMBER
bonus_rate IN     NUMBER
Enter the source code for the procedure body, then click the Next button. Enter the PL/SQL source code in Example 5-2.

Description of the illustration xe_create_proc_objbrows.gif
Click the SQL tab to view the source code for the procedure body. If you need to make corrections, click the Previous button.
When you have finished, click the Finish button. You can click the Edit button to make updates to the subprogram, such as adding additional variable declarations outside the BEGIN .. END block as in Example 5-2.
Click the Compile button to compile the procedure. If errors occur, correct the source code and try compiling again. Compiling the procedure also saves any changes to the procedure.
When you have finished, click the Finish button.

In the previous steps, you created a procedure. For information about how to execute or call a procedure, see "Calling Stored Procedures or Functions".

Viewing Procedures or Functions With the Object Browser Page

To find out which stored procedures or functions exist in your database, use the Object Browser.
To use Object Browser to view procedures and functions:

Log in to the Database Home Page. See "Logging in to the Database Home Page". To run the examples in this guide, log in as user HR with your password for the HR account.
On the Database Home Page, click the Object Browser icon.
In the object list, select Procedures or Functions, then click the name of the procedure or function you want to display. For example, you could select Procedures, then click the name of the procedure (AWARD_BONUS) you previously created.
The procedure or function information displays.

Description of the illustration xe_view_procedure.gif

Creating Stored Procedures With SQL CREATE PROCEDURE

The SQL CREATE PROCEDURE statement lets you create stored procedures that are stored in the database. These stored (schema level) subprograms can be accessed from SQL. You can use the optional OR REPLACE clause to modify an existing procedure without first dropping the procedure.
Example 5-1 is an example of a simple stored procedure that displays current date.

Example 5-1 Creating a Simple Stored Procedure

CREATE OR REPLACE PROCEDURE today_is AS
BEGIN
-- display the current system date in long format
  DBMS_OUTPUT.PUT_LINE( 'Today is ' || TO_CHAR(SYSDATE, 'DL') );
END today_is;
/
-- to call the procedure today_is, you can use the following block
BEGIN
  today_is(); -- the parentheses are optional here
END;
/

Creating a Stored Procedure That Uses Parameters

When you create a procedure or function, you can specify parameters that are passed to the procedure or function when it is called (or invoked). In Example 5-2, note the use of the IN option with procedure arguments emp_id and bonus_rate. For a discussion of IN and IN OUT argument options in PL/SQL subprograms, see "Using Local PL/SQL Procedures and Functions in PL/SQL Blocks".

Example 5-2 Creating a Stored Procedure That Uses Parameters

-- including OR REPLACE is more convenient when updating a subprogram
-- IN is the default for parameter declarations so it could be omitted
CREATE OR REPLACE PROCEDURE award_bonus (emp_id IN NUMBER, bonus_rate IN NUMBER)
  AS
-- declare variables to hold values from table columns, use %TYPE attribute
   emp_comm        employees.commission_pct%TYPE;
   emp_sal         employees.salary%TYPE;
-- declare an exception to catch when the salary is NULL
   salary_missing  EXCEPTION;
BEGIN  -- executable part starts here
-- select the column values into the local variables
   SELECT salary, commission_pct INTO emp_sal, emp_comm FROM employees
    WHERE employee_id = emp_id;
-- check whether the salary for the employee is null, if so, raise an exception
   IF emp_sal IS NULL THEN
     RAISE salary_missing;
   ELSE 
     IF emp_comm IS NULL THEN
-- if this is not a commissioned employee, increase the salary by the bonus rate
-- for this example, do not make the actual update to the salary
-- UPDATE employees SET salary = salary + salary * bonus_rate 
--   WHERE employee_id = emp_id;
       DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id || ' receives a bonus: ' 
                            || TO_CHAR(emp_sal * bonus_rate) );
     ELSE
       DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id 
                            || ' receives a commission. No bonus allowed.');
     END IF;
   END IF;
EXCEPTION  -- exception-handling part starts here
   WHEN salary_missing THEN
      DBMS_OUTPUT.PUT_LINE('Employee ' || emp_id || 
                           ' does not have a value for salary. No update.');
   WHEN OTHERS THEN
      NULL; -- for other exceptions do nothing
END award_bonus;
/

-- the following BEGIN..END block calls, or executes, the award_bonus procedure 
-- using employee IDs 123 and 179 with the bonus rate 0.05 (5%)
BEGIN
  award_bonus(123, 0.05);
  award_bonus(179, 0.05);
END;
/

The output of the calls is similar to:
Employee 123 received a bonus: 325
Employee 179 receives a commission. No bonus allowed.
When executed, this procedure processes an employee ID and a bonus rate. It uses the Id to select the salary and commission percentage of the employee from the employees table. If the salary is null, an exception is raised. If the employee does not receive a commission, the employee's salary is updated by the bonus rate; otherwise no update is made. For a discussion of exception handling, see "Handling PL/SQL Errors".
For different methods to execute (call) stored subprograms, see Example 5-6.

Creating a Stored Procedure With the AUTHID Clause

By default, stored procedures and functions execute with the privileges of their owner, not their current user. Such definer's rights subprograms are bound to the schema in which they reside, allowing you to refer to objects in the same schema without qualifying their names. For example, if schemas HR and OE both have a table called departments, a procedure owned by HR can refer to departments rather than the qualified HR.departments. If user OE calls the procedure owned by HR, the procedure still accesses the departments table owned by HR.
You can use the AUTHID CURRENT_USER clause to make stored procedures and functions execute with the privileges and schema context of the calling user. You can create one instance of the procedure, and many users can call it to access their own data because invoker's rights subprograms are not bound to a particular schema.
In Example 5-3, the procedure is created with the AUTHID CURRENT_USER clause. This example is based on Example 4-36.

Example 5-3 Creating a Stored Procedure With the AUTHID Clause

CREATE OR REPLACE PROCEDURE create_log_table 
-- use AUTHID CURRENT _USER to execute with the privileges and 
-- schema context of the calling user
  AUTHID CURRENT_USER AS 
  tabname       VARCHAR2(30); -- variable for table name
  temptabname   VARCHAR2(30); -- temporary variable for table name
  currentdate   VARCHAR2(8);  -- varible for current date
BEGIN
-- extract, format, and insert the year, month, and day from SYSDATE into 
-- the currentdate variable
  SELECT TO_CHAR(EXTRACT(YEAR FROM SYSDATE)) || 
     TO_CHAR(EXTRACT(MONTH FROM SYSDATE),'FM09') || 
     TO_CHAR(EXTRACT(DAY FROM SYSDATE),'FM09') INTO currentdate FROM DUAL;
-- construct the log table name with the current date as a suffix
  tabname := 'log_table_' || currentdate;

-- check whether a table already exists with that name
-- if it does NOT exist, then go to exception handler and create table
-- if the table does exist, then note that table already exists
  SELECT TABLE_NAME INTO temptabname FROM USER_TABLES 
    WHERE TABLE_NAME = UPPER(tabname);
  DBMS_OUTPUT.PUT_LINE('Table ' || tabname || ' already exists.');

  EXCEPTION
    WHEN NO_DATA_FOUND THEN 
    -- this means the table does not exist because the table name 
    -- was not found in USER_TABLES
      BEGIN
-- use EXECUTE IMMEDIATE to create a table with tabname as the table name
        EXECUTE IMMEDIATE 'CREATE TABLE ' || tabname 
                         || '(op_time VARCHAR2(10), operation VARCHAR2(50))' ;
        DBMS_OUTPUT.PUT_LINE(tabname || ' has been created');
      END;

END create_log_table;
/

-- to call the create_log_table procedure, you can use the following
BEGIN 
  create_log_table; 
END;
/

For different methods to execute (call) stored subprograms, see Example 5-6.

Creating Stored Functions With the SQL CREATE FUNCTION Statement

The SQL CREATE FUNCTION statement lets you create stored functions that are stored in an Oracle database. These stored (schema level) subprograms can be accessed from SQL. You can use the optional OR REPLACE clause to modify an existing function.
Example 5-4 is an example of a function that returns a character string that contains the upper case last and first names of an employee. The example also show how to run (call) the function.

Example 5-4 Creating a Stored Function That Returns a String

CREATE OR REPLACE FUNCTION last_first_name (empid NUMBER)
  RETURN VARCHAR2 IS
  lastname   employees.last_name%TYPE; -- declare a variable same as last_name
  firstname  employees.first_name%TYPE; -- declare a variable same as first_name
BEGIN
  SELECT last_name, first_name INTO lastname, firstname FROM employees 
    WHERE employee_id = empid;
  RETURN ( 'Employee: ' || empid || ' - ' || UPPER(lastname) 
                                 || ', ' || UPPER(firstname) );
END last_first_name; 
/

-- you can use the following block to call the function
DECLARE
  empid NUMBER := 163; -- pick an employee ID to test the function
BEGIN
-- display the output of the function
  DBMS_OUTPUT.PUT_LINE( last_first_name(empid) );
END;
/

-- you can also call a function from a SQL SELECT statement 
-- using the dummy DUAL table
SELECT last_first_name(163) FROM DUAL;

Example 5-5 is an example of a stored function that returns the calculated salary ranking for a specific employee based on the current minimum and maximum salaries of employees in the same job category.

Example 5-5 Creating a Stored Function That Returns a Number

-- function calculates the salary ranking of the employee based on the current
-- minimum and maximum salaries for employees in the same job category
CREATE OR REPLACE FUNCTION emp_sal_ranking (empid NUMBER) 
  RETURN NUMBER IS
  minsal        employees.salary%TYPE; -- declare a variable same as salary
  maxsal        employees.salary%TYPE; -- declare a variable same as salary
  jobid         employees.job_id%TYPE; -- declare a variable same as job_id
  sal           employees.salary%TYPE; -- declare a variable same as salary
BEGIN
-- retrieve the jobid and salary for the specific employee ID
  SELECT job_id, salary INTO jobid, sal FROM employees WHERE employee_id = empid;
-- retrieve the minimum and maximum salaries for employees with the same job ID
  SELECT MIN(salary), MAX(salary) INTO minsal, maxsal FROM employees
      WHERE job_id = jobid;
-- return the ranking as a decimal, based on the following calculation
  RETURN ((sal - minsal)/(maxsal - minsal));
END emp_sal_ranking;
/

-- create a PL/SQL block to call the function, you can also use another subprogram
-- because a function returns a value, it is called as part of a line of code
DECLARE
  empid NUMBER := 163; -- pick an employee ID to test the function
BEGIN
-- display the output of the function, round to 2 decimal places
  DBMS_OUTPUT.PUT_LINE('The salary ranking for employee ' || empid || ' is: ' 
                       || ROUND(emp_sal_ranking(empid),2) );
END;
/

The output of the PL/SQL block is similar to:
The salary ranking for employee 163 is: .63

Calling Stored Procedures or Functions

You can call a stored subprogram from a BEGIN ... END block or from another subprogram or a package.
When calling a stored procedure or function, you can write the actual parameters using the following type of notation:

Positional notation: You specify the same parameters in the same order as they are declared in the procedure. This notation is compact, but you must specify the parameters (especially literals) in the correct order.
Named notation: You specify the name of each parameter and its value. An arrow (=>) serves as the association operator. The order of the parameters is not significant.
Mixed notation: You specify the first parameters with positional notation, then switch to named notation for the last parameters.

Example 5-6 shows how you can call the stored procedure in Example 5-2.

Example 5-6 Techniques for Calling Stored Procedures or Functions

-- use a PL/SQL block to execute the procedure
BEGIN
  award_bonus(179, 0.05);
END;
/
-- using named notation for the parameters, rather than positional
BEGIN 
  award_bonus(bonus_rate=>0.05, emp_id=>123); 
END;
/

Editing Procedures or Functions

To edit procedures and functions, you can use the Object Browser page, the SQL Commands page, or the SQL CREATE OR REPLACE statement with SQL Command Line.
If you use the SQL CREATE OR REPLACE statement with SQL Command Line, you simply type in the modified procedure or function code. See "Entering and Executing SQL Statements and Commands".
To edit a procedure in the SQL Commands page:

Log in to the Database Home Page. See "Logging in to the Database Home Page".
On the home page, click the SQL icon to display the SQL page.
Click the SQL Commands icon to display the SQL Commands page.
Click the Saved SQL tab to display the saved SQL modules.
Click the name of the saved SQL that contains the procedure or function code that you want to edit.
Modify the source code for the procedure or function. Click the Run button if you want to execute the procedure or function.
When you are finished, you can click the Save button to save the code for future use.

To edit a subprogram with Object Browser:

Log in to the Database Home Page. See "Logging in to the Database Home Page". To run the examples in this guide, log in as user HR with your password for the HR account.
Click the Object Browser icon on the Database Home Page.
The Object Browser home page appears.
Select Procedures or Functions in the object list, then click the subprogram you want to display.
With the subprogram displayed, click Edit button to modify the subprogram code.
Click the Compile button to ensure your changes did raise any errors when executed. Compiling the subprogram also saves the changes.

Dropping a Procedure or Function

You can drop a procedure or function from the database with the Object Browser page or the SQL DROP statement.
To use the Object Browser page to drop procedures and functions:

Log in to the Database Home Page. See "Logging in to the Database Home Page". To run the examples in this guide, log in as user HR with your password for the HR account.
On the Database Home Page, click the Object Browser icon.
In the object list, select Procedures or Functions, then click the name of the procedure or function you want to drop.
Click the Drop button.
Click the Finish button to confirm the action.

To drop procedures or functions with SQL statements, use the SQL DROP PROCEDURE or DROP FUNCTION statement, as shown in Example 5-7.

Example 5-7 Dropping Subprograms With the DROP Statement

-- drop the procedure award_bonus to remove from the database
DROP PROCEDURE award_bonus;

-- drop the function emp_sal_ranking to remove from database
DROP FUNCTION emp_sal_ranking;

Managing Packages

You can create, modify, and drop packages and package bodies using the Object Browser page, the SQL Commands page, the Script Editor page, or SQL Command Line (SQL*Plus). You can view existing packages and package bodies with the Object Browser page.

Writing Packages With PL/SQL Code

With PL/SQL, you can break down an application into well-defined modules. Using PL/SQL code, you can write program units that are stored as database objects that can be reused. These objects include packages, subprograms, and triggers. Subprograms and packages are discussed in this section; triggers are discussed in Chapter 6, "Using Triggers".

Guidelines for Writing Packages

When writing packages, keep them general so they can be reused in future applications. Become familiar with the Oracle-supplied packages, and avoid writing packages that duplicate features already provided by Oracle.
Design and define package specifications before the package bodies. Place in a specification only those parts that must be visible to calling programs. That way, other developers cannot build unsafe dependencies on your implementation details.
To reduce the need for recompiling when code is changed, place as few items as possible in a package specification. Changes to a package body do not require recompiling calling procedures. Changes to a package specification require Oracle Database XE to recompile every stored subprogram that references the package.

Creating Packages in the SQL Commands Page

To create and run a package specification or body in the SQL Commands page:

Log in to the Database Home Page. See "Logging in to the Database Home Page".
On the home page, click the SQL icon to display the SQL page.
Click the SQL Commands icon to display the SQL Commands page.
On the SQL Commands page, enter the PL/SQL code for the package specification or body. Use the code in Example 5-8.

Description of the illustration xe_create_pkg_sql.gif
Click the Run button to create the package specification or body. If necessary, select (highlight) only the specific code for creating the package specification or body before clicking the Run button. Any comments outside the package or package body block are not legal in the SQL Commands page.
If you want to save the PL/SQL code for future use, click the Save button.
In the Name field, enter a name for the saved PL/SQL code (emp_actions_pkg_spec). You can also enter an optional description. Click the Save button to save the code.
To access saved PL/SQL code, click the Saved SQL tab and select the name of the saved PL/SQL code that you want to access.
To create, run, and save the PL/SQL code for a package body, repeat the steps in this example with the code in Example 5-9.

In the previous steps you created a package. For information about how to execute or call a subprogram in the package, see "Calling Procedures and Functions in Packages".

See Also:

Oracle Database Express Edition Application Express User's Guide for detailed information about using SQL Scripts

Creating Packages With the Object Browser Page

You can use the Object Browser page to create packages. This section explains how to create a package specification.
To create a package specification:

Log in to the Database Home Page. See "Logging in to the Database Home Page".
On the Database Home Page, click the Object Browser icon.
In the Detail pane, select Package from the Create menu.
In the Create Package page, select the Specification option and click Next.
Enter the package name (emp_actions_new), and then click the Next button.
Enter the PL/SQL source code for the package specification. Use the code in Example 5-8.

Description of the illustration xe_create_pkg_objbrows.gif
After entering the code for the package specification, click the Finish button.
Click the Body tab, then the Edit button to enter the source code for the package body. Use the code in Example 5-9, substituting emp_actions_new for emp_actions.
Click the Compile button to run the package. If errors are raised, correct the source code and try compiling again. Compiling the package also saves any changes made to the package.
When you have finished, click the Finish button.

In the previous steps, you created a package. For information about how to execute or call a subprogram in the package, see "Calling Procedures and Functions in Packages".

Viewing Packages With the Object Browser Page

To find out which packages and package bodies exist in your database, use the Object Browser.
To use the Object Browser page to view packages and package bodies:

Log in to the Database Home Page. See "Logging in to the Database Home Page".
On the Database Home Page, click the Object Browser icon.
In the object list, select Packages then click the name of the package you want to display.
The package specification information displays.
With the package specification displayed, click the Body tab to view the package body if it exists.

Creating Packages With the SQL CREATE PACKAGE Statement

To create packages, use the SQL CREATE PACKAGE and CREATE PACKAGE BODY statements. You can use these SQL statements in the SQL Commands page, the Script Editor page, the Object Browser page, or SQL Command Line (SQL*Plus). In Example 5-8 and Example 5-9, the OR REPLACE option is used so that you can update an existing package without having to first drop the package.
In Example 5-8, the emp_actions package specification contains two procedures that update the employees table and one function that provides information. The package specification provides the declaration of the subprograms. The package body provides the contents of the subprograms.

Example 5-8 Creating a Package Specification

CREATE OR REPLACE PACKAGE emp_actions AS  -- package specification
  
  PROCEDURE hire_employee (lastname VARCHAR2, 
    firstname VARCHAR2, email VARCHAR2, phoneno VARCHAR2,
    hiredate DATE, jobid VARCHAR2, sal NUMBER, commpct NUMBER,
    mgrid NUMBER, deptid NUMBER);
  PROCEDURE remove_employee (empid NUMBER);
  FUNCTION emp_sal_ranking (empid NUMBER) RETURN NUMBER;
END emp_actions;
/

In Example 5-9, the emp_actions package body is created. The package body provides the contents of the subprograms in the package specification.

Example 5-9 Creating a Package Body

CREATE OR REPLACE PACKAGE BODY emp_actions AS  -- package body

-- code for procedure hire_employee, which adds a new employee
  PROCEDURE hire_employee (lastname VARCHAR2,
    firstname VARCHAR2, email VARCHAR2, phoneno VARCHAR2, hiredate DATE,
    jobid VARCHAR2, sal NUMBER, commpct NUMBER, mgrid NUMBER, deptid NUMBER) IS
    min_sal    employees.salary%TYPE; -- variable to hold minimum salary for jobid
    max_sal    employees.salary%TYPE; -- variable to hold maximum salary for jobid
    seq_value  NUMBER;  -- variable to hold next sequence value
  BEGIN
    -- get the next sequence number in the employees_seq sequence
    SELECT employees_seq.NEXTVAL INTO seq_value FROM DUAL;
    -- use the next sequence number for the new employee_id
    INSERT INTO employees VALUES (seq_value, lastname, firstname, email,
     phoneno, hiredate, jobid, sal, commpct, mgrid, deptid);
     SELECT min_salary INTO min_sal FROM jobs WHERE job_id = jobid;
     SELECT max_salary INTO max_sal FROM jobs WHERE job_id = jobid;
     IF sal > max_sal THEN
       DBMS_OUTPUT.PUT_LINE('Warning: ' || TO_CHAR(sal) 
                 || ' is greater than the maximum salary '
                 || TO_CHAR(max_sal) || ' for the job classification ' || jobid );
     ELSIF sal < min_sal THEN
       DBMS_OUTPUT.PUT_LINE('Warning: ' || TO_CHAR(sal) 
                 || ' is less than the minimum salary '
                 || TO_CHAR(min_sal) || ' for the job classification ' || jobid );
     END IF;
  END hire_employee;

-- code for procedure remove_employee, which removes an existing employee
  PROCEDURE remove_employee (empid NUMBER) IS
     firstname employees.first_name%TYPE;
     lastname  employees.last_name%TYPE;
  BEGIN
    SELECT first_name, last_name INTO firstname, lastname FROM employees 
      WHERE employee_id = empid;
    DELETE FROM employees WHERE employee_id = empid;
    DBMS_OUTPUT.PUT_LINE('Employee: ' || TO_CHAR(empid) || ', ' 
                      || firstname || ', ' || lastname || ' has been deleted.');
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('Employee ID: ' || TO_CHAR(empid) || ' not found.');
  END remove_employee;

-- code for function emp_sal_ranking, which calculates the salary ranking of the
-- employee based on the minimum and maximum salaries for the job category
  FUNCTION emp_sal_ranking (empid NUMBER) RETURN NUMBER IS
    minsal        employees.salary%TYPE; -- declare a variable same as salary
    maxsal        employees.salary%TYPE; -- declare a variable same as salary
    jobid         employees.job_id%TYPE; -- declare a variable same as job_id
    sal           employees.salary%TYPE; -- declare a variable same as salary
  BEGIN
-- retrieve the jobid and salary for the specific employee ID
    SELECT job_id, salary INTO jobid, sal FROM employees 
       WHERE employee_id = empid;
-- retrieve the minimum and maximum salaries for the job ID
    SELECT min_salary, max_salary INTO minsal, maxsal FROM jobs
       WHERE job_id = jobid;
-- return the ranking as a decimal, based on the following calculation
    RETURN ((sal - minsal)/(maxsal - minsal));
  END emp_sal_ranking;
END emp_actions;
/

-- the following BEGIN..END block calls, or executes, the emp_sal_ranking
-- function in the emp_actions package with an argument value
DECLARE
  empid NUMBER := 163; -- use a test value for the employee_id
BEGIN
  DBMS_OUTPUT.put_line('The salary ranking for employee ' || empid || ' is: ' 
                       || ROUND(emp_actions.emp_sal_ranking(empid),2) );
END;
/

The output of the PL/SQL block is similar to:
The salary ranking for employee 163 is: .58
Note that the function result for employee 163 is different from the result for Example 5-5. While the functions have the same function name (emp_sal_ranking), they are not the same function. The function in the package is identified by the package name prefix, as in emp_actions.emp_sal_ranking.
For methods on calling subprograms in a package, see "Calling a Subprogram in a Package".

Editing Packages

To edit packages and package bodies, you can use the Object Browser page, the SQL Commands page, or the SQL CREATE OR REPLACE statement with SQL Command Line.
If you use the SQL CREATE OR REPLACE statement with SQL Command Line, you simply type in the modified package specification or body code. See "Entering and Executing SQL Statements and Commands".
To edit a package in the SQL Commands page:

Log in to the Database Home Page. See "Logging in to the Database Home Page".
On the home page, click the SQL icon to display the SQL page.
Click the SQL Commands icon to display the SQL Commands page.
Click the Saved SQL tab to display the saved SQL modules.
Click the name of the saved SQL that contains the package code that you want to edit.
Modify the source code for the package. Click the Run button if you want to execute the package.
When you are finished, you can click the Save button to save the code for future use.

To edit a package with the Object Browser page:

Log in to the Database Home Page. See "Logging in to the Database Home Page". To run the examples in this guide, log in as user HR with your password for the HR account.
On the Database Home Page, click the Object Browser icon.
In the object list, select Packages and then click the package you want to display.
The package specification information displays.
With the package specification displayed, click the Edit button to modify the package specification. You can click the Body tab to edit the source code for the package body if it exists.
Click the Compile button to ensure your changes did raise any errors when executed. Compiling the package also saves the changes.

Dropping Packages

You can use the SQL DROP statement or the Object Browser page to drop packages and package bodies.
You can drop a package or package body with the SQL DROP statement. When drop a package specification, the corresponding package body is dropped also. You can choose to drop only the package body. For example:

-- drop only the package body
DROP PACKAGE BODY my_package;
-- drop the package specification and package body
DROP PACKAGE my_package;
To drop a package or package body with the Object Browser page:

Log in to the Database Home Page. See "Logging in to the Database Home Page". To run the examples in this guide, log in as user HR with your password for the HR account.
On the Database Home Page, click the Object Browser icon.
Select Packages in the object list, then click the package you want to display.
The package specification information displays.
With the package specification displayed, click the Drop button to drop the package specification and package body. You can click the Body tab and then the Drop button to drop only the packaged body if it exists.
Click the Finish button to confirm that you want to drop the package specification or package body.

Calling Procedures and Functions in Packages

To call the procedures or functions of the emp_actions package created in Example 5-9, you can execute the statements in Example 5-10. The subprograms can be executed in a BEGIN .. END block or from another subprogram. Note the use of the package name as a prefix to the subprogram name.

Example 5-10 Calling a Subprogram in a Package

-- the following calls the hire_employee subprogram in the emp_actions package
-- with the associated parameter values
BEGIN
  emp_actions.hire_employee('Townsend', 'Mark', 'MTOWNSEND',
   '555.123.2222', '31-JUL-05', 'AC_MGR', 9000, .1, 101, 110);
END;
/

-- the following calls the remove_employee subprogram in the emp_actions package
-- in this case, remove the employee just added (employee_id = 208)
-- note that the employee ID might be different on your system
BEGIN
  emp_actions.remove_employee(208);
END;
/

-- cleanup: drop the package
DROP PACKAGE emp_actions;

Packages are stored in the database, where they can be shared by many applications. Calling a packaged subprogram for the first time loads the whole package and caches it in memory, saving on disk I/O for subsequent calls. Thus, packages enhance reuse and improve performance in a multiple-user, multiple-application environment.
If a subprogram does not take any parameters, you can include an empty set of parentheses or omit the parentheses, both in PL/SQL and in functions called from SQL queries. For calls to a method that takes no parameters, an empty set of parentheses is optional within PL/SQL scopes, but they are required within SQL scopes.

Accessing Variables in Packages

You can create a package specification that is designated only to supply common variables to other packages or subprograms. With the variables in one package, they can be easily maintained for all subprograms that use the variables, rather than maintaining the variables in all the individual subprograms. Common variables are typically used in multiple subprograms, such as a sales tax rate.
In Example 5-11, the variables my_var_pi, my_var_e, and my_var_sales_tax can be used by any subprogram. If you change the value of any of those variables, then all subprograms that use the variable will get the new value without having to change anything in those individual subprograms.
Note that you need to use of the package name as a prefix to the variable name, such as my_var_pkg.my_var_pi.

Example 5-11 Creating Variables in a PL/SQL Package Specification

CREATE OR REPLACE PACKAGE my_var_pkg AS
-- set up a variable for pi, used in calculations with circles and spheres
  my_var_pi         NUMBER := 3.14016408289008292431940027343666863227;
-- set up a variable for e, the base of the natural logarithm
  my_var_e          NUMBER := 2.71828182845904523536028747135266249775;
-- set up a variable for the current retail sales tax rate
  my_var_sales_tax  NUMBER := 0.0825;
END my_var_pkg;
/

Example 5-12 shows how variables that are defined in the my_var_pkg package specification can be used in PL/SQL subprograms.

Example 5-12 Using Variables From a Package Specification

CREATE OR REPLACE PROCEDURE circle_area(radius NUMBER) IS
  c_area NUMBER;
BEGIN
-- the following uses the value of the my_var_pi variable in my_var_pkg for pi 
-- in the following calculation of the area of a circle
  c_area := my_var_pkg.my_var_pi * radius**2;
  DBMS_OUTPUT.PUT_LINE('Radius: ' || TO_CHAR(radius) 
                       || ' Area: ' || TO_CHAR(c_area) );
END circle_area;
/

BEGIN -- some examples of the use of package variables
-- call the circle_area procedure with radius equal to 3, my_var_pi is used to
-- calculate the area in circle_area
  circle_area(3);
-- determine the sales tax on a $25 item using my_var_sales_tax for the tax rate
  DBMS_OUTPUT.PUT_LINE('Sales tax on $25.99 is $' 
                        || TO_CHAR(25.99 * my_var_pkg.my_var_sales_tax) );
END;
/

Accessing Types in Packages

You can create a package specification that is designated only to supply common types, along with common variables, to other packages or subprograms. With the types in one package, they can be easily maintained for all subprograms that use the types, rather than maintaining the types in all the individual subprograms. Common types, such as a REF CURSOR, can be used to declare variables in other packages and subprograms. See "Cursor Variables (REF CURSORs)".
In Example 5-13, the emp_refcur_typ and my_refcur_typ types can be used by any subprogram to declare cursor variables. Note that you need to use of the package name as a prefix to the type name, such as my_var_pkg.my_refcur_typ.

Example 5-13 Creating Types and Variables in a PL/SQL Package Specification

CREATE OR REPLACE PACKAGE my_var_pkg AS
-- set up a strongly typed cursor variable for the employees table
  TYPE emp_refcur_typ IS REF CURSOR RETURN employees%ROWTYPE;
-- set up a weakly typed cursor variable for multiple use
  TYPE my_refcur_typ IS REF CURSOR;
-- set up a variable for pi, used in calculations with circles and spheres
  my_var_pi         NUMBER := 3.14016408289008292431940027343666863227;
-- set up a variable for e, the base of the natural logarithm
  my_var_e          NUMBER := 2.71828182845904523536028747135266249775;
-- set up a variable for the current retail sales tax rate
  my_var_sales_tax  NUMBER := 0.0825;
END my_var_pkg;
/

Example 5-14 show how the emp_refcur_typ cursor variable that is defined in the my_var_pkg package specification can be used in PL/SQL subprograms.

Example 5-14 Using the emp_refcur_typ REF CURSOR From a Package Specification

-- this procedure uses the strongly-typed my_var_pkg.emp_refcur_typ REF CURSOR
CREATE OR REPLACE PROCEDURE display_emp_cursor (
                             emp_cursor IN OUT my_var_pkg.emp_refcur_typ) AS
  person employees%ROWTYPE;
BEGIN
  DBMS_OUTPUT.PUT_LINE('-- Here are the employees in the result set --');
  LOOP
    FETCH emp_cursor INTO person;
    EXIT WHEN emp_cursor%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(person.employee_id || ' - ' || person.last_name 
                                            || ', ' || person.first_name);
  END LOOP;
END display_emp_cursor;
/

-- this procedure uses the strongly-typed my_var_pkg.emp_refcur_typ REF CURSOR
CREATE OR REPLACE PROCEDURE get_emp_id (firstname IN VARCHAR2, 
                                        lastname IN VARCHAR2) AS
  emp_cursor my_var_pkg.emp_refcur_typ;
BEGIN
-- search for employee IDs based on the input for first and last names
  OPEN emp_cursor FOR SELECT * FROM employees 
    WHERE SUBSTR(UPPER(first_name), 1, LENGTH(firstname)) = UPPER(firstname) 
    AND SUBSTR(UPPER(last_name), 1, LENGTH(lastname)) = UPPER(lastname);
-- pass emp_cursor to the display_emp_cursor procedure for processing
  display_emp_cursor(emp_cursor); 
  CLOSE emp_cursor;
END get_emp_id;
/

BEGIN -- some examples of the use of package types
-- call the get_emp_id procedure that uses a REF CURSOR defined in a package
  get_emp_id('steve', 'kin');
END;
/

Example 5-15 show how the my_refcur_typ cursor variable that is defined in the my_var_pkg package specification can be used to return a result set that could be accessed by other subprograms.

Example 5-15 Using the my_refcur_typ REF CURSOR From a Package Specification

-- this procedure uses the weakly-typed my_var_pkg.my_refcur_typ REF CURSOR
CREATE OR REPLACE PROCEDURE get_emp_info (firstname IN VARCHAR2,
  lastname IN VARCHAR2, emp_cursor IN OUT my_var_pkg.my_refcur_typ) AS
BEGIN
-- the following returns employee info based on first and last names
  OPEN emp_cursor FOR SELECT employee_id, first_name, last_name, email,
    phone_number FROM employees 
    WHERE SUBSTR(UPPER(first_name), 1, LENGTH(firstname)) = UPPER(firstname)
    AND SUBSTR(UPPER(last_name), 1, LENGTH(lastname)) = UPPER(lastname);
END get_emp_info;
/

-- the procedure can be updated to change the columns returned in the result set
CREATE OR REPLACE PROCEDURE get_emp_info (firstname IN VARCHAR2,
  lastname IN VARCHAR2, emp_cursor IN OUT my_var_pkg.my_refcur_typ) AS
BEGIN
-- because this procedure uses a weakly typed REF CURSOR, the cursor is flexible
-- and the SELECT statement can be changed, as in the following
  OPEN emp_cursor FOR SELECT e.employee_id, e.first_name, e.last_name, e.email,
    e.phone_number, e.hire_date, j.job_title FROM employees e
    JOIN jobs j ON e.job_id = j.job_id
    WHERE SUBSTR(UPPER(first_name), 1, LENGTH(firstname)) = UPPER(firstname)
    AND SUBSTR(UPPER(last_name), 1, LENGTH(lastname)) = UPPER(lastname);
END get_emp_info;
/

Oracle Provided Packages

Oracle Database XE provides product-specific packages that define application programming interfaces (APIs) you can call from PL/SQL, SQL, Java, or other programming environments. This section includes a list of the most common packages with a brief description and an overview of a few useful packages.

List of Oracle Database XE Packages

Table 5-1 provides a list of the common PL/SQL packages included with Oracle Database XE.

Table 5-1 Summary of Oracle Supplied PL/SQL Packages

Package Name	Description
`DBMS_ALERT`	Provides support for the asynchronous notification of database events.
`DBMS_APPLICATION_INFO`	Lets you register an application name with the database for auditing or performance tracking purposes.
`DBMS_CHANGE_NOTIFICATION`	Is part of a set of features that clients use to receive notifications when result sets of a query have changed. The package contains interfaces that can be used by mid-tier clients to register objects and specify delivery mechanisms.
`DBMS_CRYPTO`	Lets you encrypt and decrypt stored data, can be used in conjunction with PL/SQL programs running network communications, and supports encryption and hashing algorithms.
`DBMS_DATAPUMP`	Lets you move all, or part of, a database between databases, including both data and metadata.
`DBMS_DB_VERSION`	Specifies the Oracle version numbers and other information useful for simple conditional compilation selections based on Oracle versions.
`DBMS_DDL`	Provides access to some SQL DDL statements from stored procedures, and provides special administration operations not available as DDLs.
`DBMS_DEBUG`	Implements server-side debuggers and provides a way to debug server-side PL/SQL program units.
`DBMS_DESCRIBE`	Describes the arguments of a stored procedure with full name translation and security checking.
`DBMS_EPG`	Implements the embedded PL/SQL gateway that enables a Web browser to invoke a PL/SQL stored procedure through an HTTP listener.
`DBMS_ERRLOG`	Provides a procedure that enables you to create an error logging table so that DML operations can continue after encountering errors rather than abort and roll back.
`DMBS_FILE_TRANSFER`	Lets you copy a binary file within a database or to transfer a binary file between databases.
`DBMS_JOB`	Lets you schedule administrative procedures that you want performed at periodic intervals; it is also the interface for the job queue.
`DBMS_LOCK`	Lets you request, convert and release locks through Oracle Lock Management services.
`DBMS_METADATA`	Lets callers easily retrieve complete database object definitions (metadata) from the dictionary.
`DBMS_OBFUSCATION_TOOLKIT`	Provides procedures for Data Encryption Standards.
`DBMS_OUTPUT`	Displays output from stored procedures, packages, and triggers, which is especially useful for displaying PL/SQL debugging information.
`DBMS_PIPE`	Provides a DBMS pipe service which enables messages to be sent between sessions.
`DBMS_RANDOM`	Provides a built-in random number generator.
`DBMS_RESUMABLE`	Lets you suspend large operations that run out of space or reach space limits after executing for a long time, fix the problem, and make the statement resume execution.
`DBMS_ROWID`	Provides procedures to create rowids and to interpret their contents.
`DBMS_SCHEDULER`	Provides a collection of scheduling functions that are callable from any PL/SQL program.
`DBMS_SERVER_ALERT`	Lets you issue alerts when some threshold has been violated.
`DBMS_SESSION`	Provides access to SQL `ALTER` `SESSION` statements, and other session information, from stored procedures.
`DBMS_SQL`	Lets you use dynamic SQL to access the database.
`DBMS_TDB`	Reports whether a database can be transported between platforms using the RMAN `CONVERT DATABASE` command. It verifies that databases on the current host platform are of the same endian format as the destination platform, and that the state of the current database does not prevent transport of the database.
`DBMS_TTS`	Checks if the transportable set is self-contained.
`DBMS_TYPES`	Consists of constants, which represent the built-in and user-defined types.
`DBMS_UTILITY`	Provides various utility routines.
`DBMS_WARNING`	Provides the interface to query, modify and delete current system or session settings.
`DBMS_XDB`	Describes Resource Management and Access Control APIs for PL/SQL
`DBMS_XDB_VERSION`	Describes versioning APIs
`DBMS_XDBT`	Describes how an administrator can create a ConText index on the XML DB hierarchy and configure it for automatic maintenance
`DBMS_XDBZ`	Controls the Oracle XML DB repository security, which is based on Access Control Lists (ACLs).
`DBMS_XMLDOM`	Explains access to XMLType objects
`DBMS_XMLGEN`	Converts the results of a SQL query to a canonical XML format.
`DBMS_XMLPARSER`	Explains access to the contents and structure of XML documents.
`DMBS_XMLQUERY`	Provides database-to-XMLType functionality.
`DBMS_XMLSAVE`	Provides XML-to-database-type functionality.
`DBMS_XMLSCHEMA`	Explains procedures to register and delete XML schemas.
`DBMS_XMLSTORE`	Provides the ability to store XML data in relational tables.
`DBMS_XPLAN`	Describes how to format the output of the `EXPLAIN` `PLAN` command.
`DBMS_XSLPROCESSOR`	Explains access to the contents and structure of XML documents.
`HTF`	Hypertext functions generate HTML tags.
`HTMLDB_APPLICATION`	Enables users to take advantage of global variables
`HTMLDB_CUSTOM_AUTH`	Enables users to create form elements dynamically based on a SQL query instead of creating individual items page by page.
`HTMLDB_ITEM`	Enables users to create form elements dynamically based on a SQL query instead of creating individual items page by page.
`HTMLDB_UTIL`	Provides utilities for getting and setting session state, getting files, checking authorizations for users, resetting different states for users, and also getting and setting preferences for users.
`HTP`	Hypertext procedures generate HTML tags.
`OWA_CACHE`	Provides an interface that enables the `PL/SQL Gateway` cache to improve the performance of PL/SQL Web applications.
`OWA_COOKIE`	Provides an interface for sending and retrieving HTTP cookies from the client's browser.
`OWA_CUSTOM`	Provides a Global PLSQL Agent Authorization callback function
`OWA_IMAGE`	Provides an interface to access the coordinates where a user clicked on an image.
`OWA_OPT_LOCK`	Contains subprograms that impose optimistic locking strategies so as to prevent lost updates.
`OWA_PATTERN`	Provides an interface to locate text patterns within strings and replace the matched string with another string.
`OWA_SEC`	Provides an interface for custom authentication.
`OWA_TEXT`	Contains subprograms used by `OWA_PATTERN` for manipulating strings. They are externalized so you can use them directly.
`OWA_UTIL`	Contains utility subprograms for performing operations such as getting the value of CGI environment variables, printing the data that is returned to the client, and printing the results of a query in an HTML table.
`UTL_COLL`	Enables PL/SQL programs to use collection locators to query and update.
`UTL_COMPRESS`	Provides a set of data compression utilities.
`UTL_DBWS`	Provides database Web services.
`UTL_ENCODE`	Provides functions that encode RAW data into a standard encoded format so that the data can be transported between hosts.
`UTL_FILE`	Enables your PL/SQL programs to read and write operating system text files and provides a restricted version of standard operating system stream file I/O.
`UTL_HTTP`	Enables HTTP callouts from PL/SQL and SQL to access data on the Internet or to call Oracle Web Server Cartridges.
`UTL_I18N`	Provides a set of services (Oracle Globalization Service) that help developers build multilingual applications.
`UTL_INADDR`	Provides a procedure to support internet addressing.
`UTL_LMS`	Retrieves and formats error messages in different languages.
`UTL_MAIL`	A utility for managing e-mail which includes commonly used e-mail features, such as attachments, CC, BCC, and return receipt.
`UTL_RAW`	Provides SQL functions for `RAW` datatypes that concat, substr to and from `RAWS.`
`UTL_RECOMP`	Recompiles invalid PL/SQL modules, Java classes, indextypes and operators in a database, either sequentially or in parallel.
`UTL_REF`	Enables a PL/SQL program to access an object by providing a reference to the object.
`UTL_SMTP`	Provides PL/SQL functionality to send e-mails.
`UTL_TCP`	Provides PL/SQL functionality to support simple TCP/IP-based communications between servers and the outside world.
`UTL_URL`	Provides escape and unescape mechanisms for URL characters.

Overview of Some Useful Packages

This section provides a summary of some useful packages.
This section contains the following topics:

DBMS_OUTPUT Package

The DBMS_OUTPUT package enables you to display output from PL/SQL blocks, subprograms, packages, and triggers. This package is especially useful for displaying PL/SQL debugging information. The PUT_LINE procedure outputs information to a buffer that can be read by another trigger, procedure, or package. You display the information by calling the GET_LINE procedure or by setting the SERVEROUTPUT ON setting in SQL Command Line.
For more information, see "Inputting and Outputting Data with PL/SQL". For examples of the use of DBMS_OUTPUT.PUT_LINE, see Example 5-1, Example 5-2, and Example 5-3.

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_OUTPUT packages

DBMS_RANDOM Package

The DBMS_RANDOM package provides a built-in random number generator. DBMS_RANDOM can be explicitly initialized, but does not need to be initialized before calling the random number generator. It will automatically initialize with the date, userid, and process id if no explicit initialization is performed.
If this package is seeded twice with the same seed, then accessed in the same way, it will produce the same results in both cases.
The DBMS_RANDOM.VALUE function can be called with no parameters to return a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision). Alternatively, you can call the function with low and high parameters to return a random number which is greater than or equal to the low parameter and less than high parameter.
Example 5-16 shows the use of the DBMS_RANDOM.VALUE function to return random numbers from 1 ton 100. The random numbers are truncated to integer values and stored in an array.

Example 5-16 Using the DBMS_RANDOM Package

DECLARE
-- declare an array type with 10 elements of NUMBER
  TYPE random_array IS VARRAY(10) OF NUMBER;
  random_numbers random_array;
  j NUMBER;
BEGIN
  random_numbers := random_array(); -- initialize the array
  FOR i IN 1..10 LOOP
-- add an element to the array
    random_numbers.EXTEND(1); 
-- insert a random number in the next element in the array
    random_numbers(i) := TRUNC(DBMS_RANDOM.VALUE(1,101));

    j := 1;
-- make sure the random number is not already in the array
-- if it is, generated a new random number and check again
    WHILE j < random_numbers.LAST LOOP
       IF random_numbers(i) = random_numbers(j) THEN
          random_numbers(i) := TRUNC(DBMS_RANDOM.VALUE(1,101));
          j := 1;
       ELSE
         j := j + 1;
       END IF;
    END LOOP;

 END LOOP;

-- display the random numbers in the array
 FOR k IN random_numbers.FIRST..random_numbers.LAST LOOP
   DBMS_OUTPUT.PUT_LINE(random_numbers(k));
 END LOOP;

END;
/

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the DBMS_RANDOM packages

HTP Package

With the HTP package, you can create a Web page using HTP hypertext procedures to generate HTML tags. For example the procedure HTP.PARA generates the <P> paragraph tag and HTP.ANCHOR generates the <A> anchor tag. You can also use HTP.PRINT to explicit print HTML tags.
Note that for nearly every HTP procedure that generates one or more HTML tags, there is a corresponding HTF package hypertext function with identical parameters.
Example 5-17 is a modification of Example 4-29 using the HTP.PRINT procedure. For each DBMS_OUTPUT.PUT_LINE in the original example, an HTP.PRINT has been substituted in the modified example.

Example 5-17 Using HTP Print Procedure

CREATE OR REPLACE PROCEDURE htp_yearly_bonus AS 
-- declare variables for data fetched from cursor
  empid       employees.employee_id%TYPE; -- variable for employee_id
  hiredate    employees.hire_date%TYPE;   -- variable for hire_date
  firstname   employees.first_name%TYPE;  -- variable for first_name
  lastname    employees.last_name%TYPE;   -- variable for last_name
  rowcount    NUMBER;
  bonusamount NUMBER;
  yearsworked NUMBER;
-- declare the cursor with a parameter
  CURSOR cursor1 (thismonth NUMBER)IS 
    SELECT employee_id, first_name, last_name, hire_date FROM employees 
       WHERE EXTRACT(MONTH FROM hire_date) = thismonth;
BEGIN
  HTP.PRINT('<html>');                                   -- HTML open
  HTP.PRINT('<head>');                                   -- HEAD open
  HTP.PRINT('<title>Using the HTP Package</title>');     -- title line
  HTP.PRINT('</head>');                                  -- HEAD close 
  HTP.PRINT('<body TEXT="#000000" BGCOLOR="#FFFFFF">') ; -- BODY open
-- open and pass a parameter to cursor1, select employees hired on this month
  OPEN cursor1(EXTRACT(MONTH FROM SYSDATE));
  HTP.PRINT('<h1>----- Today is ' || TO_CHAR(SYSDATE, 'DL') || ' -----</h1>');
  HTP.PRINT('<p>Employees with yearly bonus amounts:</p>');
  HTP.PRINT('<pre>'); -- insert the preformat tag
  LOOP
-- fetches 4 columns into variables
    FETCH cursor1 INTO empid, firstname, lastname, hiredate; 
-- check the cursor attribute NOTFOUND for the end of data
    EXIT WHEN cursor1%NOTFOUND;
-- calculate the yearly bonus amount based on months (years) worked
  yearsworked := ROUND( (MONTHS_BETWEEN(SYSDATE, hiredate)/12) );
  IF yearsworked > 10   THEN bonusamount := 2000;
    ELSIF yearsworked > 8 THEN bonusamount := 1600;
    ELSIF yearsworked > 6 THEN bonusamount := 1200;
    ELSIF yearsworked > 4 THEN bonusamount := 800;
    ELSIF yearsworked > 2 THEN bonusamount := 400;
    ELSIF yearsworked > 0 THEN bonusamount := 100;
  END IF;
-- display the employee Id, first name, last name, hire date, and bonus 
-- for each record (row) fetched
    HTP.PRINT( empid || ' ' || RPAD(firstname, 21, ' ') ||
         RPAD(lastname, 26, ' ') || hiredate || TO_CHAR(bonusamount, '$9,999'));
  END LOOP;
  HTP.PRINT('</pre>'); -- end the preformat tag
  rowcount := cursor1%ROWCOUNT;
  HTP.PRINT('<p>The number of rows fetched is ' || rowcount || '</p>');
  CLOSE cursor1;
  HTP.PRINT('</body>');                                  -- BODY close
  HTP.PRINT('</html>');                                  -- HTML close
END;
/

See Also:

Oracle Database PL/SQL Packages and Types Reference for information about the HTP packages

UTL_FILE Package

The UTL_FILE package enables PL/SQL programs to read and write operating system (OS) text files. It provides a restricted version of standard OS stream file I/O, including open, put, get, and close operations. When you want to read or write a text file, you call the FOPEN function, which returns a file handle for use in subsequent procedure calls. When opening a file with FOPEN, the file can be opened in append (A), read (R), or write (W) mode. After a file is opened, you can use UTL_FULE procedures such as PUT_LINE to write a text string and line terminator to an open file and GET_LINE to read a line of text from an open file into an output buffer.
Before a user can run UTL_FILE procedures, the user must be granted access to UTL_FILE and there must be an accessible directory for the user to read and write files. As the user SYS, you need to run the SQL GRANT EXECUTE statement to provide access to the UTL_FILE package, run the SQL CREATE DIRECTORY statement to set up an accessible directory, and run the SQL GRANT ... DIRECTORY statement to grant privileges to that directory. Example 5-18 shows how to set up an existing directory and grant the HR user access to that directory.

Example 5-18 Setting up a Directory for Use With UTL_FILE

-- first connect as SYS to perform the necessary setups
-- when you run the following to connect as SYS, use your password for SYS
CONNECT SYS/ORACLE AS SYSDBA
-- the following grants access on the UTL_FILE package to user HR
GRANT EXECUTE ON UTL_FILE TO HR;
-- the following sets up directory access for /tmp on a Linux platform
CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp';
-- you could use 'c:\temp' for temp_dir on a Windows platform, note that
-- c:\temp must exist on the Windows computer
-- the following grants the user read and write access to the directory
GRANT READ, WRITE ON DIRECTORY temp_dir TO HR;
-- now connect as user HR/HR to check directory setup 
-- when you connect as HR, use your password for HR
CONNECT HR/HR
-- the following SELECT query lists information about all directories that 
-- have been set up for the user
SELECT * FROM ALL_DIRECTORIES;
-- if TEMP_DIR is listed, then you are ready to run UTL_FILE procedures as HR

After the SQL statements in Example 5-18 are executed, you can connect as the user HR and run UTL_FILE procedures. Some simple examples are shown in Example 5-19.

Example 5-19 Using the UTL_FILE Package

-- connect as user HR and run UTL_FILE procedures
DECLARE
  string1 VARCHAR2(32767);
  file1 UTL_FILE.FILE_TYPE; 
BEGIN
  file1 := UTL_FILE.FOPEN('TEMP_DIR','log_file_test','A'); -- open in append mode
  string1 := TO_CHAR(SYSDATE) || ' UTL_FILE test';
  UTL_FILE.PUT_LINE(file1, string1); -- write a string to the file
  UTL_FILE.FFLUSH(file1);
  UTL_FILE.FCLOSE_ALL; -- close all open files
END;
/

DECLARE
  string1 VARCHAR2(32767);
  file1 UTL_FILE.FILE_TYPE; 
BEGIN
  file1 := UTL_FILE.FOPEN('TEMP_DIR','log_file_test','R'); -- open in read mode
  UTL_FILE.GET_LINE(file1, string1, 32767); -- read a string from the file
  DBMS_OUTPUT.PUT_LINE(string1); -- display the string
  UTL_FILE.FCLOSE_ALL; -- close all open files
END;
/

be happy and make others to be happy

Infolinks

Tuesday 3 July 2012

Overview of Procedures, Functions, and Packages

Overview of Procedures, Functions, and Packages

Stored Procedures and Functions

Packages

Managing Stored Procedures and Functions

Creating a Procedure or Function With the SQL Commands Page

Creating a Procedure or Function With the Object Browser Page

Viewing Procedures or Functions With the Object Browser Page

Creating Stored Procedures With SQL CREATE PROCEDURE

Creating a Stored Procedure That Uses Parameters

Creating a Stored Procedure With the AUTHID Clause

Creating Stored Functions With the SQL CREATE FUNCTION Statement

Calling Stored Procedures or Functions

Editing Procedures or Functions

Dropping a Procedure or Function

Managing Packages

Writing Packages With PL/SQL Code

Guidelines for Writing Packages

Creating Packages in the SQL Commands Page

Creating Packages With the Object Browser Page

Viewing Packages With the Object Browser Page

Creating Packages With the SQL CREATE PACKAGE Statement

Editing Packages

Dropping Packages

Calling Procedures and Functions in Packages

Accessing Variables in Packages

Accessing Types in Packages

Oracle Provided Packages

List of Oracle Database XE Packages

Overview of Some Useful Packages

DBMS_OUTPUT Package

DBMS_RANDOM Package

HTP Package

UTL_FILE Package

No comments:

Post a Comment