2 Module Language

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" 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" for more information.

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

- scott 

- scott/tiger

scott@{instance_alias | net8_connection_string}
}

- scott/tiger@{instance_alias | net8_connection_string}

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.

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 it is, 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 proc_name 
        <param_1>    <datatype_1> 
        <param_2>    <datatype_2> 
        SQLCODE; 
    <sql_statement>; 

PROCEDURE proc_name ( 
        :<param_1>    <datatype_1>, 
        :<param_2>    <datatype_2>, 
        SQLSTATE | SQLCODE );
    <sql_statement>; 

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. 
 */ 

Error Messages

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

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.

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.