3 Accessing Stored Procedures

This chapter describes how to use SQL*Module to generate interface procedures to call stored procedures. It covers the following topics:

PL/SQL
Stored Procedures
Stored Packages
Accessing Stored Procedures
Case of Package and Procedure Names
Early and Late Binding
Cursor Variables
Dynamic SQL
The WITH INTERFACE Clause
Storing Module Language Procedures
Connecting to a Database

Note:

The examples in this chapter use the tables defined in Chapter 6, "Demonstration Programs".

PL/SQL

This section contains a brief overview of PL/SQL, Oracle's procedural language extension to SQL. PL/SQL is a modern block-structured language that enables you to

declare constants and variables
control execution flow, using IF ... THEN ... ELSE, EXIT, GOTO, and other procedural constructs
create loops, using WHILE ... LOOP and FOR ... LOOP
assign constant or variable expressions to a variable
issue SQL Data Manipulation Language and Transaction Control statements
define exceptions, handle them using WHEN EXCEPTION_NAME THEN ..., and raise them using RAISE EXCEPTION_NAME

See the Oracle Database PL/SQL Language Reference for complete information about the PL/SQL language.

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.

PROCEDURE get_gpa( 
             student_id IN NUMBER,
             gpa        OUT NUMBER) IS 
    n                NUMBER; 
    grade_temp       NUMBER; 
    gpa_temp         NUMBER; -- needed because PL/SQL cannot read
                             -- an OUT parameter like GPA
    CURSOR c1(sid) IS 
        SELECT grade FROM enrollment
            WHERE student_id = sid;

    BEGIN
        n := 0;
        gpa := 0;
        OPEN c1(student_id);
        LOOP 
            FETCH c1 INTO grade_temp; 
            EXIT WHEN c1%NOTFOUND;     -- c1%NOTFOUND is TRUE 
                                       -- when no more data found 
            gpa_temp := gpa_temp + grade_temp; 
            n := n + 1; 
        END LOOP; 
        IF n > 0 THEN 
            gpa := gpa_temp / n; 
        END IF; 
        CLOSE c1; 
    END; 
END PROCEDURE get_gpa;

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.

You can store procedures in the database by 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 Oracle Database 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. However, 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.

For complete information about stored packages, see the Oracle Database PL/SQL Language Reference.

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. shows, in schematic form, how this process works.

Figure 3-1 Accessing a Stored Procedure

Description of "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 the names of database objects to uppercase when 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 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:

PACKAGE emppkg IS 
 
     PROCEDURE get_sal_comm (emp_num     IN   NUMBER, 
                             salary      OUT  NUMBER, 
                             commission  OUT  NUMBER) 
     WITH INTERFACE
     PROCEDURE get_sal_emp  ( 
                              emp_num     INTEGER, 
                              salary      REAL, 
                              commission  REAL INDICATOR comm_ind, 
                              comm_ind    SMALLINT, 
                              SQLCODE); 
END emppkg;

If you generate an RPC interface procedures output file for the package using the command

modada pname=EMPPKG rpc_generate=yes binding=late userid=scott/tiger

SQL*Module generates a call in the output file, as follows:

With Oracle_Sqllib; use Oracle_Sqllib;
with SQL_STANDARD;
Package EMPPKG is
 
procedure GET_SAL_EMP(EMPNUM: in sql_standard.int;
 SALARY: out sql_standard.real;
 COMMISION: out sql_standard.real;
 COMM_IND: out sql_standard.smallint;
 SQLCODE: out sql_standard.sqlcode_type);
