SQL*Module for Ada Programmer's Guide Release 8.0 A58231-01 |
Library | Product | Contents | Index

# 2
Module Language

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

- The Module
- Structure of a Module
- SQL Datatypes
- SQL Commands
- Text in a Module
- Indicator Parameters
- Status Parameters
- CONNECT Statement
- DISCONNECT Statement
- Multi-tasking
- Multi-tasking Example

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

- introductory material, in a preamble
- optional cursor declarations for use by queries that can return multiple rows of data
- definitions of procedures to be called by the host application

The easiest way to see how a module is constructed is to look at an example. The small module below 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.

    DECLARE cursor1 CURSOR FOR
        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,
             SQLCODE);

        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,
             SQLCODE);

    -- the SQL command is a FETCH on the cursor
        FETCH cursor1
            INTO :empname,
                 :empno,
                 :sal,
                 :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),
            SQLCODE);

        CONNECT TO :dbname USER :username USING :passwd;

    -- Disconnect
    PROCEDURE do_disconnect (SQLCODE);

        DISCONNECT CURRENT;

Note:`` 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" on page 2-2 follows.

    -- Include TEXT_IO,SQL_STANDARD and EXAMPLE1_MOD package specs.

    with
         SQL_STANDARD,
         TEXT_IO,
         EXAMPLE1_MOD;
    use
         SQL_STANDARD;

    -- 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       ";

        DEPT_NUMBER          : SQL_STANDARD.INT;
        EMPLOYEE_NUMBER      : SQL_STANDARD.INT;
        EMPLOYEE_NAME        : string(1..10);
        SALARY               : SQL_STANDARD.REAL;
        COMMISSION           : SQL_STANDARD.REAL;
        COMM_IND             : SQL_STANDARD.SMALLINT;
        SQLCODE              : SQL_STANDARD.SQLCODE_TYPE;
        LENGTH               : integer;

        CONNECT_ERROR        : exception;
        SQLCODE_ERROR        : exception;

    begin

    -- Call a module procedure to connect
    -- to the Oracle server.
        EXAMPLE1_MOD.DO_CONNECT
            (SERVICE_NAME, USERNAME, PASSWORD, SQLCODE);

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

        TEXT_IO.NEW_LINE(2);

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

    -- Call the module procedure to open the cursor.
    -- You open the cursor using the input parameter (dept_number).
        EXAMPLE1_MOD.OPEN_CURSOR1(DEPT_NUMBER, SQLCODE);
    -- 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");
        TEXT_IO.PUT("----------------------------------------");

    -- Call the FETCH procedure in a loop, to get
    -- the employee data.
        loop
            EXAMPLE1_MOD.FETCH_EMP_DATA
                   (EMPLOYEE_NUMBER,
                    EMPLOYEE_NAME,
                    SALARY,
                    COMMISSION,
                    COMM_IND,
                    SQLCODE);

            TEXT_IO.NEW_LINE;

    -- 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;

            TEXT_IO.PUT(string(EMPLOYEE_NAME));
            STD_INT_IO.PUT(EMPLOYEE_NUMBER, WIDTH => 9);
            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);
            else
                TEXT_IO.PUT("        Null");
            end if;
        end loop;

        TEXT_IO.NEW_LINE(2);

    -- Call the procedure to close the cursor.
        EXAMPLE1_MOD.CLOSE_CURSOR1(SQLCODE);

    -- Call the procedure to disconnect.
        EXAMPLE1_MOD.DO_DISCONNECT(SQLCODE);

    -- Handle the error exception.

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

         when CONNECT_ERROR =>
              TEXT_IO.PUT("Connect failure to " &
                           string(SERVICE_NAME));
              TEXT_IO.NEW_LINE(2);

    end EXAMPLE1_DRV;

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" on page 2-17 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

- a preamble
- zero or more cursor declarations
- one or more procedure definitions

### Preamble

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

- the MODULE clause
- the LANGUAGE clause
- the AUTHORIZATION clause

#### MODULE Clause

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

Note: 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.

#### LANGUAGE Clause

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.

#### AUTHORIZATION Clause

The AUTHORIZATION clause is required. This 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 one of 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 Net8 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.

Note: 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" on page 3-9.

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.

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

    DECLARE cursor1 CURSOR FOR
         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 you do so, SQL*Module generates a syntax error.

Caution: 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" on page 2-17 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>
            SQLCODE;
        <sql_statement>;

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>,
            SQLSTATE | SQLCODE );
        <sql_statement>;

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

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

Table 2-1 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" on page 4-9.

##### Table 2-1 Datatypes

