Procedures

A PL/SQL procedure is a named PL/SQL block. Unlike an anonymous block, a procedure can

take parameters

be invoked from a separate application

be compiled once, but invoked many times

be stored in compiled form in a database, independent of the shared SQL cache

A procedure contains one or more PL/SQL blocks. The following example computes the grade point average. The student ID number is passed as a parameter to the procedure, and the computed grade point average is returned by the procedure.

The procedure declaration adds a parameter list to the PL/SQL block. In this example, student_id is a parameter whose mode is IN. The mode of a parameter indicates whether the parameter passes data to a procedure (IN), returns data from a procedure (OUT), or can do both (IN OUT). The parameter gpa is an OUT parameter. It returns a value, but you cannot use it to pass a value to the procedure. Nor can you read its value inside the procedure, even after a value has been assigned to it.

Stored Procedures

You can store PL/SQL procedures in the database, and call these stored procedures from Oracle applications. Storing a procedure in the database offers many advantages. Only one copy of the procedure needs to be maintained, it is in the database, and it can be accessed by many different applications. This considerably reduces maintenance requirements for large applications. A stored procedure is not recompiled each time it is called.

Procedures can be stored in the database using Oracle tools such as SQL*Plus. You create the source for the procedure using your text editor, and execute the source using SQL*Plus (for example, with the @ operator). When you input the source, use the CREATE PROCEDURE command. (You can also use CREATE OR REPLACE PROCEDURE, to replace an already stored procedure of the same name.)

See the Oracle8 Reference for complete information about the CREATE PROCEDURE command.

Stored Packages

The examples of stored procedures shown so far in this chapter involve standalone procedures (sometimes called top-level procedures). These are useful in small applications. But, to gain the full power of stored procedures, you should use packages.

A package encapsulates procedures, as well as other PL/SQL objects. Stored packages that are used with Ada applications have two parts: a package specification and a package body. The specification is the (exposed) interface to the host application; it declares the procedures that are called by the application. A complete PL/SQL package specification can also declare functions, as well as other PL/SQL objects such as constants, variables, and exceptions. However, an Ada application using SQL*Module cannot access or reference PL/SQL objects other than subprograms. The package body contains the PL/SQL code that defines the procedures and other objects that are declared in the package specification.

Although an Ada application can only access public subprograms, a called subprogram can in turn call private subprograms, and can access public and private variables and constants in the package.

Accessing Stored Procedures