sql_001 : constant string :=
"begin ""EMPPKG.SCOTT""." &
    """GET_SAL_COMM""(:EMPNUM, :SALARY, :COMMISION:COMM_IND); end;";
 
end EMPPKG;
...

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 Oracle Database version 8 server. See the Oracle Database PL/SQL Language 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 by 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 by using the Module Language command ALLOCATE. For example, to allocate the SQL_CURSOR curs that is the formal parameter in the example, 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:

CONNECT scott/tiger
 
CREATE OR REPLACE PACKAGE cursor_var_pkg AS
 
    TYPE emp_record_type IS RECORD (ename EMP.ename%TYPE;); 
    TYPE curtype IS REF CURSOR RETURN emp_record_type;
 
    PROCEDURE OPEN1(cur1 IN OUT curtype)
    WITH INTERFACE
    PROCEDURE 
           OPEN1 (SQLCODE integer, cur1 SQL_CURSOR);
 
end cursor_var_pkg;
 
CREATE OR REPLACE PACKAGE BODY cursor_var_pkg AS
 
    PROCEDURE OPEN1(cur1 IN OUT curtype) IS
    BEGIN
        OPEN cur1 FOR SELECT ename FROM emp_view;
    END;
END cursor_var_pkg;
 
COMMIT;

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:

PROCEDURE fetch_from_cursor (
    SQLCODE,
    :curs SQL_CURSOR,
    :emp_name VARCHAR2(11));
 
  FETCH :curs INTO :emp_name;

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, 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 standalone stored procedure, you can define the cursor in a separate package, then reference that package in the standalone 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 standalone 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 Oracle Database PL/SQL Language 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, 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, and so on. Use these functions in your program to open a cursor, parse the statement, and so on.

For more details on this package, see Oracle Database Advanced Application Developer's Guide

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 or SQLSTATE status parameters. Status parameters are filled in automatically as the procedure executes. They are not directly accessible within the procedure body.

CREATE or REPLACE PACKAGE gpa_pkg AS 
  PROCEDURE get_gpa (student_id  IN  NUMBER, 
                     gpa         OUT NUMBER) 
  WITH INTERFACE
  PROCEDURE get_gpa_if 
                     (student_id  INTEGER, 
                      gpa         REAL, 
                      SQLCODE     INTEGER 
                      SQLSTATE    CHARACTER(6)); 
...

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, 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:

CREATE OR REPLACE PACKAGE gpa_pkg AS 
 
  PROCEDURE get_gpa(student_id        IN     NUMBER, 
                    student_last_name IN OUT CHARACTER, 
                    gpa               OUT    NUMBER) 
  WITH INTERFACE
  PROCEDURE get_gpa_if 
                   (student_id        INTEGER, 
                    student_last_name CHARACTER(15) 
                                      INDICATOR sname_ind, 
                    sname_ind         SMALLINT, 
                    gpa               REAL, 
                    SQLSTATE          CHARACTER(6), 
                    SQLCODE           INTEGER); 
END;

In the example, 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 , 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

Note:

SQL datatypes compatible with NUMBER are also compatible with types derived from NUMBER, such as REAL.

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 Oracle Database 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:

the SQLCODE error handling parameter
the SQLSTATE error handling parameter
indicator parameters
datatype mapping between PL/SQL base and derived datatypes and SQL types

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

Note:

The maximum length for the following data types are:

For STRING is 32500 bytes
For DATE is 2048 bytes
For ROWID and MLSLABEL is 256 bytes

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

PROCEDURE proc1 (
        PARAM1 IN     NUMBER,
        PARAM2 IN OUT DATE,
        PARAM3    OUT DOUBLE PRECISION,
        PARAM4        CHARACTER,
        PARAM5        BINARY_INTEGER)

If you run the module compiler, modada, as follows:

modada pname=PROC1 rpc_generate=yes user=scott/tiger oname=proc1

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.)

For example, the following module file:

MODULE        test_sp 
AUTHORIZATION scott
 
PROCEDURE get_emp ( 
        :empname    CHAR(10),
        :empnumber  INTEGER, 
         SQLCODE); 
    SELECT ename INTO :empname 
        FROM emp 
        WHERE empno = :empnumber; 
 
PROCEDURE put_emp ( 
        :empname    CHAR(10), 
        :empnumber  INTEGER, 
        :deptnumber INTEGER, 
        SQLCODE); 
    INSERT INTO emp (ename, empno, deptno) VALUES 
                    (:empname, :empnumber, :deptnumber);

when stored as a package in the database would produce the following PL/SQL code for the package specification:

package test_sp is 
procedure get_emp 
    (empname out char, 
    empnumber in number) 
  with interface procedure get_emp 
    (empname char(11),
    empnumber integer, 
    sqlcode integer); 
procedure put_emp 
    (empname in char, 
    empno in number, 
    deptno in number) 
  with interface procedure put_emp 
    (empname char(11), 
    empnumber integer, 
    deptnumber integer, 
    sqlcode integer); 
end test_sp;

Note: You cannot store module procedures that contain the ALLOCATE statement, nor statements CONNECT, DISCONNECT, ENABLE THREADS, CONTEXT, nor FETCH and CLOSE statements that refer to cursor variables.

Connecting to a Database

When you write an Ada program that calls RPC interface procedures that were generated from stored procedures, you need a way to connect to a database at runtime. The steps you can take to do this are

Write a module that contains connect and disconnect procedures. See "CONNECT Statement" for the syntax of these procedures. See also the examples in the demomod sample in Chapter 6, "Demonstration Programs".
Compile the module using SQL*Module.

Add a with clause to the host application file referencing the generated specification name.

Compile the specification file.
Compile the source output file.
Link your main application.