Skip Headers
Oracle® SQL*Module for Ada Programmer's Guide
11g Release 1 (11.1)

Part Number B31228-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

2 Module Language

This chapter describes SQL standard Module Language, including Oracle's extensions to standard Module Language. It discusses the following topics:

This chapter does not cover the use of SQL*Module to provide interfaces to stored procedures. See Chapter 3, "Accessing Stored Procedures" for information about calling stored procedures from a host application.

The Module

A module is a single file that contains

The easiest way to see how a module is constructed is to look at an example. The small module contains a cursor declaration, procedures that open and close the cursor, and a procedure that uses the cursor to fetch data from the familiar EMP table. Two consecutive dashes (- -) begin a comment, which continues to the end of the line. Case is not significant.

An Example Module

-- the preamble (contains three clauses) 
MODULE     EXAMPLE1_MOD    -- Define a module named example1_mod. 
LANGUAGE   Ada             -- The procedures are compiled into
                           -- Ada, and called from an
                           -- Ada application.

AUTHORIZATION SCOTT/TIGER  -- Use Scott's tables. 
                           -- His password is "tiger" 
                           -- (the password does not have to 
                           -- be specified in the module file). 

-- Declare a cursor for a SELECT statement that 
-- fetches four columns from the EMP table.
-- dept_number will be specified 
-- in the procedure that opens the cursor. 

    SELECT ename, empno, sal, comm
       FROM emp 
       WHERE deptno = :dept_number

