Oracle8 Application Developer's Guide Release 8.0 A58241-01 |
Library | Product | Contents | Index

# 10
Using Procedures and Packages

This chapter discusses the procedural capabilities of Oracle, including:

- PL/SQL Procedures and Packages
- PL/SQL Packages
- Remote Dependencies
- Cursor Variables
- Hiding PL/SQL Code
- Error Handling
- Invoking Stored Procedures
- Calling Stored Functions from SQL Expressions
- Supplied Packages
- Describing Stored Procedures
- Listing Information about Procedures and Packages
- The DBMS_ROWID Package
- The UTL_HTTP Package

Note: If you are using Trusted Oracle, also see the Trusted Oracle documentation for additional information.

## PL/SQL Procedures and Packages

PL/SQL is a modern, block-structured programming language. It provides you with a number of features that make developing powerful database applications very convenient. For example, PL/SQL provides procedural constructs, such as loops and conditional statements, that you do not find in standard SQL.

You can directly issue SQL data manipulation language (DML) statements inside PL/SQL blocks, and you can use procedures, supplied by Oracle, to perform data definition language (DDL) statements.

PL/SQL code executes on the server, so using PL/SQL allows you to centralize significant parts of your database applications for increased maintainability and security. It also enables you to achieve a significant reduction of network overhead in client/server applications.

Note: Some Oracle tools, such as Oracle Forms, contain a PL/SQL engine, and can execute PL/SQL locally.

You can even use PL/SQL for some database applications in place of 3GL programs that use embedded SQL or the Oracle Call Interface (OCI).

There are several kinds of PL/SQL program units:

- anonymous PL/SQL blocks
- triggers
- stand-alone stored procedures and functions
- packages, that can contain stored procedures and functions

  See Also: For complete information about the PL/SQL language, see the PL/SQL User's Guide and Reference.

### Anonymous Blocks

An anonymous PL/SQL block consists of an optional declarative part, an executable part, and one or more optional exception handlers.

You use the declarative part to declare PL/SQL variables, exceptions, and cursors. The executable part contains PL/SQL code and SQL statements, and can contain nested blocks. Exception handlers contain code that is called when the exception is raised, either as a predefined PL/SQL exception (such as `NO_DATA_FOUND` or `ZERO_DIVIDE`), or as an exception that you define.

The following short example of a PL/SQL anonymous block prints the names of all employees in department 20 in the`EMP` table, using the`DBMS_OUTPUT` package (described on page 12-22):

    DECLARE
        emp_name    VARCHAR2(10);
        CURSOR      c1 IS SELECT ename FROM emp
                            WHERE deptno = 20;
    BEGIN
        LOOP
            FETCH c1 INTO emp_name;
            EXIT WHEN c1%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(emp_name);
        END LOOP;
    END;

Note: If you try this block out using SQL*Plus make sure to issue the command`SET SERVEROUTPUT ON` so that output using the `DBMS_OUTPUT` procedures such as `PUT_LINE` is activated. Also, terminate the example with a slash (/) to activate it.

Exceptions allow you to handle Oracle error conditions within PL/SQL program logic. This allows your application to prevent the server from issuing an error that could cause the client application to abort. The following anonymous block handles the predefined Oracle exception `NO_DATA_FOUND` (which would result in an `ORA`-`01403` error if not handled):

    DECLARE
        emp_number   INTEGER := 9999;
        emp_name     VARCHAR2(10);
    BEGIN
        SELECT ename INTO emp_name FROM emp
            WHERE empno = emp_number;   -- no such number
        DBMS_OUTPUT.PUT_LINE('Employee name is ' || emp_name);
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.PUT_LINE('No such employee: ' || emp_number);
    END;

You can also define your own exceptions, declare them in the declaration part of a block, and define them in the exception part of the block. An example follows:

    DECLARE
        emp_name           VARCHAR2(10);
        emp_number         INTEGER;
        empno_out_of_range EXCEPTION;
    BEGIN
        emp_number := 10001;
        IF emp_number > 9999 OR emp_number < 1000 THEN
            RAISE empno_out_of_range;
        ELSE
            SELECT ename INTO emp_name FROM emp
                WHERE empno = emp_number;
            DBMS_OUTPUT.PUT_LINE('Employee name is ' || emp_name);
    END IF;
    EXCEPTION
        WHEN empno_out_of_range THEN
            DBMS_OUTPUT.PUT_LINE('Employee number ' || emp_number ||
              ' is out of range.');
    END;

See Also: PL/SQL User's Guide and Reference for a complete treatment of exceptions.

Anonymous blocks are most often used either interactively, from a tool such as SQL*Plus, or in a precompiler, OCI, or SQL*Module application. They are normally used to call stored procedures, or to open cursor variables.

See Also: A description of cursor variables on page 10-26.

### Database Triggers

A database trigger is a special kind of PL/SQL anonymous block. You can define triggers to fire before or after SQL statements, either on a statement level or for each row that is affected. See Chapter 13, "Using Database Triggers" in this Guide for more information.

### Stored Procedures and Functions

A stored procedure or function is a PL/SQL program unit that

- has a name
- can take parameters, and return values
- is stored in the data dictionary
- can be invoked by many users

  Note: The term stored procedure is sometimes used generically in this Guide to cover both stored procedures and stored functions.

#### Procedure Names

Since a procedure is stored in the database, it must be named, to distinguish it from other stored procedures, and to make it possible for applications to call it. Each publicly-visible procedure in a schema must have a unique name. The name must be a legal PL/SQL identifier.

Note: If you plan to call a stored procedure using a stub generated by SQL*Module, the stored procedure name must also be a legal identifier in the calling host 3GL language such as Ada or C.

Procedure and function names that are part of packages can be overloaded. That is, you can use the same name for different subprograms as long as their formal parameters differ in number, order, or datatype family. See PL/SQL User's Guide and Reference for more information about subprogram name overloading.

#### Procedure Parameters

Stored procedures and functions can take parameters. The following example shows a stored procedure that is similar to the anonymous block on page 10-3:

    PROCEDURE get_emp_names (dept_num IN NUMBER) IS
        emp_name       VARCHAR2(10);
        CURSOR         c1 (depno NUMBER) IS
                          SELECT ename FROM emp
                            WHERE deptno = depno;

    BEGIN
        OPEN c1(dept_num);
        LOOP
            FETCH c1 INTO emp_name;
            EXIT WHEN c1%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE(emp_name);
        END LOOP;
        CLOSE c1;
    END;

In the stored procedure example, the department number is an input parameter, which is used when the parameterized cursor C1 is opened.

The formal parameters of a procedure have three major parts:

name | The name of the parameter, which must be a legal PL/SQL identifier.
mode | The parameter mode, which indicates whether the parameter is an input-only parameter (`IN`), an output-only parameter (`OUT`), or is both an input and an output parameter (`IN` `OUT`). If the mode is not specified, `IN` is assumed.
datatype | The parameter datatype is a standard PL/SQL datatype.

#### Parameter Modes

You use parameter modes to define the behavior of formal parameters. The three parameter modes,`IN` (the default),`OUT`, and`IN OUT`, can be used with any subprogram. However, avoid using the`OUT` and`IN OUT` modes with functions. The purpose of a function is to take zero or more arguments and return a single value. It is poor programming practice to have a function return multiple values. Also, functions should be free from side effects, which change the values of variables not local to the subprogram.

Table 10-1 summarizes the information about parameter modes. Parameter modes are explained in detail in the PL/SQL User's Guide and Reference.

##### Table 10-1 Parameter Modes

IN | OUT | IN OUT
the default | must be specified | must be specified
passes values to a subprogram | returns values to the caller | passes initial values to a subprogram; returns updated values to the caller
formal parameter acts like a constant | formal parameter acts like an uninitialized variable | formal parameter acts like an initialized variable
formal parameter cannot be assigned a value | formal parameter cannot be used in an expression; must be assigned a value | formal parameter should be assigned a value
actual parameter can be a constant, initialized variable, literal, or expression | actual parameter must be a variable | actual parameter must be a variable

#### Parameter Datatypes

The datatype of a formal parameter consists of one of the following:

- an unconstrained type name, such as`NUMBER` or`VARCHAR2`
- a type that is constrained using the `%TYPE` or `%ROWTYPE` attributes

  Note: Numerically constrained types such as`NUMBER(2)` or`VARCHAR2(20)` are not allowed in a parameter list.

##### %TYPE and %ROWTYPE Attributes

However, you can use the type attributes `%TYPE` and `%ROWTYPE` to constrain the parameter. For example, the`GET_EMP_NAMES` procedure specification in "Procedure Parameters" on page 10-5 could be written as

    PROCEDURE get_emp_names(dept_num IN emp.deptno%TYPE)

to have the `DEPT_NUM` parameter take the same datatype as the `DEPTNO` column in the `EMP` table. The column and table must be available when a declaration using `%TYPE` (or `%ROWTYPE`) is elaborated.

Using `%TYPE` is recommended, since if the type of the column in the table changes, it is not necessary to change the application code.

If the `GET_EMP_NAMES` procedure is part of a package, then you can use previously-declared public (package) variables to constrain a parameter datatype. For example:

    dept_number    number(2);
    ...
    PROCEDURE get_emp_names(dept_num IN dept_number%TYPE);

You use the `%ROWTYPE` attribute to create a record that contains all the columns of the specified table. The following example defines the `GET_EMP_REC` procedure, which returns all the columns of the `EMP` table in a PL/SQL record, for the given `EMPNO`:

    PROCEDURE get_emp_rec (emp_number  IN emp.empno%TYPE,
                           emp_ret    OUT emp%ROWTYPE) IS
    BEGIN
        SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
            INTO emp_ret
            FROM emp
            WHERE empno = emp_number;
    END;

You could call this procedure from a PL/SQL block as follows:

    DECLARE
      emp_row      emp%ROWTYPE;     -- declare a record matching a
                                    -- row in the EMP table
    BEGIN
      get_emp_rec(7499, emp_row);   -- call for emp# 7499
      DBMS_OUTPUT.PUT(emp_row.ename || ' ' || emp_row.empno);
      DBMS_OUTPUT.PUT(' ' || emp_row.job || ' ' || emp_row.mgr);
      DBMS_OUTPUT.PUT(' ' || emp_row.hiredate || ' ' || emp_row.sal);
      DBMS_OUTPUT.PUT(' ' || emp_row.comm || ' ' || emp_row.deptno);
      DBMS_OUTPUT.NEW_LINE;
    END;

Stored functions can also return values that are declared using `%ROWTYPE`. For example:

    FUNCTION get_emp_rec (dept_num IN emp.deptno%TYPE)
        RETURN emp%ROWTYPE IS ...

#### Tables and Records

You can pass PL/SQL tables as parameters to stored procedures and functions. You can also pass tables of records as parameters.

#### Default Parameter Values

Parameters can take default values. You use the `DEFAULT` keyword or the assignment operator to give a parameter a default value. For example, the specification for the `GET_EMP_NAMES` procedure on page 10-5 could be written as

    PROCEDURE get_emp_names (dept_num IN NUMBER DEFAULT 20) IS ...

or as

    PROCEDURE get_emp_names (dept_num IN NUMBER := 20) IS ...

When a parameter takes a default value, it can be omitted from the actual parameter list when you call the procedure. When you do specify the parameter value on the call, it overrides the default value.

#### DECLARE Keyword

Unlike in an anonymous PL/SQL block, you do not use the keyword `DECLARE` before the declarations of variables, cursors, and exceptions in a stored procedure. In fact, it is an error to use it.

### Creating Stored Procedures and Functions

Use your normal text editor to write the procedure. At the beginning of the procedure, place the command

    CREATE PROCEDURE procedure_name AS   ...

For example, to use the example on page 10-8, you can create a text (source) file called get_emp.sql containing the following code:

    CREATE PROCEDURE get_emp_rec (emp_number  IN emp.empno%TYPE,
                                  emp_ret    OUT emp%ROWTYPE) AS
    BEGIN
        SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
            INTO emp_ret
            FROM emp
            WHERE empno = emp_number;
    END;
    /

Then, using an interactive tool such as SQL*Plus, load the text file containing the procedure by entering the command

    SQLPLUS> @get_emp

to load the procedure into the current schema from the get_emp.sql file (.sql is the default file extension). Note the slash (/) at the end of the code. This is not part of the code; it just activates the loading of the procedure.

WARNING: When developing a new procedure, it is usually much more convenient to use the CREATE OR REPLACE... PROCEDURE command. This replaces any previous version of that procedure in the same schema with the newer version, but note that this is done without warning.

You can use either the keyword `IS` or `AS` after the procedure parameter list.

Use the `CREATE` [`OR` `REPLACE`] `FUNCTION`... command to store functions. See the Oracle8 SQL Reference for the complete syntax of the `CREATE` `PROCEDURE` and `CREATE` `FUNCTION` commands.

#### Privileges Required to Create Procedures and Functions

To create a stand-alone procedure or function, or package specification or body, you must meet the following prerequisites:

- You must have the `CREATE` `PROCEDURE` system privilege to create a procedure or package in your schema, or the `CREATE` `ANY` `PROCEDURE` system privilege to create a procedure or package in another user's schema.

  Note: To create without errors, that is, to compile the procedure or package successfully, requires the following additional privileges: The owner of the procedure or package must have been explicitly granted the necessary object privileges for all objects referenced within the body of the code; the owner cannot have obtained required privileges through roles.

If the privileges of a procedure's or package's owner change, the procedure must be reauthenticated before it is executed. If a necessary privilege to a referenced object is revoked from the owner of the procedure (or package), the procedure cannot be executed.

The `EXECUTE` privilege on a procedure gives a user the right to execute a procedure owned by another user. Privileged users execute the procedure under the security domain of the procedure's owner. Therefore, users never have to be granted the privileges to the objects referenced by a procedure. This allows for more disciplined and efficient security strategies with database applications and their users. Furthermore, all procedures and packages are stored in the data dictionary (in the `SYSTEM` tablespace). No quota controls the amount of space available to a user who creates procedures and packages.

### Altering Stored Procedures and Functions

To alter a stored procedure or stored function, you must first `DROP` it, using the `DROP` `PROCEDURE` or `DROP` `FUNCTION` command, then recreate it using the `CREATE` `PROCEDURE` or `CREATE` `FUNCTION` command. Alternatively, use the `CREATE` `OR` `REPLACE` `PROCEDURE` or `CREATE` `OR` `REPLACE` `FUNCTION` command, which first drops the procedure or function if it exists, then recreates it as specified.

WARNING: The procedure or function is dropped without any warning.

### External Procedures

A PL/SQL procedure executing on an Oracle Server can call an external procedure, written in a 3GL. The 3GL procedure executes in a separate address space from that of the Oracle Server.

See Also: For information about external procedures, see the PL/SQL User's Guide and Reference.

## PL/SQL Packages

A package is a group of PL/SQL types, objects, and stored procedures and functions. The specification part of a package declares the public types, variables, constants, and subprograms that are visible outside the immediate scope of the package. The body of a package defines the objects declared in the specification, as well as private objects that are not visible to applications outside the package.

The following example shows a package specification for a package named `EMPLOYEE_MANAGEMENT`. The package contains one stored function and two stored procedures.

    CREATE PACKAGE employee_management AS
       FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,
          mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,
          deptno NUMBER) RETURN NUMBER;
       PROCEDURE fire_emp (emp_id NUMBER);
       PROCEDURE sal_raise (emp_id NUMBER, sal_incr NUMBER);
    END employee_management;