You can use SQL*Module to provide a bridge that enables your host application to access procedures stored in the database. A host application written in Ada cannot call a stored database subprogram directly. But you can use SQL*Module to construct an interface procedure ("stub'') that calls the stored database subprogram. Table 3-1 shows, in schematic form, how this process works.

Figure 3-1 Accessing a Stored Procedure

In this example, there is a procedure stored in the database called enroll. The PL/SQL source code that created the procedure is shown in the right-hand box. The WITH INTERFACE clause in the procedure is described in the section "The WITH INTERFACE Clause". The procedure has two database parameters: class_no and student_id. The SQLCODE error return parameter is added in the interfacing clause.

Case of Package and Procedure Names

The Oracle Server always translates to uppercase the names of database objects as they are inserted into the database. This includes the names of packages and procedures. For example, if you are loading a package into the database in the SCOTT schema, and have a PL/SQL source file that contains the line

CREATE PACKAGE school_records AS ...

then Oracle inserts the name into the schema as SCHOOL_RECORDS, not the lowercase ``school_records''. The following SQL*Module command (in UNIX)

modada rpc_generate=yes pname=school_records userid=scott

generates an error, since there is no package named ``school_records'' in the schema.

If you prefer to have your package and procedure names stored in lowercase in the database, you must quote all references to the name in the PL/SQL source file, or as you insert them into the database using SQL*Plus. So, you would code

CREATE PACKAGE "school_records" AS ...

Note also that SQL*Module preserves the case of subprogram names when creating interface procedure files.

However, if you really do want uppercase names, some operating systems (OPEN VMS is an example) require that you quote the name when you specify it on the command line. So, you would enter the command as

modada rpc_generate=yes pname="SCHOOL_RECORDS" user=scott

See your system-specific Oracle documentation, and your operating system documentation, for additional information on case conventions for command lines that are in effect for your operating system.

Early and Late Binding

When you generate RPCs (remote procedure calls) using SQL*Module, you have a choice of early binding or late binding. Your choice of early or late binding is controlled by the BINDING option.

When you choose early binding, SQL*Module generates a call to the procedure stored in the database, and also uses a time stamp that is associated with the call. The time stamp records the date and time (to the nearest second) that the stored procedure was last compiled. The time stamp is created by the Oracle database. If a host application calls the stored procedure through the interface procedure, and the time stamp recorded with the interface procedure is earlier than the time stamp on the stored procedure recorded in the database, an error is returned to the host application in the SQLCODE and/or SQLSTATE status parameter. The SQLCODE error is 4062 "time stamp of name has been changed".

The late binding option, on the other hand, does not use a time stamp. If your application calls a stored procedure that has been recompiled since SQL*Module generated the interface procedure, no error is returned to the application.

With late binding, SQL*Module generates the call to the stored procedure using an anonymous PL/SQL block. The following example shows a specification for a stored procedure that is part of a package in the SCOTT schema:

In other words, the call to the stored procedure get_sal_comm is performed using an anonymous PL/SQL block. This is the way stored procedures are called from an Oracle precompiler or Oracle Call Interface application.

The advantages of late binding are

greater flexibility

changes in the stored procedure(s) are transparent to the user

gives behavior similar to interactive SQL (for example, SQL*PLus)

The disadvantages of late binding are

There might be additional performance overhead at runtime, due to the necessity of compiling the PL/SQL anonymous block.

It is difficult to detect runtime PL/SQL compilation errors in the host application. For example, if the anonymous block that calls the late-bound procedure fails at runtime, there is no convenient way for the host application to determine the cause of the error.

The lack of time-stamp capability means that changes, perhaps radical changes, in the stored procedure could be made after the host application was built, and the application would have no way of detecting this.

Use the BINDING={EARLY | LATE} command line option to select early or late binding when generating RPC interface procedures. See Chapter 5, "Running SQL*Module" for a description of this and other command line options.

Cursor Variables

You can use cursor variables in your application. A cursor variable is a reference to a cursor that is defined and opened on the Oracle8 server. See the ">PL/SQL User's Guide and Reference for complete information about cursor types.

The advantages of cursor variables are

Encapsulation: queries are centralized, placed in the stored procedure that opens the cursor variable. The logic is hidden from the user.

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 and rows in the table.

Cursor Variable Parameters

You define a cursor variable parameter in your module using the type SQL_CURSOR. For example:

PROCEDURE alloc_cursor (
SQLCODE,
:curs SQL_CURSOR);

In this example, the parameter curs has the type SQL_CURSOR.

Allocating a Cursor Variable

You must allocate the cursor variable. You do this using the Module Language command ALLOCATE. For example, to allocate the SQL_CURSOR curs that is the formal parameter in the example above, you write the statement:

ALLOCATE :curs;

Note: You use the ALLOCATE command only for cursor variables. You do not need to use it for standard cursors.

Opening a Cursor Variable

You must open a cursor variable on the Oracle Server. You cannot use the OPEN command that you use to open a standard cursor to open a cursor variable. You open a cursor variable by calling a PL/SQL stored procedure that opens the cursor (and defines it in the same statement).

For example, consider the following PL/SQL package, stored in the database:

After you have stored this package, and you have generated the interface procedures, you can open the cursor curs by calling the OPEN1 stored procedure from your Ada driver program. You can then call module procedures that FETCH the next row from the opened cursor. For example:

In your driver program, you call this procedure to fetch each row from the result defined by the cursor. When there is no more data, the value +100 is returned in SQLCODE.

Note: When you use SQL*Module to create the interface procedure to call the stored procedure that opens the cursor variable, you must specify BINDING=LATE. Early binding is not supported for cursor variables in this release.

Opening in a Stand-alone Stored Procedure

In the example above, a cursor type was defined inside a package, and the cursor was opened in a procedure in that package. But it is not always necessary to define a cursor type inside the package that contains the procedures that open the cursor.

If you need to open a cursor inside a stand-alone stored procedure, you can define the cursor in a separate package, then reference that package in the stand-alone stored procedure that opens the cursor. Here is an example:

PACKAGE dummy IS
TYPE EmpName IS RECORD (name VARCHAR2(10));
TYPE emp_cursor_type IS REF CURSOR RETURN EmpName;
END;
-- and then define a stand-alone procedure:
PROCEDURE open_emp_curs (
emp_cursor IN OUT dummy.emp_cursor_type;
dept_num IN NUMBER) IS
BEGIN
OPEN emp_cursor FOR
SELECT ename FROM emp WHERE deptno = dept_num;
END;
END;

Return Types

When you define a reference cursor in a PL/SQL stored procedure, you must declare the type that the cursor returns. See the ">PL/SQL User's Guide and Reference for complete information on the reference cursor type and its return types.

Closing a Cursor Variable

Use the Module Language CLOSE command to close a cursor variable. For example, to close the emp_cursor cursor variable that was OPENed in the examples above, use the statement

CLOSE :emp_cursor;

Note that the cursor variable is a parameter, and so you must precede it with a colon.

You can reuse ALLOCATEd cursor variables. You can OPEN, FETCH, and CLOSE as many times as needed for your application. However, if you disconnect from the server, then reconnect, you must reallocate cursor variables.

Restrictions on Cursor Variables

The following restrictions apply to the use of cursor variables:

1. You can only use cursor variables with the commands:

ALLOCATE

FETCH

CLOSE

2. The DECLARE CURSOR command does not apply to cursor variables.

You cannot FETCH from a CLOSEd cursor variable.

You cannot FETCH from a non-ALLOCATEd cursor variable.

Cursor variables cannot be stored in columns in the database.

A cursor variable itself cannot be declared in a package specification. Only the type of the cursor variable can be declared in the package specification.

A cursor variable cannot be a component of a PL/SQL record.

Dynamic SQL

Dynamic SQL is the capability of executing SQL commands that are stored in character string variables. The package DBMS_SQL parses Data Definition Language (DDL) and Data Manipulation (DML) statements at runtime. DBMS_SQL has functions such as OPEN_CURSOR, PARSE, DEFINE_COLUMN, EXECUTE, FETCH_ROWS, COLUMN_VALUE, etc. Use these functions in your program to open a cursor, parse the statement, and so on. An example that does dynamic SQL (demo_dyn_drv.ada and demo_dyn.mad for Solaris platforms) is in the demo directory.

The WITH INTERFACE Clause

The stored procedure format in the previous section can be used for stored procedures that are to be called from applications written using Oracle tools. For example, a SQL*Plus script can call the GET_GPA procedure in "Procedures" just as it is written.

You can code a WITH INTERFACE clause, or you can let SQL*Module generate a default WITH INTERFACE clause for stored procedures that have been stored without this clause.

This clause, when added to a procedure declaration in the package specification, lets you add parameters that are essential to perform an RPC to a PL/SQL procedure, through a calling interface procedure in the output file. In addition, the WITH INTERFACE clause uses SQL datatypes, not the PL/SQL datatypes that are used in the stored procedure definition. The additional features of the WITH INTERFACE clause are

use of SQL datatypes

optional indicator parameters

use of the SQLSTATE and SQLCODE status parameters

Note: The procedures names that you code in WITH INTERFACE clauses must be unique within the entire application. If you let SQL*Module generate default WITH INTERFACE, then overloaded procedure names are resolved using an algorithm described in "MAPPING".

Arrays are not allowed in WITH INTERFACE clauses.

Examples

The following package declaration shows how you use the WITH INTERFACE clause to map PL/SQL datatypes to SQL datatypes, and add the SQLCODE and/or SQLSTATE status parameters. Status parameters are filled in automatically as the procedure executes. They are not directly accessible within the procedure body.

The interface procedure name specified in the WITH INTERFACE clause can be the same as the name of the procedure itself, or, as in this example, it can be different. However, the name specified in the WITH INTERFACE clause is the name that must be used when you invoke the stored procedure from your host application.

In the example above, the datatypes in the WITH INTERFACE clause are SQL datatypes (INTEGER and REAL). These types are compatible with the PL/SQL datatype NUMBER.

You must include either a SQLCODE or a SQLSTATE parameter in the parameter list of the WITH INTERFACE clause. You can include both. SQLSTATE is the recommended parameter; SQLCODE is provided for compatibility with the SQL89 standard.

Note: Parameters in the PL/SQL procedure specification cannot be constrained. Parameters in the WITH INTERFACE clause must be constrained where required.

The following package definition shows an example of the WITH INTERFACE clause:

In the example above, the student_last_name parameter is a CHARACTER, which is both a PL/SQL and a SQL datatype. In the PL/SQL part of the procedure definition, the parameter must be unconstrained, following the syntax of PL/SQL. But in the WITH INTERFACE clause, you must specify the length of the parameter.

The student_last_name parameter also takes an indicator parameter, using the syntax shown. See Appendix B for the formal syntax of the WITH INTERFACE clause.

SQL Datatypes

The SQL datatypes that you can use in the WITH INTERFACE clause are listed in Table 3-1, along with their compatible PL/SQL datatypes.

Table 3-1 SQL Datatypes

SQL Datatypes

Range or Size

SQL Meaning

Compatible PL/SQL Datatypes

CHARACTER (N) OR CHAR (N)

1 < N < 32500 bytes

String of length N (if N is omitted, N is effectively 1)

VARCHAR2(N), CHAR(N), DATE

DOUBLE PRECISION

Implicit precision 38

Approximate numeric type

NUMBER

INTEGER

or INT

System specific

Integer type

NUMBER, BINARY_INTEGER

SMALLINT

System specific

Small (or short) integer type

NUMBER, BINARY_INTEGER

REAL

System-specific

Approximate numeric type

NUMBER

VARCHAR2(N)

1 < N < 32500 bytes

Character array of length N

VARCHAR2(N),

CHAR(N),DATE

SQL_CURSOR

Cursor variable type

REF cursor

Notes

1. SQL datatypes compatible with NUMBER are also compatible with types derived from

NUMBER, such as REAL.

2. The size of integer and small integer types is system specific. For many systems, integers are 32 bits wide and small integers are 16 bits, but check your system documentation for the size on your system.

DATE Datatype

SQL*Module does not directly support the Oracle DATE datatype. You can, however, use character strings when you fetch, select, update, or insert DATE values. Oracle does the conversion between internal DATEs and character strings. See the Oracle8 Reference for more information about the DATE datatype, and conversion between DATEs and character strings.

The Default WITH INTERFACE Clause

If a package has already been defined in the database with no WITH INTERFACE clauses for the subprograms, you can still generate interface procedures to call the subprograms. The default WITH INTERFACE clause that is generated by SQL*Module when there is no WITH INTERFACE clause in the package or procedure gives you all the features of the standard WITH INTERFACE clause:

Procedures

When SQL*Module generates an interface procedure with a default WITH INTERFACE clause, it generates a SQLCODE parameter in the first parameter position, and a SQLSTATE parameter in the second position. Then, for each actual parameter in the stored procedure or stored function, a parameter is generated with the appropriate mapped host language datatype. Each parameter is followed by an indicator parameter, mapped to the correct host language type from the SQL datatype SMALLINT.

Functions

If SQL*Module is generating a default WITH INTERFACE clause for functions in a package, then the WITH INTERFACE clause is generated as if the function were a procedure, with the return value and its indicator parameter as the last two parameters in the clause.

Table 3-2 shows how predefined, or base, PL/SQL datatypes are mapped to SQL datatypes, and then to host language datatypes. PL/SQL subtypes that are derived from the base types are also supported, and are mapped as indicated for the base type.

Table 3-2 Mapping PL/SQL Datatypes to SQL Datatypes

PL/SQL

Datatype

Ada Language Datatype

BINARY INTEGER

SQL_STANDARD.INT

NUMBER

NUMBER(P,S)

SQL_STANDARD.

DOUBLE_PRECISION

RAW

LONG RAW

STRING

LONG

STRING

BOOLEAN

SQL_STANDARD.INT

CHAR

SQL_STANDARD.CHAR

VARCHAR2

STRING

DATE

SQL_STANDARD.CHAR

ROWID

STRING

CURSOR

ORACLE_SQLLIB.SQL_CURSOR

Notes

Maximum length of a STRING is 32500 bytes.

Maximum length of a DATE is 2048 bytes.

Maximum length of ROWID and MLSLABEL is 256 bytes.

Suppose, for example, that a procedure stored in the SCOTT schema has the parameter list

then the Ada procedure specification in the generated output file proc1_.a would be created by SQL*Module as follows:

procedure PROC1(SQLCODE: in out sql_standard.sqlcode_type;
sqlstate: in out sql_standard.sqlstate_type;
PARAM1: in sql_standard.double_precision;
PARAM1_ind: in sql_standard.smallint;
PARAM2: in out oracle_sqllib.sql_date;
PARAM2_ind: in out sql_standard.smallint;
PARAM3: out sql_standard.double_precision;
PARAM3_ind: out sql_standard.smallint;
PARAM4: in string;
PARAM4_ind: in sql_standard.smallint;
PARAM5: in sql_standard.int;
PARAM5_ind: in sql_standard.smallint);

Function calls are generated as procedures with the last two parameters in the generated prototype being the return parameter and the indicator variable for the return parameter. For example:

FUNCTION func1 (
PARAM1 IN NUMBER) RETURN VARCHAR2

would have the Ada prototype:

procedure FUNC1(SQLCODE: in out sql_standard.sqlcode_type;
sqlstate: in out sql_standard.sqlstate_type;
PARAM1: in sql_standard.double_precision;
PARAM1_ind: in sql_standard.smallint;
mod_func_return: out string;
mod_func_return_ind: out sql_standard.smallint) is
begin
. . .
end FUNC1;

Storing Module Language Procedures

You can also use SQL*Module to create a stored package in the database from Module Language procedures. By specifying the module file in the INAME command line option (see Chapter 5, "Running SQL*Module" for details), and setting the option STORE_PACKAGE=YES, the procedures in the module file are stored in a package in the database, using the module name as the default package name. (The default name can be overridden using the PNAME option. See Chapter 5, "Running SQL*Module" for details.)