SQL Datatype | Meaning
CHARACTER | single character
CHARACTER(L) | character string of length L bytes
DOUBLE PRECISION | approximate numeric
INTEGER | exact numeric, no fractional part
REAL | approximate numeric
SMALLINT | exact numeric, no fractional part, equal to or smaller in range than INTEGER
Oracle Datatype |
VARCHAR2(L) | variable-length character string of length L bytes
SQL*Module Datatypes: SQL_CURSOR SQL_CONTEXT ARRAY(N) OF SQL_CURSOR ARRAY(N) OF CHARACTER ARRAY(N) OF CHARACTER(L) ARRAY(N) OF DOUBLE PRECISION ARRAY(N) OF INTEGER ARRAY(N) OF REAL ARRAY(N) OF SMALLINT ARRAY(N) OF VARCHAR2(L) | cursor variable type task context Arrays of SQL Datatypes shown above. N is the number of elements.
Note: 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.

Note: 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 statement:

- ALLOCATE
- CLOSE
- COMMIT
- CONNECT TO
- CONTEXT ALLOCATE
- CONTEXT FREE
- DELETE
- DISCONNECT
- ENABLE THREADS
- FETCH
- INSERT
- OPEN
- ROLLBACK
- SELECT
- SET CONNECTION
- UPDATE

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. Case is significant, however, 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.

### Comments

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,
            SQLCODE);
        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 above 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 above, its returned value has the following meanings:

-1

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

0

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

> 0

For character data, Oracle passed 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.

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

    with SQL_STANDARD;
    procedure DRV is
      SQLCODE    : SQL_STANDARD.SQLCODE_TYPE;
      EMPLOYEE   : string(1..10) := "SCOTT     ";
      COMMISSION : SQL_STANDARD.REAL := 2000.0;
      COMM_IND   : SQL_STANDARD.SMALLINT := -1;
    begin
      . . .

      UPDATE_COMMISSION(EMPLOYEE, COMMISSION, COMM_IND, SQLCODE);

      . . .
    end;

The corresponding Module Language procedure is:

    PROCEDURE update_commission (
      :employee_name VARCHAR2(10),
      :commission REAL,
      :comm_ind SMALLINT,
      SQLCODE);

      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

- the procedure completed without error
- an exception occurred, such as ``no data found''
- an error occurred

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" on page 4-2.

### Error Messages

SQL*Module compile time error messages have the MOD prefix. The codes and messages are listed in Oracle8 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".

A db-env (database environment) is a Net8 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 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 Net8 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 illustative 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),
                           SQLCODE);
    CONNECT TO :dbid AS :dbname USER :uid USING :pwd;
    ...

An Ada application can contain these statements to do the connect:

    ...
    pwd : constant string := "tiger";
    ...
    DEMO.ORA_CONNECT("inst1","RMT1","scott",PWD,SQLCODE);
    if SQLCODE /= 0 then
    ...

For more information, see the Net8 Administrator's Guide and the Oracle8 Administrator's Guide.

### SET CONNECTION Statement

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.

## DISCONNECT Statement

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;

    ...
    PROCEDURE ora_commit(SQLCODE);
       COMMIT WORK;

these procedures are called from your application as follows:

    ...
    DEMO.ORA_COMMIT(SQLCODE);
    DEMO.ORA_DISCONNECT("RMT1", SQLCODE);
    ...

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

## Multi-tasking

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:

### ENABLE THREADS

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:

    ENABLE THREADS;

### SQL_CONTEXT Datatype

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:

    PROCEDURE seldept (:ctx SQL_CONTEXT,
                       :dno INTEGER,
                        SQLCODE);
        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.

### CONTEXT ALLOCATE

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

    CONTEXT ALLOCATE :ctxvar;

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

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:

    CONTEXT FREE :ctxvar;

### 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);
        ENABLE THREADS;
    PROCEDURE allocate_context (:ctx SQL_CONTEXT, SQLCODE);
        CONTEXT ALLOCATE :ctx;
    PROCEDURE free_context (:ctx SQL_CONTEXT, SQLCODE);
        CONTEXT FREE :ctx;
    PROCEDURE disconn_db (:ctx SQL_CONTEXT,
                          :dbname VARCHAR2(14),
                          SQLCODE);
       DISCONNECT :dbname;

these procedures are called as follows:

    with ADADEMO;
    -- Declare contexts CTX1, CTX2
    ...
    ADADEMO.ENABLE_THREADS(SQLCODE):

    ADADEMO.ALLOCATE_CONTEXT(CTX1, SQLCODE);
    ADADEMO.ALLOCATE_CONTEXT(CTX2, SQLCODE);
    -- Spawn tasks that process data:
    ...

An example of explicitly disconnecting and freeing contexts is:

    -- After processing data:
    ...
    ADADEMO.DISCONN_DB(CTX1, DBNAM1);
    ADADEMO.DISCONN_DB(CTX2, DBNAM2);
    ADADEMO.FREE_CONTEXT(CTX1);
    ADADEMO.FREE_CONTEXT(CTX2);

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