The body for this package defines the function and the procedures:

    CREATE PACKAGE BODY employee_management AS
       FUNCTION hire_emp (name VARCHAR2, job VARCHAR2,
          mgr NUMBER, hiredate DATE, sal NUMBER, comm NUMBER,
          deptno NUMBER) RETURN NUMBER IS

    -- The function accepts all arguments for the fields in
    -- the employee table except for the employee number.
    -- A value for this field is supplied by a sequence.
    -- The function returns the sequence number generated
    -- by the call to this function.

           new_empno    NUMBER(10);

       BEGIN
          SELECT emp_sequence.NEXTVAL INTO new_empno FROM dual;
          INSERT INTO emp VALUES (new_empno, name, job, mgr,
             hiredate, sal, comm, deptno);
          RETURN (new_empno);
       END hire_emp;

       PROCEDURE fire_emp(emp_id IN NUMBER) AS

    -- The procedure deletes the employee with an employee
    -- number that corresponds to the argument EMP_ID.  If
    -- no employee is found, an exception is raised.

       BEGIN
          DELETE FROM emp WHERE empno = emp_id;
          IF SQL%NOTFOUND THEN
          raise_application_error(-20011, 'Invalid Employee
             Number: ' || TO_CHAR(emp_id));
       END IF;
    END fire_emp;

    PROCEDURE sal_raise (emp_id IN NUMBER, sal_incr IN NUMBER) AS

    -- The procedure accepts two arguments.  EMP_ID is a
    -- number that corresponds to an employee number.
    -- SAL_INCR is the amount by which to increase the
    -- employee's salary.
       BEGIN

    -- If employee exists, update salary with increase.
          UPDATE emp
             SET sal = sal + sal_incr
             WHERE empno = emp_id;
          IF SQL%NOTFOUND THEN
             raise_application_error(-20011, 'Invalid Employee
                Number: ' || TO_CHAR(emp_id));
          END IF;
       END sal_raise;
    END employee_management;

Note: If you want to try this example, first create the sequence number `EMP_SEQUENCE`. You can do this using the following SQL*Plus statement:
    SQL> EXECUTE CREATE SEQUENCE emp_sequence
       > START WITH 8000 INCREMENT BY 10;

### Creating Packages

Each part of a package is created with a different command. Create the package specification using the `CREATE` `PACKAGE` command. The `CREATE` `PACKAGE` command declares public package objects.

To create a package body, use the `CREATE` `PACKAGE` `BODY` command. The `CREATE` `PACKAGE` `BODY` command defines the procedural code of the public procedures and functions declared in the package specification. (You can also define private (or local) package procedures, functions, and variables within the package body. See "Local Objects" on page 10-15.

#### The OR REPLACE Clause

It is often more convenient to add the `OR` `REPLACE` clause in the `CREATE` `PACKAGE` or `CREATE` `PACKAGE` `BODY` commands when you are first developing your application. The effect of this option is to drop the package or the package body without warning. The `CREATE` commands would then be

    CREATE OR REPLACE PACKAGE package_name AS ...

and

    CREATE OR REPLACE PACKAGE BODY package_name AS ...

#### Privileges Required to Create Packages

The privileges required to create a package specification or package body are the same as those required to create a stand-alone procedure or function; see page 10-10.

### Creating Packaged Objects

The body of a package can contain

- procedures declared in the package specification
- functions declared in the package specification
- definitions of cursors declared in the package specification
- local procedures and functions, not declared in the package specification
- local variables

Procedures, functions, cursors, and variables that are declared in the package specification are global. They can be called, or used, by external users that have execute permission for the package, or that have `EXECUTE` `ANY` `PROCEDURE` privileges.

When you create the package body, make sure that each procedure that you define in the body has the same parameters, by name, datatype, and mode, as the declaration in the package specification. For functions in the package body, the parameters as well as the return type must agree in name and type.

#### Local Objects

You can define local variables, procedures, and functions in a package body. These objects can only be accessed by other procedures and functions in the body of the same package. They are not visible to external users, regardless of the privileges they hold.

### Naming Packages and Package Objects

The names of a package and all public objects in the package must be unique within a given schema. The package specification and its body must have the same name. All package constructs must have unique names within the scope of the package, unless overloading of procedure names is desired.

### Dropping Packages and Procedures

A stand-alone procedure, a stand-alone function, a package body, or an entire package can be dropped using the SQL commands `DROP` `PROCEDURE`, `DROP` `FUNCTION`, `DROP` `PACKAGE` `BODY`, and `DROP` `PACKAGE`, respectively. A `DROP` `PACKAGE` statement drops both a package's specification and body.

The following statement drops the `OLD_SAL_RAISE` procedure in your schema:

    DROP PROCEDURE old_sal_raise;

#### Privileges Required to Drop Procedures and Packages

To drop a procedure or package, the procedure or package must be in your schema or you must have the `DROP` `ANY` `PROCEDURE` privilege. An individual procedure within a package cannot be dropped; the containing package specification and body must be re-created without the procedures to be dropped.

### Package Invalidations and Session State

Each session that references a package object has its own instance of the corresponding package, including persistent state for any public and private variables, cursors, and constants. If any of the session's instantiated packages (specification or body) are subsequently invalidated and recompiled, all other dependent package instantiations (including state) for the session are lost.

For example, assume that session S instantiates packages P1 and P2, and that a procedure in package P1 calls a procedure in package P2. If P1 is invalidated and recompiled (for example, as the result of a DDL operation), the session S instantiations of both P1 and P2 are lost. In such situations, a session receives the following error the first time it attempts to use any object of an invalidated package instantiation:

    ORA-04068: existing state of packages has been discarded

The second time a session makes such a package call, the package is reinstantiated for the session without error.

Note: Oracle has been optimized to not return this message to the session calling the package that it invalidated. Thus, in the example above, session `S` would receive this message the first time it called package `P2`, but would not receive it when calling `P1`.

In most production environments, DDL operations that can cause invalidations are usually performed during inactive working hours; therefore, this situation might not be a problem for end-user applications. However, if package specification or body invalidations are common in your system during working hours, you might want to code your applications to detect for this error when package calls are made. For example, the user-side application might reinitialize any user-side state that depends on any session's package state (that was lost) and reissue the package call.

## Remote Dependencies

Dependencies among PL/SQL library units (packages, stored procedures, and stored functions) can be handled in two ways:

- timestamps
- signatures

### Timestamps

If timestamps are used to handle dependencies among PL/SQL library units, whenever you alter a library unit or a relevant schema object all of its dependent units are marked as invalid and must be recompiled before they can be executed.

Each library unit carries a timestamp that is set by the server when the unit is created or recompiled. Figure 10-1 demonstrates this graphically. Procedures P1 and P2 call stored procedure P3. Stored procedure P3 references table T1. In this example, each of the procedures is dependent on table T1. P3 depends upon T1 directly, while P1 and P2 depend upon T1 indirectly.

#### Figure 10-1 Dependency Relationships

If P3 is altered, P1 and P2 are marked as invalid immediately if they are on the same server as P3. The compiled states of P1 and P2 contain records of the timestamp of P3. So if the procedure P3 is altered and recompiled, the timestamp on P3 no longer matches the value that was recorded for P3 during the compilation of P1 and P2.

If P1 and P2 are on a client system, or on another Oracle Server in a distributed environment, the timestamp information is used to mark them as invalid at runtime.

#### Disadvantages of the Timestamp Model

The disadvantage of this dependency model is that is unnecessarily restrictive. Recompilation of dependent objects across the network are often performed when not strictly necessary, leading to performance degradation.

Furthermore, on the client side, the timestamp model can lead to situations that block an application from running at all, if the client-side application is built using PL/SQL version 2. (Earlier releases of tools such as Oracle Forms that used PL/SQL version 1 on the client side did not use this dependency model, since PL/SQL version 1 had no support for stored procedures.)

For releases of Oracle Forms that are integrated with PL/SQL version 2 on the client side, the timestamp model can present problems. First of all, during the installation of the application, the application is rendered invalid unless the client-side PL/SQL procedures that it uses are recompiled at the client site. Also, if a client-side procedure depends on a server procedure, and the server procedure is changed or automatically recompiled, the client-side PL/SQL procedure must then be recompiled. Yet in many application environments (such as Forms runtime applications), there is no PL/SQL compiler available on the client. This blocks the application from running at all. The client application developer must then redistribute new versions of the application to all customers.

### Signatures

To alleviate some of the problems with the timestamp-only dependency model, Oracle provides the additional capability of remote dependencies using signatures. The signature capability affects only remote dependencies. Local (same server) dependencies are not affected, as recompilation is always possible in this environment.

The signature of a subprogram contains information about the

- name of the subprogram
- base types of the parameters of the subprogram
- modes of the parameters (`IN`, `OUT`, `IN` `OUT`)

  Note: Only the types and modes of parameters are significant. The name of the parameter does not affect the signature.

The user has control over whether signatures or timestamps govern remote dependencies. See "Controlling Remote Dependencies" on page 10-24 for more information. If the signature dependency model is in effect, a dependency on a remote library unit causes an invalidation of the dependent unit if the dependent unit contains a call to a subprogram in the parent unit, and the signature of this subprogram has been changed in an incompatible manner.

For example, consider a procedure `GET_EMP_NAME` stored on a server `BOSTON_SERVER`. The procedure is defined as

    CREATE OR REPLACE PROCEDURE get_emp_name (
                    emp_number   IN NUMBER,
                    hire_date   OUT VARCHAR2,
                    emp_name    OUT VARCHAR2) AS
    BEGIN
        SELECT ename, to_char(hiredate, 'DD-MON-YY')
            INTO emp_name, hire_date
            FROM emp
            WHERE empno = emp_number;
    END;

When `GET_EMP_NAME` is compiled on the `BOSTON_SERVER`, its signature as well as its timestamp is recorded.

Now assume that on another server, in California, some PL/SQL code calls `GET_EMP_NAME` identifying it using a `DBlink` called `BOSTON_SERVER`, as follows:

    CREATE OR REPLACE PROCEDURE print_ename (
          emp_number IN NUMBER) AS
        hire_date    VARCHAR2(12);
        ename        VARCHAR2(10);
    BEGIN
        get_emp_name@BOSTON_SERVER(
            emp_number, hire_date, ename);
        dbms_output.put_line(ename);
        dbms_output.put_line(hiredate);
    END;

When this California server code is compiled, the following actions take place:

- a connection is made to the Boston server
- the signature of `GET_EMP_NAME` is transferred to the California server
- the signature is recorded in the compiled state of `PRINT_ENAME`

At runtime, during the remote procedure call from the California server to the Boston server, the recorded signature of `GET_EMP_NAME` that was saved in the compiled state of `PRINT_ENAME` gets sent across to the Boston server., regardless of whether there were any changes or not.

If the timestamp dependency mode is in effect, a mismatch in timestamps causes an error status to be returned to the calling procedure.

However, if the signature mode is in effect, any mismatch in timestamps is ignored, and the recorded signature of `GET_EMP_NAME` in the compiled state of `PRINT_ENAME` on the California server is compared with the current signature of `GET_EMP_NAME` on the Boston server. If they match, the call succeeds. If they do not match, an error status is returned to the `PRINT_NAME` procedure.

Note that the `GET_EMP_NAME` procedure on the Boston server could have been changed. Or, its timestamp could be different from that recorded in the `PRINT_NAME` procedure on the California server, due to, for example, the installation of a new release of the server. As long as the signature remote dependency mode is in effect on the California server, a timestamp mismatch does not cause an error when `GET_EMP_NAME` is called.

#### What Is a Signature?

A signature is associated with each compiled stored library unit. It identifies the unit using the following criteria:

- the name of the unit, that is, the package, procedure, or function name
- the types of each of the parameters of the subprogram
- the modes of the parameters
- the number of parameters
- the type of the return value for a function

#### When Does a Signature Change?

##### Datatypes

A signature changes when you change from one class of datatype to another. Within each datatype class, there can be several types. Changing a parameter datatype from one type to another within a class does not cause the signature to change.

Table 10-2 shows the classes of types.

##### Table 10-2 Datatype Classes

Varchar Types: | Number Types:
`VARCHAR2` | `NUMBER`
`VARCHAR` | `INTEGER`
`STRING` | `INT`
`LONG` | `SMALLINT`
`ROWID` | `DECIMAL`
| `DEC`
Character Types: | `REAL`
`CHARACTER` | `FLOAT`
`CHAR` | `NUMERIC`
| `DOUBLE` `PRECISION`
Raw Types: | `NUMERIC`
`RAW` |
`LONG` `RAW` |
Integer Types: | Date Type:
`BINARY_INTEGER` | `DATE`
`PLS_INTEGER` |
`BOOLEAN` | MLS Label Type:
`NATURAL` | `MLSLABEL`
`POSITIVE` |
`POSITIVEN` |
`NATURALN` |

##### Modes

Changing to or from an explicit specification of the default parameter mode `IN` does not change the signature of a subprogram. For example, changing

    PROCEDURE P1 (param1 NUMBER);

to

    PROCEDURE P1 (param1 IN NUMBER);

does not change the signature. Any other change of parameter mode does change the signature.

##### Default Parameter Values

Changing the specification of a default parameter value does not change the signature. For example, procedure P1 has the same signature in the following two examples:

    PROCEDURE P1 (param1 IN NUMBER := 100);
    PROCEDURE P1 (param1 IN NUMBER := 200);

An application developer who requires that callers get the new default value must recompile the called procedure, but no signature-based invalidation occurs when a default parameter value assignment is changed.

#### Examples of Signatures

In the `GET_EMP_NAME` procedure defined on page 10-5, if the procedure body is changed to

    BEGIN
    -- date format model changes
        SELECT ename, to_char(hiredate, 'DD/MON/YYYY')
            INTO emp_name, hire_date
            FROM emp
            WHERE empno = emp_number;
    END;

then the specification of the procedure has not changed, and so its signature has not changed.

But if the procedure specification is changed to

    CREATE OR REPLACE PROCEDURE get_emp_name (
                    emp_number  IN NUMBER,
                    hire_date   OUT DATE,
                    emp_name    OUT VARCHAR2) AS

and the body is changed accordingly, then the signature changes, because the parameter `HIRE_DATE` has a different datatype.

However, if the name of that parameter changes to `WHEN_HIRED`, and the datatype remains `VARCHAR2`, and the mode remains `OUT`, then the signature does not change. Changing the name of a formal parameter does not change the signature of the unit.

Consider the following example:

    CREATE OR REPLACE PACKAGE emp_package AS
        TYPE emp_data_type IS RECORD (
            emp_number NUMBER,
            hire_date  VARCHAR2(12),
            emp_name   VARCHAR2(10));
        PROCEDURE get_emp_data
            (emp_data IN OUT emp_data_type);
    END;

    CREATE OR REPLACE PACKAGE BODY emp_package AS
        PROCEDURE get_emp_data
            (emp_data IN OUT emp_data_type) IS
    BEGIN
        SELECT empno, ename, to_char(hiredate, 'DD/MON/YY')
            INTO emp_data
            FROM emp
            WHERE empno = emp_data.emp_number;
    END;

If the package specification is changed so that the record's field names are changed, but the types remain the same, this does not affect the signature. For example, the following package specification has the same signature as the previous package specification example:

    CREATE OR REPLACE PACKAGE emp_package AS
        TYPE emp_data_type IS RECORD (
            emp_num    NUMBER,         -- was emp_number
            hire_dat   VARCHAR2(12),   --was hire_date
            empname    VARCHAR2(10));  -- was emp_name
        PROCEDURE get_emp_data
            (emp_data IN OUT emp_data_type);
    END;

Changing the name of the type of a parameter does not cause a change in the signature if the type remains the same as before. For example, the following package specification for `EMP_PACKAGE` is the same as the first one on page 10-23:

    CREATE OR REPLACE PACKAGE emp_package AS
        TYPE emp_data_record_type IS RECORD (
            emp_number NUMBER,
            hire_date  VARCHAR2(12),
            emp_name   VARCHAR2(10));
        PROCEDURE get_emp_data
            (emp_data IN OUT emp_data_record_type);
    END;

### Controlling Remote Dependencies

Whether the timestamp or the signature dependency model is in effect is controlled by the dynamic initialization parameter `REMOTE_DEPENDENCIES_MODE`.

- If the initialization parameter file contains the specification

      REMOTE_DEPENDENCIES_MODE = TIMESTAMP

  and this is not explicitly overridden dynamically, then only timestamps are used to resolve dependencies.

- If the initialization parameter file contains the parameter specification

      REMOTE_DEPENDENCIES_MODE = SIGNATURE

  and this not explicitly overridden dynamically, then signatures are used to resolve dependencies.

- You can alter the mode dynamically by using the DDL commands

      ALTER SESSION SET REMOTE_DEPENDENCIES_MODE =
          {SIGNATURE | TIMESTAMP}

  to alter the dependency model for the current session, or

      ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE =
          {SIGNATURE | TIMESTAMP}

  to alter the dependency model on a system-wide basis after startup.

If the `REMOTE_DEPENDENCIES_MODE` parameter is not specified, either in the `INIT`.`ORA` parameter file, or using the `ALTER` `SESSION` or `ALTER` `SYSTEM` DDL commands, `TIMESTAMP` is the default value. So, unless you explicitly use the `REMOTE_DEPENDENCIES_MODE` parameter, or the appropriate DDL command, your server is operating using the timestamp dependency model.

When you use `REMOTE_DEPENDENCIES_MODE`=`SIGNATURE` you should be aware of the following:

- If you change the default value of a parameter of a remote procedure, the local procedure calling the remote procedure is not invalidated. If the call to the remote procedure does not supply the parameter, the default value is used. In this case, because invalidation/recompilation does not automatically occur, the old default value is used. If you wish to see the new default values, you must recompile the calling procedure manually.
- If you add a new overloaded procedure in a package (a new procedure with the same name as an existing one), local procedures that call the remote procedure are not invalidated. If it turns out that this overloading ought to result in a rebinding of existing calls from the local procedure under the `TIMESTAMP` mode, this rebinding does not happen under the `SIGNATURE` mode, because the local procedure does not get invalidated. You must recompile the local procedure manually to achieve the new rebinding.
- If the types of parameters of an existing packaged procedure are changed so that the new types have the same shape as the old ones, the local calling procedure is not invalidated/recompiled automatically. You must recompile the calling procedure manually to get the semantics of the new type.

#### Dependency Resolution

When `REMOTE_DEPENDENCIES_MODE` = `TIMESTAMP` (the default value), dependencies among library units are handled by comparing timestamps at runtime. If the timestamp of a called remote procedure does not match the timestamp of the called procedure, the calling (dependent) unit is invalidated, and must be recompiled. In this case, if there is no local PL/SQL compiler, the calling application cannot proceed.

In the timestamp dependency mode, signatures are not compared. If there is a local PL/SQL compiler, recompilation happens automatically when the calling procedure is executed.

When `REMOTE_DEPENDENCIES_MODE` = `SIGNATURE`, the recorded timestamp in the calling unit is first compared to the current timestamp in the called remote unit. If they match, then the call proceeds normally. If the timestamps do not match, then the signature of the called remote subprogram, as recorded in the calling subprogram, is compared with the current signature of the called subprogram. If they do not match, using the criteria described in the section "What Is a Signature?" on page 10-20, then an error is returned to the calling session.

### Suggestions for Managing Dependencies

Oracle recommends that you follow these guidelines for setting the `REMOTE_DEPENDENCIES_MODE` parameter:

- Server-side PL/SQL users can set the parameter to `TIMESTAMP` (or let it default to that) to get the timestamp dependency mode.
- Server-side PL/SQL users can choose to use the signature dependency mode if they have a distributed system and wish to avoid possible unnecessary recompilations.
- Client-side PL/SQL users should set the parameter to `SIGNATURE`. This allows
  - installation of new applications at client sites, without the need to recompile procedures
  - ability to upgrade the server, without encountering timestamp mismatches.
- When using `SIGNATURE` mode on the server side, make sure to add new procedures to the end of the procedure (or function) declarations in a package spec. Adding a new procedure in the middle of the list of declarations can cause unnecessary invalidation and recompilation of dependent procedures.

## Cursor Variables

Cursor variables are references to cursors. A cursor is a static object; a cursor variable is a pointer to a cursor. Since cursor variables are pointers, they can be passed and returned as parameters to procedures and functions. A cursor variable can also refer to ("point to") different cursors in its lifetime.

Some additional advantages of cursor variables are

- Encapsulation: queries are centralized in the stored procedure that opens the cursor variable.
- Ease of maintenance: if you need to change the cursor, you only need to make the change in one place: the stored procedure. There is no need to change each application.
- Convenient security: the user of the application is the username used when the application connects to the server. The user must have execute permission on the stored procedure that opens the cursor. But the user does not need to have read permission on the tables used in the query. This capability can be used to limit access to the columns in the table, as well as access to other stored procedures.

See the PL/SQL User's Guide and Reference for a complete discussion of cursor variables.

### Declaring and Opening Cursor Variables

You normally allocate memory for a cursor variable in the client application, using the appropriate `ALLOCATE` command. In Pro*C, you use the `EXEC` `SQL` `ALLOCATE` <cursor_name> command. In the OCI, you use the Cursor Data Area.

You can also use cursor variables in applications that run entirely in a single server session. You can declare cursor variables in PL/SQL subprograms, open them, and use them as parameters for other PL/SQL subprograms.

### Examples of Cursor Variables

This section includes several examples of cursor variable usage in PL/SQL. For additional cursor variable examples that use the programmatic interfaces, see the following manuals:

- Pro*C/C++ Precompiler Programmer's Guide
- Programmer's Guide to the Oracle Precompilers
- Programmer's Guide to the Oracle Call Interface
- SQL*Module User's Guide and Reference

#### Fetching Data

The following package defines a PL/SQL cursor variable type `EMP_VAL_CV_TYPE`, and two procedures. The first procedure opens the cursor variable, using a bind variable in the `WHERE` clause. The second procedure (`FETCH_EMP_DATA`) fetches rows from the `EMP` table using the cursor variable.

    CREATE OR REPLACE PACKAGE emp_data AS

      TYPE emp_val_cv_type IS REF CURSOR RETURN emp%ROWTYPE;

      PROCEDURE open_emp_cv (emp_cv      IN OUT emp_val_cv_type,
                             dept_number     IN INTEGER);
      PROCEDURE fetch_emp_data (emp_cv       IN emp_val_cv_type,
                                emp_row     OUT emp%ROWTYPE);

    END emp_data;

    CREATE OR REPLACE PACKAGE BODY emp_data AS

      PROCEDURE open_emp_cv (emp_cv      IN OUT emp_val_cv_type,
                             dept_number     IN INTEGER) IS
      BEGIN
        OPEN emp_cv FOR SELECT * FROM emp WHERE deptno = dept_number;
      END open_emp_cv;

      PROCEDURE fetch_emp_data (emp_cv       IN emp_val_cv_type,
                                emp_row     OUT emp%ROWTYPE) IS
      BEGIN
        FETCH emp_cv INTO emp_row;
      END fetch_emp_data;
    END emp_data;

The following example shows how you can call the `EMP_DATA` package procedures from a PL/SQL block:

    DECLARE
    -- declare a cursor variable
      emp_curs emp_data.emp_val_cv_type;

      dept_number dept.deptno%TYPE;
      emp_row emp%ROWTYPE;

    BEGIN
      dept_number := 20;

    -- open the cursor using a variable
      emp_data.open_emp_cv(emp_curs, dept_number);

    -- fetch the data and display it
      LOOP
        emp_data.fetch_emp_data(emp_curs, emp_row);
        EXIT WHEN emp_curs%NOTFOUND;
        DBMS_OUTPUT.PUT(emp_row.ename || '  ');
        DBMS_OUTPUT.PUT_LINE(emp_row.sal);
      END LOOP;
    END;

#### Implementing Variant Records

The power of cursor variables comes from their ability to point to different cursors. In the following package example, a discriminant is used to open a cursor variable to point to one of two different cursors:

    CREATE OR REPLACE PACKAGE emp_dept_data AS

      TYPE cv_type IS REF CURSOR;

      PROCEDURE open_cv (cv          IN OUT cv_type,
                         discrim     IN     POSITIVE);

    END emp_dept_data;
    /

    CREATE OR REPLACE PACKAGE BODY emp_dept_data AS

      PROCEDURE open_cv (cv      IN OUT cv_type,
                         discrim IN     POSITIVE) IS

      BEGIN
        IF discrim = 1 THEN
          OPEN cv FOR SELECT * FROM emp WHERE sal > 2000;
        ELSIF discrim = 2 THEN
          OPEN cv FOR SELECT * FROM dept;
        END IF;
      END open_cv;

    END emp_dept_data;

You can call the `OPEN_CV` procedure to open the cursor variable and point it to either a query on the `EMP` table or on the `DEPT` table. How would you use this? The following PL/SQL block shows that you can fetch using the cursor variable, then use the `ROWTYPE_MISMATCH` predefined exception to handle either fetch:

    DECLARE
      emp_rec  emp%ROWTYPE;
      dept_rec dept%ROWTYPE;
      cv       emp_dept_data.cv_type;

    BEGIN
      emp_dept_data.open_cv(cv, 1); -- open CV for EMP fetch
      FETCH cv INTO dept_rec;       -- but fetch into DEPT record
                                    -- which raises ROWTYPE_MISMATCH
      DBMS_OUTPUT.PUT(dept_rec.deptno);
      DBMS_OUTPUT.PUT_LINE('  ' || dept_rec.loc);

    EXCEPTION
      WHEN ROWTYPE_MISMATCH THEN
        BEGIN
          DBMS_OUTPUT.PUT_LINE
               ('Row type mismatch, fetching EMP data...');
          FETCH cv into emp_rec;
          DBMS_OUTPUT.PUT(emp_rec.deptno);
          DBMS_OUTPUT.PUT_LINE('  ' || emp_rec.ename);
        END;
    END;

## Hiding PL/SQL Code

You can deliver your stored procedures in object code format using the PL/SQL Wrapper. Wrapping your PL/SQL code hides your application internals. To run the PL/SQL Wrapper, enter the WRAP command at your system prompt using the following syntax:

    WRAP INAME=input_file [ONAME=ouput_file]

See Also: For complete instructions on using the PL/SQL Wrapper, see the PL/SQL User's Guide and Reference.

## Error Handling

Oracle allows user-defined errors in PL/SQL code to be handled so that user-specified error numbers and messages are returned to the client application. Once received, the client application can handle the error based on the user-specified error number and message returned by Oracle.

User-specified error messages are returned using the `RAISE_APPLICATION_ERROR` procedure:

    RAISE_APPLICATION_ERROR(error_number, 'text', keep_error_stack)

This procedure terminates procedure execution, rolls back any effects of the procedure, and returns a user-specified error number and message (unless the error is trapped by an exception handler). `ERROR_NUMBER` must be in the range of -20000 to -20999. Error number -20000 should be used as a generic number for messages where it is important to relay information to the user, but having a unique error number is not required. `TEXT` must be a character expression, 2 Kbytes or less (longer messages are ignored). `KEEP_ERROR_STACK` can be `TRUE`, if you want to add the error to any already on the stack, or `FALSE`, if you want to replace the existing errors. By default, this option is `FALSE`.

Note: Some of the Oracle-supplied packages, such as `DBMS_OUTPUT`, `DBMS_DESCRIBE`, and `DBMS_ALERT`, use application error numbers in the range -20000 to -20005. See the descriptions of these packages for more information.

The `RAISE_APPLICATION_ERROR` procedure is often used in exception handlers or in the logic of PL/SQL code. For example, the following exception handler selects the string for the associated user-defined error message and calls the `RAISE_APPLICATION_ERROR` procedure:

    ...
    WHEN NO_DATA_FOUND THEN
       SELECT error_string INTO message
       FROM error_table,
       V$NLS_PARAMETERS V
       WHERE error_number = -20101 AND LANG = v.value AND
          v.name = "NLS_LANGUAGE";
       raise_application_error(-20101, message);
    ...

Several examples earlier in this chapter also demonstrate the use of the `RAISE_APPLICATION_ERROR` procedure. The next section has an example of passing a user-specified error number from a trigger to a procedure. For information on exception handling when calling remote procedures, see "Handling Errors in Remote Procedures" on page 10-35.

### Declaring Exceptions and Exception Handling Routines

User-defined exceptions are explicitly defined and signaled within the PL/SQL block to control processing of errors specific to the application. When an exception is raised (signaled), the normal execution of the PL/SQL block stops and a routine called an exception handler is invoked. Specific exception handlers can be written to handle any internal or user-defined exception.

Application code can check for a condition that requires special attention using an `IF` statement. If there is an error condition, two options are available:

- Issue a `RAISE` statement that names the appropriate exception. A `RAISE` statement stops the execution of the procedure and control passes to an exception handler (if any).
- Call the `RAISE_APPLICATION_ERROR` procedure to return a user-specified error number and message.

You can also define an exception handler to handle user-specified error messages. For example, Figure 10-2 illustrates

- an exception and associated exception handler in a procedure
- a conditional statement that checks for an error (such as transferring funds not available) and issues a user-specified error number and message within a trigger
- how user-specified error numbers are returned to the calling environment (in this case, a procedure) and how that application can define an exception that corresponds to the user-specified error number

Declare a user-defined exception in a procedure or package body (private exceptions) or in the specification of a package (public exceptions). Define an exception handler in the body of a procedure (stand-alone or package).

#### Figure 10-2 Exceptions and User-Defined Errors

### Unhandled Exceptions

In database PL/SQL program units, an unhandled user-error condition or internal error condition that is not trapped by an appropriate exception handler causes the implicit rollback of the program unit. If the program unit includes a `COMMIT` statement before the point at which the unhandled exception is observed, the implicit rollback of the program unit can only be completed back to the previous commit.

Additionally, unhandled exceptions in database-stored PL/SQL program units propagate back to client-side applications that call the containing program unit. In such an application, only the application program unit call is rolled back (not the entire application program unit) because it is submitted to the database as a SQL statement.

If unhandled exceptions in database PL/SQL program units are propagated back to database applications, the database PL/SQL code should be modified to handle the exceptions. Your application can also trap for unhandled exceptions when calling database program units and handle such errors appropriately. For more information, see "Handling Errors in Remote Procedures" on page 10-35.

### Handling Errors in Distributed Queries

You can use a trigger or stored procedure to create a distributed query. This distributed query is decomposed by the local Oracle into a corresponding number of remote queries, which are sent to the remote nodes for execution. The remote nodes execute the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.

If a portion of a distributed statement fails, for example, due to an integrity constraint violation, Oracle returns error number `ORA-02055`. Subsequent statements or procedure calls return error number`ORA-02067` until a rollback or rollback to savepoint is issued.

You should design your application to check for any returned error messages that indicate that a portion of the distributed update has failed. If you detect a failure, you should rollback the entire transaction (or rollback to a savepoint) before allowing the application to proceed.

### Handling Errors in Remote Procedures

When a procedure is executed locally or at a remote location, four types of exceptions can occur:

- PL/SQL user-defined exceptions, which must be declared using the keyword `EXCEPTION`
- PL/SQL predefined exceptions, such as `NO_DATA_FOUND`
- SQL errors, such as `ORA-00900` and `ORA-02015`
- Application exceptions, which are generated using the `RAISE_APPLICATION_ERROR`() procedure

When using local procedures, all of these messages can be trapped by writing an exception handler, such as shown in the following example:

    EXCEPTION
        WHEN ZERO_DIVIDE THEN
        /* ...handle the exception */

Notice that the `WHEN` clause requires an exception name. If the exception that is raised does not have a name, such as those generated with `RAISE_APPLICATION_ERROR`, one can be assigned using `PRAGMA_EXCEPTION_INIT`, as shown in the following example:

    DECLARE
        ...
        null_salary EXCEPTION;
        PRAGMA EXCEPTION_INIT(null_salary, -20101);
    BEGIN
        ...
        RAISE_APPLICATION_ERROR(-20101, 'salary is missing');
        ...
    EXCEPTION
        WHEN null_salary THEN
            ...

When calling a remote procedure, exceptions are also handled by creating a local exception handler. The remote procedure must return an error number to the local, calling procedure, which then handles the exception as shown in the previous example. Because PL/SQL user-defined exceptions always return `ORA-06510` to the local procedure, these exceptions cannot be handled. All other remote exceptions can be handled in the same manner as local exceptions.

### Compile Time Errors

When you use SQL*Plus to submit PL/SQL code, and the code contains errors, you receive notification that compilation errors have occurred, but no immediate indication of what the errors are. For example, if you submit a stand-alone (or stored) procedure PROC1 in the file proc1.sql as follows:

    SVRMGR> @proc1

and there are one or more errors in the code, you receive a notice such as

    MGR-00072: Warning: Procedure PROC1 created with compilation errors

In this case, use the `SHOW` `ERRORS` command in SQL*Plus to get a list of the errors that were found. `SHOW` `ERRORS` with no argument lists the errors from the most recent compilation. You can qualify `SHOW` `ERRORS` using the name of a procedure, function, package, or package body:

    SQL> SHOW ERRORS PROC1
    SQL> SHOW ERRORS PROCEDURE PROC1

See the SQL*Plus User's Guide and Reference for complete information about the `SHOW` `ERRORS` command.

Note: Before issuing the `SHOW` `ERRORS` command, use the `SET` `CHARWIDTH` command to get long lines on output. The value 132 is usually a good choice:
    SET CHARWIDTH 132

For example, assume you want to create a simple procedure that deletes records from the employee table using SQL*Plus:

    CREATE PROCEDURE fire_emp(emp_id NUMBER) AS
       BEGIN
          DELETE FROM emp WHER empno = emp_id;
       END
    /

Notice that the `CREATE` `PROCEDURE` statement has two errors: the `DELETE` statement has an error (the 'E' is absent from `WHERE`) and the semicolon is missing after `END`.

After the `CREATE` `PROCEDURE` statement is issued and an error is returned, a `SHOW` `ERRORS` statement would return the following lines:

    SHOW ERRORS;

    ERRORS FOR PROCEDURE FIRE_EMP:
    LINE/COL       ERROR
    -------------- --------------------------------------------
    3/27           PL/SQL-00103: Encountered the symbol "EMPNO" wh. . .
    5/0            PL/SQL-00103: Encountered the symbol "END" when . . .
    2 rows selected.

Notice that each line and column number where errors were found is listed by the `SHOW` `ERRORS` command.

Alternatively, you can query the following data dictionary views to list errors when using any tool or application:

- `USER_ERRORS`
- `ALL_ERRORS`
- `DBA_ERRORS`

The error text associated with the compilation of a procedure is updated when the procedure is replaced, and deleted when the procedure is dropped.

Original source code can be retrieved from the data dictionary using the following views: `ALL_SOURCE`, `USER_SOURCE`, and `DBA_SOURCE`.

See Also: Oracle8 Reference

for more information about these data dictionary views.

### Debugging

You can debug stored procedures and triggers using the `DBMS_OUTPUT` supplied package. You put `PUT` and `PUT_LINE` statements in your code to output the value of variables and expressions to your terminal. See "Output from Stored Procedures and Triggers" on page 12-22 for more information about the `DBMS_OUTPUT` package.

A more convenient way to debug, if your platform supports it, is to use the Oracle Procedure Builder, which is part of the Oracle Developer/2000 tool set. Procedure Builder lets you execute PL/SQL procedures and triggers in a controlled debugging environment, and you can set breakpoints, list the values of variables, and perform other debugging tasks. See the Oracle Procedure Builder Developer's Guide for more information.

## Invoking Stored Procedures

Procedures can be invoked from many different environments. For example:

- A procedure can be called within the body of another procedure or a trigger.
- A procedure can be interactively called by a user using an Oracle tool (such as SQL*Plus)
- A procedure can be explicitly called within an application (such as a SQL*Forms or precompiler application).
- A stored function can be called from a SQL statement in a manner similar to calling a built-in SQL function, such as `LENGTH` or `ROUND`.

Some common examples of invoking procedures from within these environments follow. For more information, see "Calling Stored Functions from SQL Expressions" on page 10-44.

### A Procedure or Trigger Calling Another Procedure

A procedure or trigger can call another stored procedure. For example, included in the body of one procedure might be the line

    . . .
    sal_raise(emp_id, 200);
    . . .

This line calls the `SAL_RAISE` procedure. `EMP_ID` is a variable within the context of the procedure. Note that recursive procedure calls are allowed within PL/SQL; that is, a procedure can call itself.

### Interactively Invoking Procedures From Oracle Tools

A procedure can be invoked interactively from an Oracle tool such as SQL*Plus. For example, to invoke a procedure named `SAL_RAISE`, owned by you, you can use an anonymous PL/SQL block, as follows:

    BEGIN
        sal_raise(1043, 200);
    END;

Note: Interactive tools such as SQL*Plus require that you follow these lines with a slash (/) to execute the PL/SQL block.

An easier way to execute a block is to use the SQL*Plus command `EXECUTE`, which effectively wraps `BEGIN` and `END` statements around the code you enter. For example:

    EXECUTE sal_raise(1043, 200);

Some interactive tools allow session variables to be created. For example, when using SQL*Plus, the following statement creates a session variable:

    VARIABLE assigned_empno NUMBER

Once defined, any session variable can be used for the duration of the session. For example, you might execute a function and capture the return value using a session variable:

    EXECUTE :assigned_empno := hire_emp('JSMITH', 'President', \
       1032, SYSDATE, 5000, NULL, 10);
    PRINT assigned_empno;
    ASSIGNED_EMPNO
    --------------
              2893

See the SQL*Plus User's Guide and Reference for SQL*Plus information. See your tools manual for information about performing similar operations using your development tool.

### Calling Procedures within 3GL Applications

A 3GL database application such as a precompiler or OCI application can include a call to a procedure within the code of the application.

To execute a procedure within a PL/SQL block in an application, simply call the procedure. The following line within a PL/SQL block calls the `FIRE_EMP` procedure:

    fire_emp(:empno);

In this case, :`EMPNO` is a host (bind) variable within the context of the application.

To execute a procedure within the code of a precompiler application, you must use the `EXEC` call interface. For example, the following statement calls the `FIRE_EMP` procedure in the code of a precompiler application:

    EXEC SQL EXECUTE
       BEGIN
          fire_emp(:empno);
       END;
    END-EXEC;

:`EMPNO` is a host (bind) variable.

For more information about calling PL/SQL procedures from within 3GL applications, see the following manuals:

- Oracle Call Interface Programmer's Guide
- Pro*C/C++ Precompiler Programmer's Guide,
- SQL*Module for Ada Programmer's Guide

### Name Resolution When Invoking Procedures

References to procedures and packages are resolved according to the algorithm described in "Name Resolution in SQL Statements" on page 4-46.

### Privileges Required to Execute a Procedure

If you are the owner of a stand-alone procedure or package, you can execute the stand-alone procedure or packaged procedure, or any public procedure or packaged procedure at any time, as described in the previous sections. If you want to execute a stand-alone or packaged procedure owned by another user, the following conditions apply:

- You must have the `EXECUTE` privilege for the stand-alone procedure or package containing the procedure, or have the `EXECUTE` `ANY` `PROCEDURE` system privilege. If you are executing a remote procedure, you must have been granted the `EXECUTE` privilege or `EXECUTE` `ANY` `PROCEDURE` system privilege directly, not via a role.
- You must include the owner's name in the call, as in:

      EXECUTE jward.fire_emp (1043);

      EXECUTE jward.hire_fire.fire_emp (1043);

Note: A stored subprogram or package executes in the privilege domain of the owner of the procedure. The owner must have been explicitly granted the necessary object privileges to all objects referenced within the body of the code.

### Specifying Values for Procedure Arguments

When you invoke a procedure, specify a value or parameter for each of the procedure's arguments. Identify the argument values using either of the following methods, or a combination of both:

- List the values in the order the arguments appear in the procedure declaration.
- Specify the argument names and corresponding values, in any order.

For example, these statements each call the procedure `UPDATE_SAL` to increase the salary of employee number 7369 by 500:

    sal_raise(7369, 500);

    sal_raise(sal_incr=>500, emp_id=>7369);

    sal_raise(7369, sal_incr=>500);

The first statement identifies the argument values by listing them in the order in which they appear in the procedure specification.

The second statement identifies the argument values by name and in an order different from that of the procedure specification. If you use argument names, you can list the arguments in any order.

The third statement identifies the argument values using a combination of these methods. If you use a combination of order and argument names, values identified in order must precede values identified by name.

If you have used the `DEFAULT` option to define default values for `IN` parameters to a subprogram (see the PL/SQL User's Guide and Reference), you can pass different numbers of actual parameters to the 1subprogram, accepting or overriding the default values as you please. If an actual value is not passed, the corresponding default value is used. If you want to assign a value to an argument that occurs after an omitted argument (for which the corresponding default is used), you must explicitly designate the name of the argument, as well as its value.

### Invoking Remote Procedures

Invoke remote procedures using an appropriate database link and the procedure's name. The following SQL*Plus statement executes the procedure `FIRE_EMP` located in the database pointed to by the local database link named `NY`:

    EXECUTE fire_emp@NY(1043);

See Also: For information on exception handling when calling remote procedures, see page 10-35.

#### Remote Procedure Calls and Parameter Values

You must explicitly pass values to all remote procedure parameters even if there are defaults. You cannot access remote package variables and constants.

### Referencing Remote Objects

Remote objects can be referenced within the body of a locally defined procedure. The following procedure deletes a row from the remote employee table:

    CREATE PROCEDURE fire_emp(emp_id NUMBER) IS
    BEGIN
        DELETE FROM emp@sales WHERE empno = emp_id;
    END;

The list below explains how to properly call remote procedures, depending on the calling environment.

- Remote procedures (stand-alone and packaged) can be called from within a procedure, OCI application, or precompiler application by specifying the remote procedure name, a database link, and the arguments for the remote procedure.

      CREATE PROCEDURE local_procedure(arg1, arg2) AS
      BEGIN
        ...
        remote_procedure@dblink(arg1, arg2);
        ...
      END;

- In the previous example, you could create a synonym for `REMOTE_PROCEDURE`@`DBLINK`. This would enable you to call the remote procedure from an Oracle tool application, such as a SQL*Forms application, as well from within a procedure, OCI application, or precompiler application.

      CREATE PROCEDURE local_procedure(arg1, arg2) AS
      BEGIN
        ...
        synonym(arg1, arg2);
        ...
      END;

- If you did not want to use a synonym, you could write a local cover procedure to call the remote procedure.

      BEGIN local_procedure(arg1, arg2);
      END;

  Here, `LOCAL_PROCEDURE` is defined as in the first item of this list.

  Note: Synonyms can be used to create location transparency for the associated remote procedures.

  WARNING: Unlike stored procedures, which use compile-time binding, runtime binding is used when referencing remote procedures. The user account to which you connect depends on the database link.

All calls to remotely stored procedures are assumed to perform updates; therefore, this type of referencing always requires two-phase commit of that transaction (even if the remote procedure is read-only). Furthermore, if a transaction that includes a remote procedure call is rolled back, the work done by the remote procedure is also rolled back. A procedure called remotely cannot execute a `COMMIT`, `ROLLBACK`, or `SAVEPOINT` statement.

A distributed update modifies data on two or more nodes. A distributed update is possible using a procedure that includes two or more remote updates that access data on different nodes. Statements in the construct are sent to the remote nodes and the execution of the construct succeeds or fails as a unit. If part of a distributed update fails and part succeeds, a rollback (of the entire transaction or to a savepoint) is required to proceed. Consider this when creating procedures that perform distributed updates.

Pay special attention when using a local procedure that calls a remote procedure. If a timestamp mismatch is found during execution of the local procedure, the remote procedure is not executed and the local procedure is invalidated.

### Synonyms for Procedures and Packages

Synonyms can be created for stand-alone procedures and packages to

- hide the identity of the name and owner of a procedure or package
- provide location transparency for remotely stored procedures (stand-alone or within a package)

When a privileged user needs to invoke a procedure, an associated synonym can be used. Because the procedures defined within a package are not individual objects (that is, the package is the object), synonyms cannot be created for individual procedures within a package.

## Calling Stored Functions from SQL Expressions

You can include user-written PL/SQL functions in SQL expressions. (You must be using PL/SQL release 2.1 or greater.) By using PL/SQL functions in SQL statements, you can do the following:

- Increase user productivity by extending SQL. Expressiveness of the SQL statement increases where activities are too complex, too awkward, or unavailable with SQL.
- Increase query efficiency. Functions used in the `WHERE` clause of a query can filter data using criteria that would otherwise have to be evaluated by the application.
- Manipulate character strings to represent special datatypes (for example, latitude, longitude, or temperature).
- Provide parallel query execution. If the query is parallelized, SQL statements in your PL/SQL function may be executed in parallel also (using the parallel query option).

### Using PL/SQL Functions

PL/SQL functions must be created as top-level functions or declared within a package specification before they can be named within a SQL statement. Stored PL/SQL functions are used in the same manner as built-in Oracle functions (such as `SUBSTR` or `ABS`).

PL/SQL functions can be placed wherever an Oracle function can be placed within a SQL statement; that is, wherever expressions can occur in SQL. For example, they can be called from the following:

- the select list of the `SELECT` command
- the condition of the `WHERE` and `HAVING` clause
- the `CONNECT` `BY`, `START` `WITH`, `ORDER` `BY`, and `GROUP` `BY` clauses
- the `VALUES` clause of the `INSERT` command
- the `SET` clause of the `UPDATE` command

You cannot call stored PL/SQL functions from a `CHECK` constraint clause of a `CREATE` or `ALTER` `TABLE` command or use them to specify a default value for a column. These situations require an unchanging definition.

Note: Unlike functions, which are called as part of an expression, procedures are called as statements. Therefore, PL/SQL procedures are not directly callable from SQL statements. However, functions called from a PL/SQL statement or referenced in a SQL expression can call a PL/SQL procedure.

### Syntax

Use the following syntax to reference a PL/SQL function from SQL:

    [[schema.]package.]function_name[@dblink][(param_1...param_n)]

For example, to reference a function that you have created that is called `MY_FUNC`, in the `MY_FUNCS_PKG` package, in the `SCOTT` schema, and that takes two numeric parameters, you could call it as:

    SELECT scott.my_funcs_pkg.my_func(10,20) from dual

### Naming Conventions

If only one of the optional schema or package names is given, the first identifier can be either a schema name or a package name. For example, to determine whether `PAYROLL` in the reference `PAYROLL`.`TAX_RATE` is a schema or package name, Oracle proceeds as follows:

- Oracle first checks for the `PAYROLL` package in the current schema.
- If a `PAYROLL` package is not found, Oracle looks for a schema named `PAYROLL` that contains a top-level `TAX_RATE` function. If the `TAX_RATE` function is not found in the `PAYROLL` schema, an error message is returned.
- If the `PAYROLL` package is found in the current schema, Oracle looks for a `TAX_RATE` function in the `PAYROLL` package. If a `TAX_RATE` function is not found in the `PAYROLL` package, an error message is returned.

You can also refer to a stored top-level function using any synonym that you have defined for it.

#### Name Precedence

In SQL statements, the names of database columns take precedence over the names of functions with no parameters. For example, if schema `SCOTT` creates the following two objects:

    CREATE TABLE emp(new_sal NUMBER ...);
    CREATE FUNCTION new_sal RETURN NUMBER IS ...;

Then in the following two statements, the reference to `NEW_SAL` refers to the column `EMP`.`NEW_SAL`:

    SELECT new_sal FROM emp;
    SELECT emp.new_sal FROM emp;

To access the function `NEW_SAL`, you would enter the following:

    SELECT scott.new_sal FROM emp;

##### Example

For example, to call the `TAX_RATE` PL/SQL function from schema `SCOTT`, execute it against the `SS_NO` and `SAL` columns in `TAX_TABLE`, and place the results in the variable `INCOME_TAX`, specify the following:

    SELECT scott.tax_rate (ss_no, sal)
        INTO income_tax
        FROM tax_table
        WHERE ss_no = tax_id;

These sample calls to PL/SQL functions are allowed in SQL expressions:

    circle_area(radius)
    payroll.tax_rate(empno)
    scott.payroll.tax_rate(dependents, empno)@ny

#### Arguments

To pass any number of arguments to a function, supply the arguments within the parentheses. You must use positional notation; named notation is not currently supported. For functions that do not accept arguments, omit the parentheses.

The argument's datatypes and the function's return type are limited to those types that are supported by SQL. For example, you cannot call a PL/SQL function that returns a PL/SQL `BINARY_INTEGER` from a SQL statement.

#### Using Default Values

The stored function `gross_pay` initializes two of its formal parameters to default values using the `DEFAULT` clause, as follows:

    CREATE FUNCTION gross_pay
        (emp_id IN NUMBER,
        st_hrs IN NUMBER DEFAULT 40,
        ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS
      ...

When calling gross_pay from a procedural statement, you can always accept the default value of `st_hrs`. That is because you can use named notation, which lets you skip parameters, as in:

    IF gross_pay(eenum,ot_hrs => otime) > pay_limit THEN ...

However, when calling gross_pay from a SQL expression, you cannot accept the default value of `st_hrs` unless you accept the default value of `ot_hrs`. That is because you cannot use named notation.

### Meeting Basic Requirements

To be callable from SQL expressions, a user-defined PL/SQL function must meet the following basic requirements:

- It must be a stored function, not a function defined within a PL/SQL block or subprogram.
- It must be a row function, not a column (group) function; that is, it cannot take an entire column of data as its argument.
- All its formal parameters must be `IN` parameters; none can be an `OUT` or `IN` `OUT` parameter.
- The datatypes of its formal parameters must be Oracle Server internal types such as `CHAR`, `DATE`, or `NUMBER`, not PL/SQL types such as `BOOLEAN`, `RECORD`, or `TABLE`.
- Its return type (the datatype of its result value) must be an Oracle Server internal type.

For example, the following stored function meets the basic requirements:

    CREATE FUNCTION gross_pay
          (emp_id IN NUMBER,
           st_hrs IN NUMBER DEFAULT 40,
           ot_hrs IN NUMBER DEFAULT 0) RETURN NUMBER AS
       st_rate  NUMBER;
       ot_rate  NUMBER;

    BEGIN
       SELECT srate, orate INTO st_rate, ot_rate FROM payroll
          WHERE acctno = emp_id;
       RETURN st_hrs * st_rate + ot_hrs * ot_rate;
    END gross_pay;

### Controlling Side Effects

To execute a SQL statement that calls a stored function, the Oracle Server must know the purity level of the function, that is, the extent to which the function is free of side effects. In this context, side effects are references to database tables or packaged variables.

Side effects can prevent the parallelization of a query, yield order-dependent (and therefore indeterminate) results, or require that package state be maintained across user sessions (which is not allowed). Therefore, the following rules apply to stored functions called from SQL expressions:

- The function cannot modify database tables; therefore, it cannot execute an `INSERT`, `UPDATE`, or `DELETE` statement.
- Functions that read or write the values of packaged variables cannot be executed remotely or in parallel.
- Only functions called from a `SELECT`, `VALUES`, or `SET` clause can write the values of packaged variables.
- The function cannot call another subprogram that breaks one of the foregoing rules. Also, the function cannot reference a view that breaks one of the foregoing rules. (Oracle replaces references to a view with a stored `SELECT` operation, which can include function calls.)

For stand-alone functions, Oracle can enforce these rules by checking the function body. However, the body of a packaged function is hidden; only its specification is visible. So, for packaged functions, you must use the pragma (compiler directive) `RESTRICT_REFERENCES` to enforce the rules.

The pragma tells the PL/SQL compiler to deny the packaged function read/write access to database tables, packaged variables, or both. If you try to compile a function body that violates the pragma, you get a compilation error.

#### Calling Packaged Functions

To call a packaged function from SQL expressions, you must assert its purity level by coding the pragma `RESTRICT_REFERENCES` in the package specification (not in the package body). The pragma must follow the function declaration but need not follow it immediately. Only one pragma can reference a given function declaration.

To code the pragma `RESTRICT_REFERENCES`, you use the syntax

    PRAGMA RESTRICT_REFERENCES (
        function_name, WNDS [, WNPS] [, RNDS] [, RNPS]);

where:

`WNDS` | means "writes no database state" (does not modify database tables)
`RNDS` | means "reads no database state" (does not query database tables)
`WNPS` | means "writes no package state" (does not change the values of packaged variables)
`RNPS` | means "reads no package state" (does not reference the values of packaged variables)

You can pass the arguments in any order, but you must pass the argument `WNDS`. No argument implies another; for example, `RNPS` does not imply `WNPS`.

In the example below, the function compound neither reads nor writes database or package state, so you can assert the maximum purity level. Always assert the highest purity level that a function allows. That way, the PL/SQL compiler will never reject the function unnecessarily.

    CREATE PACKAGE finance AS  -- package specification
       ...
       FUNCTION compound
             (years  IN NUMBER,
              amount IN NUMBER,
              rate   IN NUMBER) RETURN NUMBER;
       PRAGMA RESTRICT_REFERENCES (compound, WNDS, WNPS, RNDS, RNPS);
    END finance;

    CREATE PACKAGE BODY finance AS  --package body
       ...
       FUNCTION compound
             (years  IN NUMBER,
              amount IN NUMBER,
              rate   IN NUMBER) RETURN NUMBER IS

       BEGIN
          RETURN amount * POWER((rate / 100) + 1, years);
       END compound;
                       -- no pragma in package body
    END finance;

Later, you might call compound from a PL/SQL block, as follows:

    BEGIN
       ...
        SELECT finance.compound(yrs,amt,rte)  -- function call       INTO
    interest       FROM accounts       WHERE acctno = acct_id;

#### Referencing Packages with an Initialization Part

Packages can have an initialization part, which is hidden in the package body. Typically, the initialization part holds statements that initialize public variables.

In the following example, the `SELECT` statement initializes the public variable `prime_rate`:

    CREATE PACKAGE loans AS
       prime_rate  REAL;  -- public packaged variable
       ...
    END loans;

    CREATE PACKAGE BODY loans AS
       ...
    BEGIN  -- initialization part
       SELECT prime INTO prime_rate FROM rates;
    END loans;

The initialization code is run only once-the first time the package is referenced. If the code reads or writes database state or package state other than its own, it can cause side effects. Moreover, a stored function that references the package (and thereby runs the initialization code) can cause side effects indirectly. So, to call the function from SQL expressions, you must use the pragma `RESTRICT_REFERENCES` to assert or imply the purity level of the initialization code.

To assert the purity level of the initialization code, you use a variant of the pragma `RESTRICT_REFERENCES`, in which the function name is replaced by a package name. You code the pragma in the package specification, where it is visible to other users. That way, anyone referencing the package can see the restrictions and conform to them.

To code the variant pragma `RESTRICT_REFERENCES`, you use the syntax

    PRAGMA RESTRICT_REFERENCES (
        package_name, WNDS [, WNPS] [, RNDS] [, RNPS]);

where the arguments `WNDS`, `WNPS`, `RNDS`, and `RNPS` have the usual meaning.

In the example below, the initialization code reads database state and writes package state. However, you can assert `WNPS` because the code is writing the state of its own package, which is permitted. So, you assert `WNDS`, `WNPS`, `RNPS`-the highest purity level the function allows. (If the public variable `prime_rate` were in another package, you could not assert `WNPS`.)

    CREATE PACKAGE loans AS
        PRAGMA RESTRICT_REFERENCES (loans, WNDS, WNPS, RNPS);
        prime_rate  REAL;
        ...
    END loans;

    CREATE PACKAGE BODY loans AS
    ...
    BEGIN
        SELECT prime INTO prime_rate FROM rates;
    END loans;

You can place the pragma anywhere in the package specification, but placing it at the top (where it stands out) is a good idea.

To imply the purity level of the initialization code, your package must have a `RESTRICT_REFERENCES` pragma for one of the functions it declares. From the pragma, Oracle can infer the purity level of the initialization code (because the code cannot break any rule enforced by a pragma). In the next example, the pragma for the function discount implies that the purity level of the initialization code is at least `WNDS`:

    CREATE PACKAGE loans AS
        ...
        FUNCTION discount (...) RETURN NUMBER;
        PRAGMA RESTRICT_REFERENCES (discount, WNDS);
    END loans;
    ...

To draw an inference, Oracle can combine the assertions of all `RESTRICT_REFERENCES` pragmas. For example, the following pragmas (combined) imply that the purity level of the initialization code is at least `WNDS`, `RNDS`:

    CREATE PACKAGE loans AS
        ...
        FUNCTION discount (...) RETURN NUMBER;
        FUNCTION credit_ok (...) RETURN CHAR;
        PRAGMA RESTRICT_REFERENCES (discount, WNDS);
        PRAGMA RESTRICT_REFERENCES (credit_ok, RNDS);
    END loans;
    ...

#### Avoiding Problems

To call a packaged function from SQL expressions, you must assert its purity level using the pragma `RESTRICT_REFERENCES`. However, if the package has an initialization part, the PL/SQL compiler might not let you assert the highest purity level the function allows. As a result, you might be unable to call the function remotely, in parallel, or from certain SQL clauses.

This happens when a packaged function is purer than the package initialization code. Remember, the first time a package is referenced,

its initialization code is run. If that reference is a function call, any additional side effects caused by the initialization code occur during the call. So, in effect, the initialization code lowers the purity level of the function.

To avoid this problem, move the package initialization code into a subprogram. That way, your application can run the code explicitly (rather than implicitly during package instantiation) without affecting your packaged functions.

A similar problem arises when a packaged function is purer than a subprogram it calls. This lowers the purity level of the function. Therefore, the `RESTRICT_REFERENCES` pragma for the function must specify the lower purity level. Otherwise, the PL/SQL compiler will reject the function. In the following example, the compiler rejects the function because its pragma asserts `RNDS` but the function calls a procedure that reads database state:

    CREATE PACKAGE finance AS
       ...
       FUNCTION compound (years  IN NUMBER,
                          amount IN NUMBER) RETURN NUMBER;
       PRAGMA RESTRICT_REFERENCES (compound, WNDS, WNPS, RNDS, RNPS);
    END finance;

    CREATE PACKAGE BODY finance AS
       ...
       FUNCTION compound (years  IN NUMBER,
                          amount IN NUMBER) RETURN NUMBER IS
          rate  NUMBER;
          PROCEDURE calc_loan_rate (loan_rate OUT NUMBER) IS
             prime_rate REAL;
          BEGIN
             SELECT p_rate INTO prime_rate FROM rates;
             ...
          END;
       BEGIN
          calc_loan_rate(rate);
          RETURN amount * POWER((rate / 100) + 1, years);
       END compound;
    END finance;

### Overloading

PL/SQL lets you overload packaged (but not stand-alone) functions. That is, you can use the same name for different functions if their formal parameters differ in number, order, or datatype family.

However, a `RESTRICT_REFERENCES` pragma can apply to only one function declaration. So, a pragma that references the name of overloaded functions always applies to the nearest foregoing function declaration.

In the following example, the pragma applies to the second declaration of valid:

    CREATE PACKAGE tests AS
        FUNCTION valid (x NUMBER) RETURN CHAR;
        FUNCTION valid (x DATE) RETURN CHAR;
        PRAGMA RESTRICT_REFERENCES (valid, WNDS);
        ...

### Serially Reusable PL/SQL Packages

PL/SQL packages normally consume user global area (UGA) memory corresponding to the number of package variables and cursors in the package. This limits scalability because the memory increases linearly with the number of users. The solution is to allow some packages to be marked as `SERIALLY_REUSABLE` (using pragma syntax).

For serially reusable packages, the package global memory is not kept in the UGA per user, but instead it is kept in a small pool and reused for different users. This means that the global memory for such a package is only used within a unit of work. At the end of that unit of work, the memory can therefore be released to the pool to be reused by another user (after running the initialization code for all the global variables).

The unit of work for serially reusable packages is implicitly a `CALL` to the server, for example, an OCI call to the server, or a PL/SQL client-to-server RPC call or server-to-server RPC call.

#### Package States

The state of a nonreusable package (one not marked `SERIALLY_REUSABLE`) persists for the lifetime of a session. A package's state includes global variables, cursors, and so on.

The state of a serially reusable package persists only for the lifetime of a `CALL` to the server. On a subsequent call to the server, if a reference is made to the serially reusable package, Oracle creates a new instantiation (described below) of the serially reusable package and initializes all the global variables to `NULL` or to the default values provided. Any changes made to the serially reusable package state in the previous `CALL`s to the server are not visible.

Note: Creating a new instantiation of a serially reusable package on a `CALL` to the server does not necessarily imply that Oracle allocates memory or configures the instantiation object. Oracle simply looks for an available instantiation work area (which is allocated and configured) for this package in a least-recently used (LRU) pool in SGA. At the end of the `CALL` to the server this work area is returned back to the LRU pool. The reason for keeping the pool in the SGA is that the work area can be reused across users who have requests for the same package.

#### Why Serially Reusable Packages?

Since the state of a non-reusable package persists for the lifetime of the session, this locks up UGA memory for the whole session. In applications such as Oracle Office a log-on session can typically exist for days together. Applications often need to use certain packages only for certain localized periods in the session and would ideally like to de-instantiate the package state in the middle of the session once they are done using the package.

With `SERIALLY_REUSABLE` packages the application developers have a way of modelling their applications to manage their memory better for scalability. Package state that they care about only for the duration of a `CALL` to the server should be captured in `SERIALLY_REUSABLE` packages.

#### Syntax

A package can be marked serially reusable by a pragma. The syntax of the pragma is:

    PRAGMA SERIALLY_REUSABLE;

A package specification can be marked serially reusable whether or not it has a corresponding package body. If the package has a body, the body must have the serially reusable pragma if its corresponding specification has the pragma; and it cannot have the serially reusable pragma unless the specification also has the pragma.

#### Semantics

A package that is marked `SERIALLY_REUSABLE` has the following properties:

- Its package variables are meant for use only within the `WORK` boundaries, which correspond to `CALL`s to the server (either OCI call boundaries or PL/SQL RPC calls to the server).

  Note: If the application programmer makes a mistake and depends on a package variable that is set in a previous unit of work, the application program can fail. PL/SQL cannot check for such cases.

- A pool of package instantiations is kept and whenever a "unit of work" needs this package, one of the instantiations is "reused" as follows:
  - The package variables are reinitialized (for example, if the package variables have default values, then those values are reinitialized).
  - The initialization code in the package body is executed again.
- At the "end work" boundary, cleanup is done.
  - If any cursors were left open, they are silently closed.
  - Some non-reusable secondary memory is freed (such as memory for collection variables or long `VARCHAR2`s).
  - This package instantiation is returned back to the pool of reusable instantiations kept for this package.
- Serially reusable packages cannot be accessed from within triggers. If you attempt to access a serially reusable package from a trigger, Oracle issues the error message "cannot access Serially Reusable package <string> in the context of a trigger."

#### Example 1

This example has a serially reusable package specification (there is no body). It demonstrates how package variables behave across `CALL` boundaries.

    connect scott/tiger;

    create or replace package SR_PKG is
      pragma SERIALLY_REUSABLE;
      n number := 5;                -- default initialization
    end SR_PKG;
    /

Suppose your Enterprise Manager (or SQL*Plus) application issues the following:

    connect scott/tiger

    # first CALL to server
    begin
     SR_PKG.n := 10;
    end;
    /

    # second CALL to server
    begin
     dbms_output.put_line(SR_PKG.n);
    end;
    /

The above program will print:

    5

Note: If the package had not had the pragma `SERIALLY_REUSABLE`, the program would have printed '10'.

#### Example 2

This example has both a package specification and body which are serially reusable. Like Example 1, this example demonstrates how the package variables behave across `CALL` boundaries.

    SQL> connect scott/tiger;
    Connected.
    SQL>
    SQL> drop package SR_PKG;
    Statement processed.
    SQL>
    SQL> create or replace package SR_PKG is
         2>
         3>  pragma SERIALLY_REUSABLE;
         4>
         5>  type str_table_type is table of varchar2(200) index by binary_integer;
         6>
         7>  num     number        := 10;
         8>  str     varchar2(200) := 'default-init-str';
         9>  str_tab str_table_type;
        10>
        11>  procedure print_pkg;
        12>  procedure init_and_print_pkg(n number, v varchar2);
        13>
        14> end SR_PKG;
        15> /
    Statement processed.
    SQL>
    SQL>
    SQL> create or replace package body SR_PKG is
         2>
         3>  -- the body is required to have the pragma since the
         4>  -- specification of this package has the pragma
         5>  pragma SERIALLY_REUSABLE;
         6>
         7>  procedure print_pkg is
         8>  begin
         9>    dbms_output.put_line('num: ' || SR_PKG.num);
        10>    dbms_output.put_line('str: ' || SR_PKG.str);
        11>
        12>    dbms_output.put_line('number of table elems: ' ||
    SR_PKG.str_tab.count);
        13>    for i in 1..SR_PKG.str_tab.count loop
        14>      dbms_output.put_line(SR_PKG.str_tab(i));
        15>    end loop;
        16>  end;
        17>
        18>  procedure init_and_print_pkg(n number, v varchar2) is
        19>  begin
        20>
        21>    -- init the package globals
        22>    SR_PKG.num := n;
        23>    SR_PKG.str := v;
        24>    for i in 1..n loop
        25>      SR_PKG.str_tab(i) := v || ' ' || i;
        26>    end loop;
        27>
        28>    -- now print the package
        29>    print_pkg;
        30>  end;
        31>
        32> end SR_PKG;
        33> /
    Statement processed.
    SQL> show errors;
    No errors for PACKAGE BODY SR_PKG
    SQL>
    SQL> set serveroutput on;
    Server Output                   ON
    SQL>
    SQL> Rem SR package access in a CALL
    SQL> begin
         2>
         3>   -- initialize and print the package
         4>   dbms_output.put_line('Initing and printing pkg state..');
         5>   SR_PKG.init_and_print_pkg(4, 'abracadabra');
         6>
         7>   -- print it in the same call to the server.
         8>   -- we should see the initialized values.
         9>   dbms_output.put_line('Printing package state in the same CALL...');
        10>   SR_PKG.print_pkg;
        11>
        12> end;
        13> /
    Statement processed.
    Initing and printing pkg state..
    num: 4
    str: abracadabra
    number of table elems: 4
    abracadabra 1
    abracadabra 2
    abracadabra 3
    abracadabra 4
    Printing package state in the same CALL...
    num: 4
    str: abracadabra
    number of table elems: 4
    abracadabra 1
    abracadabra 2
    abracadabra 3
    abracadabra 4
    SQL>
    SQL> Rem SR package access in subsequent CALL
    SQL> begin
         2>
         3>  -- print the package in the next call to the server.
         4>  -- We should that the package state is reset to the initial (default)
    values.
         5>  dbms_output.put_line('Printing package state in the next CALL...');
         6>  SR_PKG.print_pkg;
         7>
         8> end;
         9> /
    Statement processed.
    Printing package state in the next CALL...
    num: 10
    str: default-init-str
    number of table elems: 0
    SQL>

#### Example 3

This example demonstrates that any open cursors in serially reusable packages get closed automatically at the end of a `WORK` boundary (which is a `CALL`), and that in a new `CALL` these cursors need to be opened again.

    Rem  For serially reusable pkg: At the end work boundaries
    Rem  (which is currently the OCI call boundary) all open
    Rem  cursors will be closed.
    Rem
    Rem  Since the cursor is closed - every time we fetch we
    Rem  will start at the first row again.

    SQL> connect scott/tiger;
    Connected.
    SQL>
    SQL> drop package  SR_PKG;
    Statement processed.
    SQL> drop table people;
    Statement processed.
    SQL>
    SQL>
    SQL> create table people (name varchar2(20));
    Statement processed.
    SQL>
    SQL> insert into  people  values ('ET');
    1 row processed.
    SQL> insert into  people  values ('RAMBO');
    1 row processed.
    SQL>
    SQL> create or replace package SR_PKG is
         2>
         3>  pragma SERIALLY_REUSABLE;
         4>  cursor c is select name from people;
         5>
         6> end SR_PKG;
         7> /
    Statement processed.
    SQL> show errors;
    No errors for PACKAGE SR_PKG
    SQL>
    SQL> set serveroutput on;
    Server Output                   ON
    SQL>
    SQL> create or replace procedure fetch_from_cursor is
         2>   name varchar2(200);
         3> begin
         4>
         5>  if (SR_PKG.c%ISOPEN) then
         6>   dbms_output.put_line('cursor is already open.');
         7>  else
         8>   dbms_output.put_line('cursor is closed; opening now.');
         9>   open SR_PKG.c;
        10>  end if;
        11>
        12>  -- fetching from cursor.
        13>  fetch SR_PKG.c into name;
        14>  dbms_output.put_line('fetched: ' || name);
        15>
        16>  fetch SR_PKG.c into name;
        17>  dbms_output.put_line('fetched: ' || name);
        18>
        19>  -- Oops forgot to close the cursor (SR_PKG.c).
        20>  -- But, since it is a Serially Reusable pkg's cursor,
        21>  -- it will be closed at the end of this CALL to the server.
        22>
        23> end;
        24> /
    Statement processed.
    SQL> show errors;
    No errors for PROCEDURE FETCH_FROM_CURSOR
    SQL>
    SQL> set serveroutput on;
    Server Output                   ON
    SQL>
    SQL> execute fetch_from_cursor;
    Statement processed.
    cursor is closed; opening now.
    fetched: ET
    fetched: RAMBO
    SQL>
    SQL> execute fetch_from_cursor;
    Statement processed.
    cursor is closed; opening now.
    fetched: ET
    fetched: RAMBO
    SQL>
    SQL> execute fetch_from_cursor;
    Statement processed.
    cursor is closed; opening now.
    fetched: ET
    fetched: RAMBO
    SQL>

### Privileges Required

To call a PL/SQL function from SQL, you must either own or have `EXECUTE` privileges on the function. To select from a view defined with a PL/SQL function, you are required to have `SELECT` privileges on the view. No separate `EXECUTE` privileges are needed to select from the view.

## Supplied Packages

Several packaged procedures are provided with the Oracle Server, either to extend the functionality of the database or to give PL/SQL access to some SQL features. You may take advantage of the functionality provided by these packages when creating your application, or you may simply want to use these packages for ideas in creating your own stored procedures.

This section lists each of the supplied packages and indicates where they are described in more detail. These packages run as the invoking user rather than the package owner. The packaged procedures are callable through public synonyms of the same name.

### Packages Supporting SQL Features

Oracle supplies the following packaged procedures to give PL/SQL access to some features of SQL:

- `DBMS_DDL`
- `DBMS_SESSION`
- `DBMS_TRANSACTION`
- `DBMS_UTILITY`

Table 10-6 describes each of these packages. The footnotes at the end of Table 10-6 explain any restrictions on the use of each procedure. You should consult the package specifications for the most up-to-date information on these packages.

##### Table 10-3 DBMS_DDL

Package | Procedure(Arguments) | SQL Command Equivalent
--- | --- | ---
`DBMS_DDL` |
     alter_compile(type varchar2,
                  schema varchar2,
                  name varchar2)
     (notes 1, 2, 3, 4)
| `ALTER` `PROCEDURE` Pro*C `COMPILE`
`ALTER` `FUNCTION` func `COMPILE`
`ALTER` `PACKAGE` pack `COMPILE`
     analyze_object(type varchar2,
      schema varchar2,
      name   varchar2,
      method varchar2,
      estimate_rows number
       default  null,
      estimate_percent number
      default null)
| `ANALYZE` `INDEX`
`ANALYZE` `TABLE`
`ANALYZE` `CLUSTER`

Note 1: not allowed in triggers
Note 2: not allowed in procedures called from SQL*Forms
Note 3: not allowed in read-only transactions
Note 4: not allowed in remote (coordinated) sessions
Note 5: not allowed in recursive sessions
Note 6: not allowed in stored procedures

##### Table 10-4 DBMS_SESSION

Package | Procedure(Arguments) | SQL Command Equivalent
--- | --- | ---
`DBMS`_ `SESSION` |
     close_database_link(
                 dblink varchar2)
| `ALTER` `SESSION` `CLOSE` `DATABASE` dblink
     reset_package (see note 5)
| This procedure reinitializes the state of all packages; there is no `SQL` equivalent
     set_nls(param varchar2,
       value varchar2)
     (notes 1,4)
| `ALTER SESSION SET` `nls_param =` `nls_param_values`
     set_role(role_cmd varchar2)
     (notes 1, 6)
| `SET` `ROLE` ...
     set_sql_trace(sql_trace
      boolean)
| `ALTER` `SESSION` `SET` `SQL_TRACE` = [`TRUE` | `FALSE`]
     unique_session_id
          return varchar2
| This function returns a unique session `ID`; there is no `SQL` equivalent.
     is_role_enabled
          return boolean
| This function is used to determine if a role is enabled; there is no `SQL` equivalent.
     et_close_cached_open_cursors(
      close_cursors
      boolean)
| `ALTER` `SESSION` `SET` `CLOSE_CACHED_OPEN`_ `CURSORS`
     free_unused_user_memory
| This procedure lets you reclaim unused memory; there is no SQL equivalent.

Note 1: not allowed in triggers
Note 2: not allowed in procedures called from SQL*Forms
Note 3: not allowed in read-only transactions
Note 4: not allowed in remote (coordinated) sessions
Note 5: not allowed in recursive sessions
Note 6: not allowed in stored procedures

##### Table 10-5 DBMS_TRANSACTION

Package | Procedure(Arguments) | SQL Command Equivalent
--- | --- | ---
`DBMS`_ `TRANSACTION` |
     advise_commit
     ALTER SESSION ADVISE COMMIT
     dvise_rollback
     ALTER SESSION ADVISE ROLLBACK
     advise_nothing
     ALTER SESSION ADVISE NOTHING
     commit  (notes 1,2,4)
     COMMIT
     commit_Comment(cmnt varchar2)
     (notes 1,2,4)
     COMMIT COMMENT text
     commit_force(xid
    varchar2,
     scn varchar2
     default null)
     (notes 1,2,3,4)
     COMMIT FORCE text ...
     read_only  (notes 1,3,4)
     SET TRANSACTION READ ONLY
     read_write  (notes 1,3,4)
     SET TRANSACTION READ WRITE
     rollback  (notes 1,2,4)
     ROLLBACK
     rollback_force(xid varchar2)
     (notes 1,2,3,4)
     ROLLBACK ... FORCE text ...
     rollback_savepoint(
                 svpt varchar2)
     (notes 1,2,4)
     ROLLBACK ... TO SAVEPOINT ...
     savepoint(savept varchar2)
     (notes 1,2,4)
     SAVEPOINT savepoint
     use_rollback_segment(
      rb_name varchar2)
     (notes 1,2,4)
     SET TRANSACTION USE ROLLBACK SEGMENT segment
     purge_mixed(xid in number)
| See Also: Oracle8 Distributed Database Systems
     begin_discrete_transaction
     (notes 1,3,4,5)
| See Also: Oracle8 Tuning
     local_transaction_id(
     create_transaction BOOLEAN
                    default FALSE)
      return VARCHAR2
| See Also: Oracle8 Distributed Database Systems
     step_id return number
| See Also: Oracle8 Distributed Database Systems

##### Table 10-6 DBMS_UTILITYs

Package | Procedure(Arguments) | SQL Command Equivalent
--- | --- | ---
`` `` `` `` `` `DBMS_UTILITY` |
    compile_schema(schema
         varchar2)
    (notes 1,2,3,4)
| This procedure is equivalent to calling `alter_compile` on all procedures, functions, and packages accessible by you. Compilation is completed in dependency order.
    analyze_schema(
      schema varchar2,
      method varchar2,
      estimate_rows number
    default null,
      estimate_percent number
                    default  null)
| This procedure is equivalent to calling `analyze_object` on all objects in the given schema.
    analyze_part_object(
      schema in varchar2 default
    null,
      object_name in varchar2
              default null,
      object_type in char default
    'T',
      command_type in char
              default 'E',
      command_opt in varchar2
              default null,
      sample_clause in varchar2
                   default
    'sample 5 percent')
| `ANALYZE` `TABLE` | `INDEX` [<schema>.]<object_name> `PARTITION` <pname> [<command_type>] [<command_opt>] [<sample_clause>]" for each partition of the object, run in parallel using job queues. This procedure submits a job for each partition; you can control the number of concurrent jobs with the initialization parameter `JOB_QUEUE_PROCESSES`. Object_type must be T (table) or I (index). Command_type can be: - C (compute statistics) - E (estimate statistics) - D (delete statistics) - V (validate structure). For V, command_opt can be 'CASCADE' when object_type is T. For C or E, command_opt can be FOR table, FOR all LOCAL indexes, FOR all columns or a combination of some of the 'for' options of analyze statistics (table). Sample_clause specifies the sample clause to use when command_type is E.
    format_error_stack
           return varchar2
| This function formats the error stack into a variable.
`DBMS_UTILITY` (continued) |
    format_error_stack

           return varchar2
| This function formats the error stack into a variable.
    format_call_stack

           return varchar2
| This function formats the current call stack into a variable.
    name_resolve(name in
    varchar2,
      context in number,
      schema out varchar2,
      part1 out varchar2,
      part2 out varchar2,
      dblink out varchar2,
      part1_type out number,
      object_number out number)
| See Also: Oracle8 Distributed Database Systems.

Note 1: not allowed in triggers
Note 2: not allowed in procedures called from SQL*Forms
Note 3: not allowed in read-only transactions
Note 4: not allowed in remote (coordinated) sessions
Note 5: not allowed in recursive sessions
Note 6: not allowed in stored procedures

For more details on each SQL command equivalent, see the Oracle8 SQL Reference.

The `COMMIT`, `ROLLBACK`, `ROLLBACK`... `TO` `SAVEPOINT`, and `SAVEPOINT` procedures are directly supported by `PL`/SQL; they are included in the `DBMS_TRANSACTION` package for completeness.

### Packages Supporting Additional Functionality

Several packages are supplied with Oracle to extend the functionality of the database (`DBMS`_* and `UTL`_* packages). The cross-reference column in Table 10-7 tells you where to look for more information on each of these packages.

##### Table 10-7 Supplied Packages: Additional Functionality

Package Name | Description | Cross-reference
--- | --- | ---
`DBMS_ALERT` | Supports asynchronous notification of database events. | Chapter 16, "Signalling Database Events with Alerters"
`DBMS_DESCRIBE` | Lets you describe the arguments of a stored procedure. | "DBMS_DESCRIBE Package" on page 10-71
`DBMS_JOB` | Lets you schedule administrative procedures that you want performed at periodic intervals. | Oracle8 Administrator's Guide
`DBMS_LOCK` | Lets you use the Oracle Lock Management services for your applications. | "The DBMS_LOCK Package" on page 3-18
`DBMS_OUTPUT` | Lets you output messages from triggers, procedures, and packages. | "Output from Stored Procedures and Triggers" on page 12-22
`DBMS_PIPE` | Allows sessions in the same instance to communicate with each other. | Chapter 12, "PL/SQL Input/Output"
`DBMS_SHARED_POOL` | Lets you keep objects in shared memory, so that they will not be aged out with the normal `LRU` mechanism. | Oracle8 Tuning
`DBMS_APPLICATION`_ `INFO` | Lets you register an application name with the database for auditing or performance tracking purposes. | Oracle8 Tuning
`DBMS_SYSTEM` | Provides system-level utilities, such as letting you enable `SQL` trace for a session. | Oracle8 Tuning
`DBMS_SPACE` | Provides segment space information not available through standard views. | Oracle8 Administrator's Guide
`DBMS_SQL` | Lets you write stored procedures and anonymous `PL`/`SQL` blocks using dynamic `SQL`; lets you parse any `DML` or `DDL` statement. | Chapter 14, "Using Dynamic SQL"
`DBMS_ROWID` | Lets you get information about `ROWID`s, including the data block number, the object number, and other components. | "The DBMS_ROWID Package" on page 10-81
`DBMS_LOB` | Lets you manipulate large objects using `PL`/`SQL` programs running on the Oracle Server. | "DBMS_LOB Package" on page 6-68
`DBMS_AQ` | Lets you add a message (of a predefined object type) onto a queue or dequeue a message. | Chapter 11, "Advanced Queuing"
`DBMS_AQADM` | Lets you perform administrative functions on a queue or queue table for messages of a predefined object type. | Chapter 11, "Advanced Queuing"
`DBMS_DISTRIBUTED`_ `TRUST_ADMIN` | Lets you maintain the Trusted Servers List, which is used in conjunction with the list at the Central Authority to determine if a privileged database link from a particular server can be accepted. | Oracle8 Distributed Database Systems
`DMBS_HS` | Lets you administer Heterogeneous Services by registering or dropping distributed external procedures, remote libraries, and non-Oracle systems. Also lets you create or drop some initialization variables for non-Oracle systems. | Oracle8 Distributed Database Systems
`DMBS_HS_EXTPROC` | Lets you use Heterogeneous Services to establish security for distributed external procedures. | Oracle8 Distributed Database Systems
`DMBS_HS`_ `PASSTHROUGH` | Lets you use Heterogeneous Services to send pass-through `SQL` statements to non-Oracle systems. | Oracle8 Distributed Database Systems
`DBMS_REFRESH` | Lets you create groups of snapshots that can be refreshed together to a transactionally consistent point in time. Use of this feature requires the distributed option. | Oracle8 Replication
`DBMS_SNAPSHOT` | Lets you refresh one or more snapshots that are not part of the same refresh group, purge snapshot log. Use of this feature requires the distributed option. | Oracle8 Replication
`DBMS_DEFER`, `DMBS_DEFER_SYS`, `DBMS_DEFER_QUERY` | Lets you build and administer deferred remote procedure calls. Use of this feature requires the replication option. | Oracle8 Replication
`DBMS_REPCAT` | Lets you use Oracle's symmetric replication facility. Use of this feature requires the replication option. | Oracle8 Replication
`DBMS_REPCAT_AUTH`, `DBMS_REPCAT_ADMIN` | Lets you create users with the privileges needed by the symmetric replication facility. Use of this feature requires the replication option. | Oracle8 Replication
`UTL_HTTP` | Lets you make `HTTP` callouts from `PL`/`SQL` and `SQL` to access data on the Internet or to call Oracle Web Server Cartridges. | "The UTL_HTTP Package" on page 10-91

## Describing Stored Procedures

You can use the `DBMS_DESCRIBE` package to get information about a stored procedure or function.

This package provides the same functionality as the Oracle Call Interface `OCIDescribeA`ny() call. The procedure `DESCRIBE_PROCEDURE` in this package accepts the name of a stored procedure, and a description of the procedure and each of its parameters. For more information on the `OCIDescribeA`ny() call, see the Oracle Call Interface Programmer's Guide.

### DBMS_DESCRIBE Package

To create the `DBMS_DESCRIBE` package, submit the `DBMSDESC`.`SQL` and `PRVTDESC`.`PLB` scripts when connected as the user `SYS`. These scripts are run automatically by the `CATPROC`.`SQL` script. See "Privileges Required to Execute a Procedure" on page 10-40 for information on the necessary privileges for users who will be executing this package.

### Security

This package is available to `PUBLIC` and performs its own security checking based on the schema object being described.

### Types

The `DBMS_DESCRIBE` package declares two PL/SQL table types, which are used to hold data returned by `DESCRIBE_PROCEDURE` in its `OUT` parameters. The types are

    TYPE VARCHAR2_TABLE IS TABLE OF VARCHAR2(30)
        INDEX BY BINARY_INTEGER;

    TYPE NUMBER_TABLE IS TABLE OF NUMBER
        INDEX BY BINARY_INTEGER;

### Errors

`DBMS_DESCRIBE` can raise application errors in the range -20000 to -20004. The errors are

    -20000: ORU 10035: cannot describe a package ('X') only a
            procedure within a package
    -20001: ORU-10032: procedure 'X' within package 'Y' does not
            exist
    -20002: ORU-10033 object 'X' is remote, cannot describe; expanded
            name 'Y'
    -20003: ORU-10036: object 'X' is invalid and cannot be described
    -20004: syntax error attempting to parse 'X'

### DESCRIBE_PROCEDURE Procedure

#### Syntax

The parameters for `DESCRIBE_PROCEDURE` are shown in Table 10-8. The syntax is:

    PROCEDURE DESCRIBE_PROCEDURE(
            object_name    IN VARCHAR2,
            reserved1      IN VARCHAR2,
            reserved2      IN VARCHAR2,
            overload       OUT NUMBER_TABLE,
            position       OUT NUMBER_TABLE,
            level          OUT NUMBER_TABLE,
            argument_name  OUT VARCHAR2_TABLE,
            datatype       OUT NUMBER_TABLE,
            default_value  OUT NUMBER_TABLE,
            in_out         OUT NUMBER_TABLE,
            length         OUT NUMBER_TABLE,
            precision      OUT NUMBER_TABLE,
            scale          OUT NUMBER_TABLE,
            radix          OUT NUMBER_TABLE
            spare          OUT NUMBER_TABLE);

##### Table 10-8 DBMS_DESCRIBE.DESCRIBE_PROCEDURE Parameters

Parameter | Mode | Description
--- | --- | ---
`object_name` | IN | The name of the procedure being described. The syntax for this parameter follows the rules used for identifiers in SQL. The name can be a synonym. This parameter is required and may not be null. The total length of the name cannot exceed 197 bytes. An incorrectly specified OBJECT_NAME can result in one of the following exceptions: ORA-20000 - A package was specified. You can only specify a stored procedure, stored function, packaged procedure, or packaged function. ORA-20001 - The procedure or function that you specified does not exist within the given package. ORA-20002 - The object that you specified is a remote object. This procedure cannot currently describe remote objects. ORA-20003 - The object that you specified is invalid and cannot be described. ORA-20004 - The object was specified with a syntax error.
`reserved1 reserved2` | IN | Reserved for future use. Must be set to null or the empty string.
`overload` | OUT | A unique number assigned to the procedure's signature. If a procedure is overloaded, this field holds a different value for each version of the procedure.
`position` | OUT | Position of the argument in the parameter list. Position 0 returns the values for the return type of a function.
`level` | OUT | If the argument is a composite type, such as record, this parameter returns the level of the datatype. See the Programmer's Guide to the Oracle Call Interface write-up of the ODESSP call for an example of its use.
`argument_name` | OUT | The name of the argument associated with the procedure that you are describing.
`datatype` | OUT | The Oracle datatype of the argument being described. The datatypes and their numeric type codes are: 0 placeholder for procedures with no arguments 1 VARCHAR, VARCHAR, STRING 2 NUMBER, INTEGER, SMALLINT, REAL, FLOAT, DECIMAL 3 BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL 8 LONG 11 ROWID 12 DATE 23 RAW 24 LONG RAW 96 CHAR (ANSI FIXED CHAR), CHARACTER 106 MLSLABEL 250 PL/SQL RECORD 251 PL/SQL TABLE 252 PL/SQL BOOLEAN
`default_value` | OUT | 1 if the argument being described has a default value; otherwise, the value is 0.
`in_out` | OUT | Describes the mode of the parameter: 0 IN 1 OUT 2 IN OUT
`length` | OUT | The data length, in bytes, of the argument being described.
`precision` | OUT | If the argument being described is of datatype 2 (NUMBER), this parameter is the precision of that number.
`scale` | OUT | If the argument being described is of datatype 2 (NUMBER, etc.), this parameter is the scale of that number.
`radix` | OUT | If the argument being described is of datatype 2 (NUMBER, etc.), this parameter is the radix of that number.
`spare` | OUT | Reserved for future functionality.

#### Return Values

All values from `DESCRIBE_PROCEDURE` are returned in its `OUT` parameters. The datatypes for these are PL/SQL tables, to accommodate a variable number of parameters.

#### Examples

One use of the `DESCRIBE_PROCEDURE` procedure would be as an external service interface.

For example, consider a client that provides an `OBJECT` _`NAME` of `SCOTT`.`ACCOUNT_UPDATE` where `ACCOUNT_UPDATE` is an overloaded function with specification:

    table account (account_no number, person_id number,
                   balance number(7,2))
    table person  (person_id number(4), person_nm varchar2(10))

    function ACCOUNT_UPDATE (account_no   number,
                             person       person%rowtype,
                             amounts      dbms_describe.number_table,
                             trans_date   date)
                             return       accounts.balance%type;

    function ACCOUNT_UPDATE (account_no   number,
                             person       person%rowtype,
                             amounts      dbms_describe.number_table,
                             trans_no     number)
                                 return       accounts.balance%type;

The describe of this procedure might look similar to the output shown below.

    overload position  argument level  datatype length prec scale rad
    -------- --------- -------- ------ -------- ------ ---- ----- ---
           1        0               0         2     22    7     2  10
           1        1   ACCOUNT     0         2      0    0     0   0
           1        2   PERSON      0       250      0    0     0   0
           1        1   PERSON_ID   1         2     22    4     0  10
           1        2   PERSON_NM   1         1     10    0     0   0
           1        3   AMOUNTS     0       251      0    0     0   0
           1        1               1         2     22    0     0   0
           1        4   TRANS_DATE  0        12      0    0     0   0
           2        0               0         2     22    7     2  10
           2        1   ACCOUNT_NO  0         2     22    0     0   0
           2        2   PERSON      0         2     22    4     0  10
           2        3   AMOUNTS     0       251     22    4     0  10
           2        1               1         2      0    0     0   0
           2        4   TRANS_NO    0         2      0    0     0   0

The following PL/SQL procedure has as its parameters all of the PL/SQL datatypes:

    CREATE OR REPLACE PROCEDURE p1 (
            pvc2    IN     VARCHAR2,
            pvc     OUT    VARCHAR,
            pstr    IN OUT STRING,
            plong   IN     LONG,
            prowid  IN     ROWID,
            pchara  IN     CHARACTER,
            pchar   IN     CHAR,
            praw    IN     RAW,
            plraw   IN     LONG RAW,
            pbinint IN     BINARY_INTEGER,
            pplsint IN     PLS_INTEGER,
            pbool   IN     BOOLEAN,
            pnat    IN     NATURAL,
            ppos    IN     POSITIVE,
            pposn   IN     POSITIVEN,
            pnatn   IN     NATURALN,
            pnum    IN     NUMBER,
            pintgr  IN     INTEGER,
            pint    IN     INT,
            psmall  IN     SMALLINT,
            pdec    IN     DECIMAL,
            preal   IN     REAL,
            pfloat  IN     FLOAT,
            pnumer  IN     NUMERIC,
            pdp     IN     DOUBLE PRECISION,
            pdate   IN     DATE,
            pmls    IN     MLSLABEL) AS

    BEGIN
        NULL;
    END;

If you describe this procedure using the package below:

    CREATE OR REPLACE PACKAGE describe_it AS

        PROCEDURE desc_proc (name VARCHAR2);

    END describe_it;

    CREATE OR REPLACE PACKAGE BODY describe_it AS

      PROCEDURE prt_value(val VARCHAR2, isize INTEGER) IS
        n INTEGER;
      BEGIN
        n := isize - LENGTHB(val);
        IF n < 0 THEN
          n := 0;
        END IF;
        DBMS_OUTPUT.PUT(val);
        FOR i in 1..n LOOP
          DBMS_OUTPUT.PUT(' ');
        END LOOP;
      END prt_value;

      PROCEDURE desc_proc (name VARCHAR2) IS

          overload     DBMS_DESCRIBE.NUMBER_TABLE;
          position     DBMS_DESCRIBE.NUMBER_TABLE;
          c_level      DBMS_DESCRIBE.NUMBER_TABLE;
          arg_name     DBMS_DESCRIBE.VARCHAR2_TABLE;
          dty          DBMS_DESCRIBE.NUMBER_TABLE;
          def_val      DBMS_DESCRIBE.NUMBER_TABLE;
          p_mode       DBMS_DESCRIBE.NUMBER_TABLE;
          length       DBMS_DESCRIBE.NUMBER_TABLE;
          precision    DBMS_DESCRIBE.NUMBER_TABLE;
          scale        DBMS_DESCRIBE.NUMBER_TABLE;
          radix        DBMS_DESCRIBE.NUMBER_TABLE;
          spare        DBMS_DESCRIBE.NUMBER_TABLE;
          idx          INTEGER := 0;

      BEGIN
          DBMS_DESCRIBE.DESCRIBE_PROCEDURE(
                  name,
                  null,
                  null,
                  overload,
                  position,
                  c_level,
                  arg_name,
                  dty,
                  def_val,
                  p_mode,
                  length,
                  precision,
                  scale,
                  radix,
                  spare);

          DBMS_OUTPUT.PUT_LINE('Position    Name        DTY  Mode');
          LOOP
              idx := idx + 1;
              prt_value(TO_CHAR(position(idx)), 12);
              prt_value(arg_name(idx), 12);
              prt_value(TO_CHAR(dty(idx)), 5);
              prt_value(TO_CHAR(p_mode(idx)), 5);
              DBMS_OUTPUT.NEW_LINE;
          END LOOP;
      EXCEPTION
         WHEN NO_DATA_FOUND THEN
            DBMS_OUTPUT.NEW_LINE;
            DBMS_OUTPUT.NEW_LINE;

      END desc_proc;
    END describe_it;

Then the results, as shown below, list all the numeric codes for the PL/SQL datatypes:

    Position  Name    Datatype_Code  Mode
    1         PVC2      1              0
    2         PVC       1              1
    3         PSTR      1              2
    4         PLONG     8              0
    5         PROWID    11             0
    6         PCHARA    96             0
    7         PCHAR     96             0
    8         PRAW      23             0
    9         PLRAW     24             0
    10        PBININT   3              0
    11        PPLSINT   3              0
    12        PBOOL     252            0
    13        PNAT      3              0
    14        PPOS      3              0
    15        PPOSN     3              0
    16        PNATN     3              0
    17        PNUM      2              0
    18        PINTGR    2              0
    19        PINT      2              0
    20        PSMALL    2              0
    21        PDEC      2              0
    22        PREAL     2              0
    23        PFLOAT    2              0
    24        PNUMER    2              0
    25        PDP       2              0
    26        PDATE     12             0
    27        PMLS      106            0

## Listing Information about Procedures and Packages

The following data dictionary views provide information about procedures and packages:

- ALL_ERRORS, USER_ERRORS, DBA_ERRORS
- ALL_SOURCE, USER_SOURCE, DBA_SOURCE
- USER_OBJECT_SIZE, DBA_OBJECT_SIZE

The OBJECT_SIZE views show the sizes of the PL/SQL objects. For a complete description of these data dictionary views, see your Oracle8 Reference.

The following statements are used in Examples 1 through 3:

    CREATE PROCEDURE fire_emp(emp_id NUMBER) AS
    BEGIN
       DELETE FROM em WHERE empno = emp_id;
    END;
    /
    CREATE PROCEDURE hire_emp (name VARCHAR2, job VARCHAR2,
       mgr NUMBER, hiredate DATE, sal NUMBER,
       comm NUMBER, deptno NUMBER)

    IS
    BEGIN
       INSERT INTO emp VALUES (emp_sequence.NEXTVAL, name,
          job, mgr, hiredate, sal, comm, deptno);
    END;
    /

The first CREATE PROCEDURE statement has an error in the DELETE statement. (The 'p' is absent from 'emp'.)

##### Example 1: Listing Compilation Errors for Objects

The following query returns all the errors for the objects in the associated schema:

    SELECT name, type, line, position, text
        FROM user_errors;

The following results are returned:

    NAME     TYPE LIN POS TEXT
    -------- ---- --- --- -------------------------------------
    FIRE_EMP PROC   3  15 PL/SQL-00201: identifier 'EM' must be
                                        declared
    FIRE_EMP PROC   3   3 PL/SQL: SQL Statement ignored

##### Example 2: Listing Source Code for a Procedure

The following query returns the source code for the `HIRE_EMP` procedure created in the example statement at the beginning of this section:

    SELECT line, text FROM user_source
        WHERE name = 'HIRE_EMP';

The following results are returned:

    LINE   TEXT
    ------ -----------------------------------------------------
         1 PROCEDURE hire_emp (name VARCHAR2, job VARCHAR2,
         2    mgr NUMBER, hiredate DATE, sal NUMBER,
         3    comm NUMBER, deptno NUMBER)
         4 IS
         5 BEGIN
         6    INSERT INTO emp VALUES (emp_seq.NEXTVAL, name,
         7    job, mgr, hiredate, sal, comm, deptno);
         8 END;

##### Example 3: Listing Size Information for a Procedure

The following query returns information about the amount of space in the SYSTEM tablespace that is required to store the HIRE_EMP procedure:

    SELECT name, source_size + parsed_size + code_size +
          error_size "TOTAL SIZE"
        FROM user_object_size
        WHERE name = 'HIRE_EMP';

The following results are returned:

    NAME                           TOTAL SIZE
    ------------------------------ ----------
    HIRE_EMP                             3897

## The DBMS_ROWID Package

The functions in this package let you get the information that you need about `ROWID`s. You can find out the data block number, the object number, and other components of the `ROWID` without having to write code to interpret the base-64 character external `ROWID`.

The specification for the `DBMS_ROWID` package is in the file dbmsutil.sql. This package is loaded when you create a database, and run catproc.sql.

Some of the functions in this package take a single parameter: a `ROWID`. This can be a character or a binary `ROWID`, either restricted or extended, as required. For each function described in this section, both the parameter types and the return type are described.

You can call the `DBMS_ROWID` functions and procedures from PL/SQL code, and you can also use the functions in SQL statements.

Note: `ROWID_INFO` is a procedure. It can only be used in PL/SQL code.

##### SQL Example

You can use functions from the `DBMS_ROWID` package just like any built-in SQL function. That is, you can use them wherever an expression can be used. In this example, the `ROWID_BLOCK_NUMBER` function is used to return just the block number of a single row in the `EMP` table:

    SELECT dbms_rowid.rowid_block_number(rowid)
      FROM emp
      WHERE ename = 'KING';

##### PL/SQL Example

This example returns the `ROWID` for a row in the `EMP` table, extracts the data object number from the `ROWID`, using the `ROWID_OBJECT` function in the `DBMS_ROWID` package, then displays the object number:

    DECLARE
      object_no   INTEGER;
      row_id      ROWID;
      ...
    BEGIN
      SELECT ROWID INTO row_id FROM emp
        WHERE empno = 7499;
      object_no := dbms_rowid.rowid_object(row_id);
      dbms_output.put_line('The obj. # is '|| object_no);
      ...

### Summary

##### Table 10-9 DBMS_ROWID Functions

Function Name | Description | See Page
--- | --- | ---
`ROWID_CREATE` | Create a `ROWID`, for testing only. | 83
`ROWID_INFO` | Procedure that returns the type and components of a `ROWID` | 84
`ROWID_TYPE` | Returns the `ROWID` type: 0 is restricted, 1 is extended. | 86
`ROWID_OBJECT` | Returns the object number of the extended `ROWID`. | 86
`ROWID_RELATIVE_FNO` | Returns the file number of a `ROWID`. | 87
`ROWID_BLOCK_NUMBER` | Returns the block number of a `ROWID`. | 87
`ROWID_ROW_NUMBER` | Returns the row number. | 87
`ROWID_TO_ABSOLUTE_FNO` | Returns the absolute file number associated with the `ROWID` for a row in a specific table. | 88
`ROWID_TO_EXTENDED` | Converts a `ROWID` from restricted format to extended. | 88
`ROWID_TO_RESTRICTED` | Converts an extended `ROWID` to restricted format. | 90
`ROWID_VERIFY` | Checks if a `ROWID` can be correctly extended by the `ROWID_TO_EXTENDED` function. | 90

Table 10-9 lists the functions and procedures in the DBMS_ROWID package.

### Exceptions

The `DBMS_ROWID` package functions and procedures can raise the `ROWID_INVALID` exception. The exception is defined in the `DBMS_ROWID` package as:

    PRAGMA EXCEPTION_INIT(ROWID_INVALID, -1410);

### ROWID_CREATE Function

The `ROWID_CREATE` function lets you create a `ROWID`, given the component parts as parameters. This function is mostly useful for testing `ROWID` operations, since only the Oracle Server can create a valid `ROWID` that points to data in a database.

#### Syntax

    FUNCTION DBMS_ROWID.ROWID_CREATE(
        rowid_type        IN  NUMBER,
        object_number     IN  NUMBER,
        relative_fno      IN  NUMBER,
        block_number      IN  NUMBER,
        row_number        IN  NUMBER)
      RETURN ROWID;

Set the `ROWID_TYPE` parameter to 0 for a restricted `ROWID`, and to 1 to create an extended `ROWID`.

If you specify `ROWID`_`TYPE` as 0, the required `OBJECT_NUMBER` parameter is ignored, and `ROWID_CREATE` returns a restricted `ROWID`.

#### Example

Create a dummy extended `ROWID`:

    my_rowid := DBMS_ROWID.ROWID_CREATE(1, 9999, 12, 1000, 13);

Find out what the ROWID_OBJECT function returns:

    obj_number := DBMS_ROWID.ROWID_OBJECT(my_rowid);

The variable OBJ_NUMBER now contains 9999.

### ROWID_INFO Procedure

This procedure returns information about a `ROWID`, including its type (restricted or extended), and the components of the `ROWID`. This is a procedure, and cannot be used in a SQL statement.

#### Syntax

    DBMS_ROWID.ROWID_INFO(
        rowid_in         IN  ROWID,
        rowid_type       OUT  NUMBER,
        object_number    OUT  NUMBER,
        relative_fno     OUT  NUMBER,
        block_number     OUT  NUMBER,
        row_number       OUT  NUMBER);

The `IN` parameter `ROWID_IN` determines if the `ROWID` is a restricted (0) or extended (1) `ROWID`.

The `OUT` parameters return the information about the `ROWID`, as indicated by their names.

For information about the `ROWID_TYPE` parameter, see the `ROWID_TYPE` function on page 10-86.

#### Example

To read back the values for the `ROWID` that you created in the `ROWID_CREATE` example:

    DBMS_ROWID.ROWID_INFO(my_rowid, rid_type, obj_num,
      file_num, block_num, row_num);

    DBMS_OUTPUT.PUT_LINE('The type is ' || rid_type);
    DBMS_OUTPUT.PUT_LINE('Data object number is ' || obj_num);
    -- and so on...

### ROWID_TYPE Function

This function returns 0 if the `ROWID` is a restricted `ROWID`, and 1 if it is extended.

#### Syntax

    FUNCTION DBMS_ROWID.ROWID_TYPE(rowid_val IN ROWID)
      RETURN NUMBER;

#### Example

    IF DBMS_ROWID.ROWID_TYPE(my_rowid) = 1 THEN
      my_obj_num := DBMS_ROWID.ROWID_OBJECT(my_rowid);

### ROWID_OBJECT Function

This function returns the data object number for an extended `ROWID`. The function returns zero if the input `ROWID` is a restricted `ROWID`.

#### Syntax

    DBMS_ROWID.ROWID_OBJECT(rowid_val IN ROWID)
      RETURN NUMBER;

#### Example

    SELECT dbms_rowid.rowid_object(ROWID)
      FROM emp
      WHERE empno = 7499;

### ROWID_RELATIVE_FNO Function

This function returns the relative file number of the `ROWID` specified as the `IN` parameter. (The file number is relative to the tablespace.)

#### Syntax

    DBMS_ROWID.ROWID_RELATIVE_FNO(rowid_val IN ROWID)
      RETURN NUMBER;

#### Example

The example PL/SQL code fragment returns the relative file number:

    DECLARE
      file_number    INTEGER;
      rowid_val      ROWID;
    BEGIN
      SELECT ROWID INTO rowid_val
        FROM dept
        WHERE loc = 'Boston';
      file_number :=
        dbms_rowid.rowid_relative_fno(rowid_val);
      ...

### ROWID_BLOCK_NUMBER Function

This function returns the database block number for the input `ROWID`.

#### Syntax

    DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid_val IN ROWID)
      RETURN NUMBER;

#### Example

The example SQL statement selects the block number from a `ROWID` and inserts it into another table:

    INSERT INTO T2 (SELECT dbms_rowid.rowid_block_number(ROWID)
      FROM some_table
      WHERE key_value = 42);

### ROWID_ROW_NUMBER Function

This function extracts the row number from the `ROWID` `IN` parameter.

#### Syntax

    DBMS_ROWID.ROWID_ROW_NUMBER(rowid_val IN ROWID)
      RETURN NUMBER;

#### Example

Select a row number:

    SELECT dbms_rowid.rowid_row_number(ROWID)
      FROM emp
      WHERE ename = 'ALLEN';

### ROWID_TO_ABSOLUTE_FNO Function

This function extracts the absolute file number from a `ROWID`, where the file number is absolute for a row in a given schema and table. The schema name and the name of the schema object (such as a table name) are provided as `IN` parameters for this function.

#### Syntax

    DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(
      rowid_val          IN  ROWID,
      schema_name        IN  VARCHAR2,
      object_name        IN  VARCHAR2)
          RETURN NUMBER;

#### Example

    DECLARE
      rel_fno        INTEGER;
      rowid_val      CHAR(18);
      object_name    VARCHAR2(20) := 'EMP';
    BEGIN
      SELECT ROWID INTO rowid_val
        FROM emp
        WHERE empno = 9999;
      rel_fno := dbms_rowid.rowid_to_absolute_fno(
        rowid_val, 'SCOTT', object_name);

### ROWID_TO_EXTENDED Function

This function translates a restricted `ROWID` that addresses a row in a schema and table that you specify to the extended `ROWID` format.

#### Syntax

    DBMS_ROWID.ROWID_TO_EXTENDED(
      restr_rowid        IN ROWID,
      schema_name        IN VARCHAR2,
      object_name        IN VARCHAR2)
           RETURN ROWID;

#### Example

Assume that there is a table called `RIDS` in the schema `SCOTT`, and that the table contains a column `ROWID_COL` that holds `ROWID`s (restricted), and a column `TABLE_COL` that point to other tables in the `SCOTT` schema. You can convert the `ROWID`s to extended format with the statement:

    UPDATE SCOTT.RIDS
      SET rowid_col =
      dbms_rowid.rowid_to_extended(rowid_col,
        'SCOTT", TABLE_COL);

#### Usage

`ROWID_TO_EXTENDED` returns the `ROWID` in the extended character format. If the input `ROWID` is `NULL`, the function returns `NULL`. If a zero-valued `ROWID` is supplied (00000000.0000.0000), a zero-valued restricted `ROWID` is returned.

If the schema and object names are provided as `IN` parameters, this function verifies `SELECT` authority on the table named, and converts the restricted `ROWID` provided to an extended `ROWID`, using the data object number of the table. That `ROWID_TO_EXTENDED` returns a value, however, does not guarantee that the converted `ROWID` actually references a valid row in the table, either at the time that the function is called, or when the extended `ROWID` is actually used.

If the schema and object name are not provided (are passed as `NULL`), then this function attempts to fetch the page specified by the restricted `ROWID` provided. It treats the file number stored in this `ROWID` as the absolute file number. This can cause problems if the file has been dropped, and its number has been reused prior to the migration. If the fetched page belongs to a valid table, the data object number of this table is used in converting to an extended `ROWID` value. This is very inefficient, and Oracle recommends doing this only as a last resort, when the target table is not known. The user must still know the correct table name at the time of using the converted value.

If an extended `ROWID` value is supplied, the data object number in the input extended `ROWID` is verified against the data object number computed from the table name parameter. If the two numbers do not match, the `INVALID_ROWID` exception is raised. If they do match, the input `ROWID` is returned.

See the `ROWID_VERIFY` function on page 10-90 for a method to determine if a given `ROWID` can be converted to the extended format.

### ROWID_TO_RESTRICTED Function

This function converts an extended `ROWID` into restricted `ROWID` format.

#### Syntax

    DBMS_ROWID.ROWID_TO_RESTRICTED(ext_rowid IN ROWID)
        RETURN ROWID;

#### Example

    INSERT INTO RID_T2@V7db1
      SELECT dbms_rowid.rowid_to_restricted(ROWID)
        FROM scott.emp@O8db1
        WHERE ename = 'SMITH';

### ROWID_VERIFY Function

This function returns 0 if the input restricted `ROWID` can be converted to extended format, given the input schema name and table name, and it returns 1 if the conversion is not possible. Note that you can use this function in a `BOOLEAN` context in a SQL statement, as shown in the example.

#### Syntax

    DBMS_ROWID.ROWID_VERIFY(
      restr_rowid        IN ROWID,
      schema_name        IN VARCHAR2,
      object_name        IN VARCHAR2)
           RETURN ROWID;

#### Example

Considering the schema in the example for the `ROWID_TO_EXTENDED` function on page 10-88, you can use the following statement to find bad `ROWID`s prior to conversion:

    SELECT ROWID, rowid_col
      FROM SCOTT.RIDS
      WHERE dbms_rowid.rowid_verify(rowid_col, NULL, NULL);

## The UTL_HTTP Package

The stored package `UTL_HTTP` makes `HTTP` (hyper-text transfer protocol) callouts from PL/SQL and SQL. You can use it to access data on the internet, or to call Oracle Web Server Cartridges. The package contains two similar entrypoints, each of which takes a string URL (universal resource locator), contacts that site, and returns the data (typically HTML - hyper-text markup language) obtained from that site.

This is the specification of packaged function `UTL_HTTP`.`REQUEST`:

    function request (url in varchar2) return varchar2;

`UTL_HTTP`.`REQUEST` returns up to the first 2000 bytes of the data retrieved from the given URL. For example:

    SVRMGR> select utl_http.request('http://www.oracle.com/') from dual;
    UTL_HTTP.REQUEST('HTTP://WWW.ORACLE.COM/')
    -------------------------------------------------------------------
    <html>
    <head><title>Oracle Corporation Home Page</title>
    <!--changed Jan. 16, 19
    1 row selected.

This is the specification of packaged function `UTL_HTTP`.`REQUEST_PIECES`, which uses type `UTL_HTTP`.`HTML_PIECES`:

    type html_pieces is table of varchar2(2000) index by binary_integer;
    function request_pieces (url in varchar2,
             max_pieces natural default 32767)
      return html_pieces;

`UTL_HTTP`.`REQUEST_PIECES` returns a PL/SQL-table of 2000-byte pieces of the data retrieved from the given URL. The optional second argument places a bound on the number of pieces retrieved. For example, the following block retrieves up to 100 pieces of data (each 2000 bytes, except perhaps the last) from the URL. It prints the number of pieces retrieved and the total length, in bytes, of the data retrieved.

      set serveroutput on
      /
      declare
        x utl_http.html_pieces;
      begin
        x := utl_http.request_pieces('http://www.oracle.com/', 100);
        dbms_output.put_line(x.count || ' pieces were retrieved.');
        dbms_output.put_line('with total length ');
        if x.count < 1
        then dbms_output.put_line('0');
        else dbms_output.put_line
        ((2000 * (x.count - 1)) + length(x(x.count)));
        end if;
      end;
      /

Here is the output:

      Statement processed.
      4 pieces were retrieved.
      with total length
      7687

Below is the specification for package `UTL_HTTP`. It describes the exceptions that can be raised by functions `REQUEST` and `REQUEST_PIECES`:

    create or replace package utl_http is
    -- Package UTL_HTTP contains functions REQUEST and REQUEST_PIECES for
    -- making HTTP callouts from PLSQL programs.

    -- Function REQUEST takes a URL as its argument.  Its return-type is a
    -- string of length 2000 or less, which contains up to the first 2000 bytes
    -- of the html result returned from the HTTP request to the argument URL.

    function request (url in varchar2) return varchar2;
    pragma restrict_references (request, wnds, rnds, wnps, rnps);

    -- Function REQUEST_PIECES also takes a URL as its argument.  Its
    -- return-type is a PLSQL-table of type UTL_HTTP.HTML_PIECES.  Each
    -- element of that PLSQL-table is a string of length  2000. The
    -- final element may be shorter than 2000 characters.

    type html_pieces is table of varchar2(2000) index by binary_integer;

    function request_pieces (url in varchar2,
          max_pieces natural default 32767)
      return html_pieces;
    pragma restrict_references (request_pieces, wnds, rnds, wnps, rnps);

    -- The elements of the PLSQL-table returned by REQUEST_PIECES are
    -- successive pieces of the data obtained from the HTTP request to that
    -- URL.  Here is a typical URL:
    --     http://www.oracle.com
    -- So a call to REQUEST_PIECES could look like the example below. Note the
    -- use of the plsql-table method COUNT to discover the number of pieces
    -- returned, which may be zero or more:
    --
    -- declare pieces utl_http.html_pieces;
    -- begin
    --   pieces := utl_http.request_pieces('http://www.oracle.com/');
    --   for i in 1 .. pieces.count loop
    --     .... -- process each piece
    --   end loop;
    -- end;
    --

    -- The second argument to REQUEST_PIECES, "MAX_PIECES", is optional.  It is
    -- the maximum number of pieces (each 2000 characters in length, except for
    -- the last, which may be shorter), that REQUEST_PIECES should return.  If
    -- provided, that argument should be a positive integer.

    -- Exceptional conditions:

    -- If initialization of the http-callout subsystem fails (for
    -- environmental reasons, for example, lack of available memory)
    -- then exception UTL_HTTP.INIT_FAILED is raised:

    init_failed exception;

    -- When the HTTP call fails (e.g., because of failure of the HTTP daemon;
    -- or because of the argument to REQUEST  or REQUEST_PIECES cannot be
    -- interpreted as a URL because it is NULL or has non-HTTP syntax) then
    -- exception UTL_HTTP.REQUEST_FAILED is raised.

    request_failed exception;

    -- Note that the above two exceptions, unless explicitly caught by an
    -- exception handler, will be reported by this generic message:
    --   ORA-06510: PL/SQL: unhandled user-defined exception
    -- which reports them as "user-defined" exceptions, although
    -- they are defined in this system package.

    -- If any other exception is raised during the processing of the http
    -- request (for example, an out-of-memory error), then function REQUEST
    -- or REQUEST_PIECES reraises that exception.

    -- When no response is received from a request to the given URL
    -- (for example, because no site corresponding to that URL is contacted)
    -- then a formatted html error message may be returned.  For example:
    --
    --  <HTML>
    --  <HEAD>
    --  <TITLE>Error Message</TITLE>
    --  </HEAD>
    --  <BODY>
    --  <H1>Fatal Error 500</H1>
    --  Can't Access Document:  http://home.nothing.comm.
    --  <P>
    --  <B>Reason:</B> Can't locate remote host:  home.nothing.comm.
    --  <P>
    --
    --  <P><HR>
    --  <ADDRESS><A HREF="http://www.w3.org">
    --   CERN-HTTPD3.0A</A></ADDRESS>
    --  </BODY>
    --  </HTML>
    --

    -- You should not expect for UTL_HTTP.REQUEST or UTL_HTTP.REQUEST_PIECES
    -- to succeed in contacting a URL unless you can contact that URL by using
    -- a browser on the same machine (and with the same privileges, environment
    -- variables, etc.)  If REQUEST or REQUEST_PIECES fails (i.e., if it raises
    -- an exception, or returns a HTML-formatted error message, yet you believe
    -- that the URL argument is correct), please try contacting that same URL
    -- with a browser, to verify network availability from your machine.

    end utl_http;

Prev | Next
| Copyright © 1997 Oracle Corporation. All Rights Reserved. |
Library | Product | Contents | Index