-- Define a procedure named "open_cursor1" to open the cursor. 
-- After the procedure name is a comma separated parameter list 
-- enclosed in ()'s. 
PROCEDURE open_cursor1 (
         :dept_number   INTEGER,
    OPEN cursor1;

-- The "fetch_emp_data" procedure gets data from the cursor. 
-- SQLCODE will return as 100 when there 
-- is no more data. 
PROCEDURE fetch_emp_data ( 
         :empno         INTEGER, 
         :empname       VARCHAR2(10), 
         :sal           REAL, 
         :commission    REAL,
         :comm_ind      SMALLINT,

-- the SQL command is a FETCH on the cursor 
    FETCH cursor1 
        INTO :empname,
             :commission INDICATOR :comm_ind;

-- Define a procedure to close the cursor. 
PROCEDURE close_cursor1 (SQLCODE); 
    CLOSE cursor1; 

-- Connect to a database
PROCEDURE do_connect (
        :dbname         VARCHAR2(12),
        :username       VARCHAR2(12),
        :passwd         VARCHAR2(12),

    CONNECT TO :dbname USER :username USING :passwd;

-- Disconnect
PROCEDURE do_disconnect (SQLCODE);



If you are familiar with the syntax of SQL89 Module Language, you should note that the newer SQL92 syntax is used in this example. This includes parentheses around a comma separated parameter list, colons before parameters, and use of the INDICATOR keyword. This syntax is supported by the current release of SQL*Module, and is used throughout this Guide.

In this example, the LANGUAGE clause (in the second line of the module) specifies "Ada". This module will be compiled into Ada code. For SQL*Module, this clause is optional. When present, it is, in effect, a comment.

When SQL*Module compiles this module, it transforms the procedures into Ada language procedures that open the cursor and call library routines to fetch the data. SQL*Module also generates a package specification file, which must be compiled into the Ada library and referenced in the host application using a with context clause. See Chapter 5, "Running SQL*Module" in this Guide for information on running SQL*Module, and Chapter 6, "Demonstration Programs" for information about Ada specification files.

A Short Example Program in Ada

To complete the example, a short Ada program that calls the procedures defined in the module file in "An Example Module" follows.

-- Include TEXT_IO,SQL_STANDARD and EXAMPLE1_MOD package specs.


-- Define the main procedure.

procedure EXAMPLE1_DRV is

-- Instantiate new packages for I/O on SQL_STANDARD datatypes.
    package STD_INT_IO is
        new text_io.integer_io(SQL_STANDARD.INT);
    use STD_INT_IO;

    package SQLCODE_IO is
        new text_io.integer_io(SQL_STANDARD.SQLCODE_TYPE);
    use SQLCODE_IO;

    package STD_FLOAT_IO is
        new text_io.float_io(SQL_STANDARD.REAL);
    use STD_FLOAT_IO;

-- Begin with declarations of all program variables, 
-- including parameters for the module procedures.
    SERVICE_NAME         : string(1..12)
                               := "INST1_ALIAS ";
    USERNAME             : string(1..12)
                               := "SCOTT       ";
    PASSWORD             : string(1..12)
                               := "TIGER       ";

    EMPLOYEE_NAME        : string(1..10); 
    SALARY               : SQL_STANDARD.REAL; 
    LENGTH               : integer;

    CONNECT_ERROR        : exception;
    SQLCODE_ERROR        : exception; 

-- Call a module procedure to connect
-- to the Oracle server.

-- Test SQLCODE to see if the connect succeeded.
    if SQLCODE /= 0 then
        raise CONNECT_ERROR;
    end if;


-- Get an input value from user to use
-- in opening the cursor.
    TEXT_IO.PUT("Enter department number: ");

-- Call the module procedure to open the cursor.
-- You open the cursor using the input parameter (dept_number).
-- If SQLCODE indicates error, call exception handler.
    if SQLCODE < 0 then
        raise SQLCODE_ERROR;
    end if;

    TEXT_IO.PUT_LINE("Employee  ID Number   Salary  Commission");

-- Call the FETCH procedure in a loop, to get
-- the employee data.


-- When SQLCODE = 100, no more rows to fetch.
        exit when SQLCODE = 100;

-- When SQLCODE less than 0, an error occurred.
        if SQLCODE < 0 then
            raise SQLCODE_ERROR;
        end if;

        STD_FLOAT_IO.PUT(SALARY, FORE => 6, AFT => 2, EXP => 0); 

        if COMM_IND = 0 then
            STD_FLOAT_IO.PUT(COMMISSION, FORE => 9, AFT => 2, EXP => 0);
            TEXT_IO.PUT("        Null");
        end if;
    end loop;


-- Call the procedure to close the cursor.

-- Call the procedure to disconnect.

-- Handle the error exception.

     when SQLCODE_ERROR => 
          TEXT_IO.PUT("Error fetching data, SQLCODE returns "); 

     when CONNECT_ERROR =>
          TEXT_IO.PUT("Connect failure to " & 


This example demonstrates several important points about SQL*Module:

The types of the parameters in the module procedures are SQL datatypes, for example SMALLINT and REAL, not Ada datatypes.

  • Parameters are passed to the generated output code procedures in the normal way. Refer to Chapter 6, "Demonstration Programs" for specific information concerning parameter passing conventions.

  • The error status of a call is returned in the SQLCODE or SQLSTATE parameter. There must be a SQLCODE or SQLSTATE status parameter somewhere in the parameter list of each Module Language procedure. See the section "Status Parameters" for more information.

Structure of a Module

This section is an informal guide to Module Language syntax, including Oracle's extensions to the language. See Appendix B to this Guide for a formal description of Module Language syntax.

A module is contained in a single file and consists of


The preamble is a set of clauses at the beginning of a module file that describes the module. The clauses are





The MODULE clause provides a name to the module. The argument is the module name, which must be a legal SQL identifier.


A SQL identifier is a string containing only the 26 letters A through Z, the digits 0 through 9, and the underscore ("_"). The identifier must start with a letter, and cannot be longer than 30 characters (18 characters to comply with the SQL89 standard). You can use lowercase letters (a..z), but a SQL identifier is not case sensitive. So, the identifiers "THIS_IDENTIFIER" and "this_identifier" are equivalent. The characters'$' and'#' are also allowed in a SQL identifier, but you should avoid using them, as they have special meaning in many languages and operating systems.

The module name must be supplied. The module name must be the same as the name of the Ada library unit that you use when you store the Ada-compiled output in your Ada library.


The LANGUAGE clause specifies the target language. It takes one argument — the language name, which must be Ada (case is not significant).

The LANGUAGE clause is optional in SQL*Module. See Chapter 5, "Running SQL*Module" for more information about running SQL*Module.


The AUTHORIZATION clause can determine, or help to determine, the database and schema that SQL*Module uses at compile time.

The argument to the AUTHORIZATION clause can take any one of the following four forms:

  • the username: scott

- scott 

  • the username plus a password, the two separated by a slash: scott/tiger

- scott/tiger

  • the username plus a database to connect to:

scott@{instance_alias | net8_connection_string}
  • a full specification, consisting of the username, a password, and the database to connect to: scott/tiger@{instance_alias | net8_connection_string}

- scott/tiger@{instance_alias | net8_connection_string}

The instance_alias is an alias containing a database name defined in the tnsnames.ora file. For more information, talk to your database administrator, or see the manual Oracle Database Net Services Administrator's Guide.

If you do not include the password in the AUTHORIZATION clause, you can specify it when you run SQL*Module, using the USERID command line option. If you do not specify a USERID value that contains a password, SQL*Module prompts you for a password. If a database name is not supplied, SQL*Module connects using the default database for the specified user.


For security reasons, omit the password in the SQL*Module or in any configuration file. You will be prompted for the password at runtime.

An application that calls module procedures is in effect submitting SQL cursor declarations and SQL statements to Oracle for processing at runtime. The application runs with the privileges of the user executing the application, not the schema specified either in the AUTHORIZATION clause or at runtime, when the Module Language code was compiled by SQL*Module.

So, when the application is executed, the user must be able to connect to the database using the same schema as that specified when the modules were compiled, in order to guarantee access to all database objects referenced in the modules.

Cursor Declarations

When a query can return multiple rows of data, you must declare a cursor for the statement. There are two different kinds of cursor that you can use in Module Language. You can declare an ANSI-standard cursor in your module file, and write module language procedures to OPEN the cursor, FETCH from it, and CLOSE it. Or, you can use a cursor variable. Cursor variables are described in "Cursor Variables".

Using a cursor, you retrieve data one row at a time using the SQL FETCH command. Standard cursors are established in Module Language using the DECLARE CURSOR statement.


The cursor name is a SQL identifier; it is not a procedure name. So, the cursor name does not need to be a valid Ada identifier.

The SQL statement in a DECLARE CURSOR clause must not include an INTO clause. The INTO clause is used in the FETCH command in the procedure that references the cursor.

You cannot use parameters when you declare a standard cursor. A placeholder is used instead. Placeholders in a cursor declaration must correspond by name to the parameters in the procedure that opens the cursor.

Note the way the variable dept_number is used in the following module fragment, taken from the example used earlier in this chapter:

     SELECT ename, empno, sal, comm 
     FROM emp 
     WHERE deptno = :dept_number 
PROCEDURE open_cursor1 (:dept_number INTEGER, SQLCODE); 
     OPEN cursor1; 

The cursor declaration is NOT terminated with a semicolon. If it is, SQL*Module generates a syntax error.


If the application consists of several modules, a cursor cannot be declared in one module, then referenced in another. Nevertheless, cursor names must be unique across all modules in the application.

Procedure Definitions

A procedure definition consists of a

  • procedure name

  • parameter list

  • single SQL statement

Procedure Name

The procedure name is a SQL identifier, and should also be a legal Ada identifier. Procedure names must be unique in the application.

Each procedure in a module must have a distinct name. The procedure name space extends over all modules of an application, so when the application uses more than one module, each procedure must still have a distinct name.

Parameter List

The parameter list contains one or more formal parameters. Each parameter must have a distinct name, which is a SQL identifier. One of the parameters must be a status parameter: SQLSTATE or SQLCODE. It can appear anywhere in the parameter list. You can include both. See "Status Parameters" for more information about status parameters.

SQL92 Syntax

In SQL89 Module Language, you defined a procedure using the syntax

PROCEDURE proc_name 
        <param_1>    <datatype_1> 
        <param_2>    <datatype_2> 

where <param_n> is a formal parameter name and <datatype_n> is a SQL datatype. Following the SQL92 standard, SQL*Module allows the syntax

PROCEDURE proc_name ( 
        :<param_1>    <datatype_1>, 
        :<param_2>    <datatype_2>, 

where the parameter list is surrounded by parentheses, and parameters are separated by commas.


You cannot mix SQL89 and SQL92 syntax. If you separate the elements of the parameter list using commas, you must also place parentheses around the parameter list.

When SQL*Module generates the output procedures, the formal parameters appear with the same names, and in the same order, as they appear in the module procedure. You can use the parameter access conventions appropriate for the Ada language when calling the output procedure from the application. Thus, Ada programmers can use named parameter association in place of, or in addition to, positional parameter association.

SQL Datatypes

The following table lists the SQL and Oracle datatypes that you can use in a module parameter list. For more information about arrays, see "Arrays as Procedure Arguments".

Table 2-1 Datatypes

SQL Datatype Meaning


single character


character string of length L bytes


approximate numeric


exact numeric, no fractional part


approximate numeric


exact numeric, no fractional part, equal to or smaller in range than INTEGER

Oracle Datatype


variable-length character string of length L bytes

SQL*Module Datatypes:











cursor variable type

task context

Arrays of SQL Datatypes shown. N is the number of elements.


CHARACTER can be abbreviated CHAR. INTEGER can be abbreviated INT.

The SQL standard for Module Language allows the use of only a subset of the SQL datatypes for Ada.


All parameters for Module Language procedures must be scalars, arrays, or strings. Records and access types are not supported.

SQL Commands

Module Language supports the following SQL statements:

The DELETE and UPDATE commands may be either searched (the normal mode) or positioned (using the WHERE CURRENT OF <cursor_name> clause). You can use the OPEN command only for ANSI-standard cursors. You must open cursor variables on the Oracle Server, using PL/SQL code.

Text in a Module

In general, Module Language is not case sensitive. You can enter keywords and identifiers in any mixture of uppercase and lowercase. However, case is significant in character string literals.

Text in a module file is free form. White space (spaces, tabs, and new lines) can be placed anywhere in the file to improve readability. The only exception to this is that identifiers, keywords, and string literals cannot be continued to a new line.

The maximum length of a line in a module is 512 characters.


SQL*Module allows comments in a module file. You can place comments anywhere that white space can appear, except in string literals.

There are two comment styles: SQL-style comments and C-style comments. A SQL-style comment starts with two consecutive dashes, which can appear anywhere on a line, and ends at the end of the line. For example:

-- This is a SQL(or Ada)style comment. 
-- For multiline comments, you must place the comment 
-- dashes on each line. 

A C-style comment begins with a slash immediately followed by an asterisk (/*), and ends at the next occurrence of an asterisk immediately followed by a slash (*/). C-style comments can span more than one line. C-style comments cannot be nested.

The following example demonstrates the C-style comment:

 *  This comment style is often used to 
 *  introduce a procedure. 

Indicator Parameters

You use indicator parameters to set the null/not null status of another (associated) parameter, or to "indicate" if a column value retrieved on a query was null. In the module procedure parameter list, an indicator parameter always has a SMALLINT type. In the SQL statement, the indicator parameter follows the associated parameter, with no comma separation. The SQL92 standard allows the keyword INDICATOR to be used to separate the indicator parameter and its associated parameter.

In the following example, grade_indic is an indicator parameter:

PROCEDURE get_grade ( 
        :grade          REAL,
        :grade_indic    SMALLINT, 
        :class_number   INTEGER, 
        :student_id     INTEGER, 
    SELECT grade 
        INTO :grade INDICATOR :grade_indic 
        FROM enrollment
        WHERE class_no = :class_number AND student_id = :student_id; 

Following the SQL89 standard, the SELECT statement would be written without the INDICATOR keyword, as follows:

SELECT grade
         INTO :grade :grade_indic 
         FROM enrollment
         WHERE class_no = :class_number AND student_id = :student_id; 

SQL*Module allows both uses of indicator parameters.

When an indicator parameter is returned from a procedure (an OUT parameter), as in the query example, its returned value has the following meanings:


The Oracle column or expression is null. The value of the associated parameter (grade in this example) is indeterminate.


Oracle assigned a column or expression value to the associated parameter.

> 0

For character data, Oracle passes a truncated column value in the associated parameter. The value of the indicator parameter shows the original length of the value in the database column.

When indicator parameters are passed as IN parameters, you must set the value in your Ada program. A value of -1 means that Oracle will assign null to the column (regardless of the value of the associated parameter), and a value of zero or greater means that Oracle will use the value of the associated parameter in the UPDATE or INSERT command. Positive values greater than zero have no special meaning; they are equivalent to zero.


If you try to assign a null to a database column that has the NOT NULL constraint, an Oracle error is returned at runtime.

The following code fragment shows an Ada driver that calls a Module Language procedure with a null indicator parameter value:

procedure DRV is 
  EMPLOYEE   : string(1..10) := "SCOTT     "; 
  . . . 
  . . . 

The corresponding Module Language procedure is:

PROCEDURE update_commission ( 
  :employee_name VARCHAR2(10), 
  :commission REAL, 
  :comm_ind SMALLINT, 
  UPDATE emp SET comm = :commission INDICATOR :comm_ind 
     WHERE ename = :employee_name; 

In this example, the parameter commission with a value of 2000.0 is passed to the update_commission procedure. But, since the indicator parameter is set to -1, employee Scott's commission is set to null in the EMP table.

Status Parameters

There are two special status parameters: SQLSTATE and SQLCODE. The status parameters return values to the calling Ada application that show if

You can place either one or both of the status parameters anywhere in the parameter list. They are always shown last in this Guide, but that is just an informal coding convention. The status parameters are not preceded by a colon, and they do not take a datatype specification. You cannot directly access the status parameters in the SQL statement in the procedure; they are set by SQL*Module.

SQLSTATE is the preferred status parameter; SQLCODE is retained for compatibility with older standards.

For more information about the status parameters and the values they return, see "Error Handling".

Error Messages

SQL*Module compile time error messages have the MOD prefix. The codes and messages are listed in Oracle Database Error Messages.

CONNECT Statement

The connect statement associates a program with a database, and sets the current connection. The syntax of the command is shown in the following syntax diagram. Key words, which you must spell exactly as shown, are in upper case; tokens you supply are in lower case. If a clause is optional, it is shown off the main path, which flows to from left to right. For a more complete discussion of syntax diagrams, see Appendix B, "Module Language Syntax".

Description of adaconn.gif follows
Description of the illustration adaconn.gif

A db-env (database environment) is a Oracle Net connect string or instance-alias. The conn-name (connection name) is optional. For multiple connections, you must specify the connection names. You can omit it if you will only use a single connection. The USING clause is optional. A passwd is the password.

Connecting as DEFAULT results in a connection to Oracle by using either TWO_TASK (if it applies to your operating system) or ORACLE_SID and the account specified by the parameter os_authent_prefix in your file init.ora. The optional token passwd is the password.

The ANSI SQL92 standard does not allow db-env to be optional. This is an Oracle extension (which will be flagged by the FIPS option) which enables a connection to the default server as a specific user.

You must use Oracle Net in SQL*Module applications. Note that passwd can only be a variable and not a character string literal. All other variables can be either a character string literal or a variable previously defined, preceded by ":".

Here is a illustrative code fragment from a module named demo.mad, which contains the following procedure to do a connect:

PROCEDURE ora_connect (:dbid   VARCHAR2(14),
                       :dbname VARCHAR2(14),
                       :uid    VARCHAR2(14),
                       :pwd    VARCHAR2(14),
CONNECT TO :dbid AS :dbname USER :uid USING :pwd;

An Ada application can contain these statements to do the connect:
pwd : constant string := "tiger";
if SQLCODE /= 0 then

For more information, see the Oracle Database Net Services Administrator's Guide.


The set connection statement sets the current connection. Its syntax is:

SET CONNECTION { connection-name | DEFAULT }

DEFAULT is a special case of the connection-name, '/', at the current ORACLE_SID.


The disconnect command ends an association between an application and a database environment. It can be summarized as:

DISCONNECT { connection-name | ALL | CURRENT | DEFAULT}

The full ANSI semantics are not followed. Instead of raising an exception condition if the connection has an active transaction, the transaction is (implicitly) rolled back and the connection(s) disconnected.

DISCONNECT ALL only disconnects connections which are established by SQLLIB (that is, by SQL*Module).

DISCONNECT DEFAULT and DISCONNECT connection-name terminate only the specified connection.

DISCONNECT CURRENT terminates the connection regarded by SQLLIB as the "current connection" (either the last connection established or the last connection specified in a SET CONNECTION statement).

After the current connection is disconnected, you must execute a set connection or a connect statement to establish a new current connection. Then you can execute any statement that requires a valid database connection.

You must explicitly commit or roll back the current transaction before disconnecting. If you do not commit the transaction before disconnecting, or if the program exits before committing, the current transaction is automatically rolled back.

Here is an example of two procedures from the module demo.mad illustrating the disconnect command:

PROCEDURE ora_disconnect (:dbname VARCHAR2(14),
                           SQLCODE) ;
   DISCONNECT :dbname;


these procedures are called from your application as follows:

A required commit command was executed using the procedure ora_commit (which is also in the file demo.mad) just before the disconnect.


Starting with release 8.0, multi-tasking Ada programs are supported by SQL*Module. The new commands that you use in a multi-tasking program are described in the following sections:


This command initializes the process for later use with Ada tasks. It must be called prior to the creation of any Ada tasks. It is:



The datatype SQL_CONTEXT is used to support multi-tasking applications. It points to SQLLIB's runtime context. You pass the context as a parameter to SQL*Module procedures. If it is passed, then it indicates which SQLLIB runtime context will be used for execution. If no SQL_CONTEXT parameter is passed, then SQL*Module uses the global runtime context of SQLLIB.

For example, here is a procedure that uses SQL_CONTEXT:

                   :dno INTEGER,
    SELECT deptno INTO :dno FROM emp WHERE dname = 'SALES';

In this example, the select statement will use the runtime context pointed to by the variable ctx. ctx must have been previously allocated in a CONTEXT ALLOCATE statement. Note that you never reference the SQL_CONTEXT variable directly. It appears only in the code that SQL*Module generates.


This command allocates storage in memory for a SQLLIB runtime context that is used to support multi-tasking. An example is:


ctxvar is of type SQL_CONTEXT. If sufficient storage is available, ctxvar will contain the address of the runtime context. Otherwise, ctxvar will be zero and an error will be returned in the provided status variables.


CONTEXT FREE frees all storage associated with the SQLLIB runtime context. It does not disconnect any active connection. Prior to deallocating the runtime context, you must execute the DISCONNECT command for each active connection.

The CONTEXT FREE statement always succeeds and ctxvar is zero after execution. If the context is ctxvar, then, an example is:


Multi-tasking Restrictions

  • All database connections must be established prior to task activation.

  • Multi-tasking applications cannot be used to access database stored procedures.

Multi-tasking Example

Here is part of a module, adademo.mad:

PROCEDURE enable_threads (SQLCODE);
PROCEDURE allocate_context (:ctx SQL_CONTEXT, SQLCODE);
    CONTEXT FREE :ctx;
PROCEDURE disconn_db (:ctx SQL_CONTEXT,
                      :dbname VARCHAR2(14),
   DISCONNECT :dbname;

these procedures are called as follows:
-- Declare contexts CTX1, CTX2

-- Spawn tasks that process data:

An example of explicitly disconnecting and freeing contexts is:
-- After processing data: