Skip Headers
Oracle® SQL*Module for Ada Programmer's Guide
11g Release 1 (11.1)
  Go To Table Of Contents
Go To Index


1 Introduction to SQL*Module

This chapter introduces you to SQL*Module and answers the following questions:

What Is SQL*Module?

You use SQL*Module to develop and manage Ada applications that access data in an Oracle database. It allows an Ada application to access relational databases without using embedded SQL, and without using a proprietary application programming interface.

SQL*Module fulfills three roles in application development:


The SQL language was designed to operate on entities in a relational database. SQL was not designed to be a general-purpose programming language, and, in fact, it is conceptually very different from 3GLs such as Ada or C. Some of these differences are:

  • SQL is a non-procedural database manipulation language, hence it deals with database objects, such as tables, rows, columns, and cursors. A third-generation language deals with data structures such as scalar variables, arrays, records, and lists.

  • SQL has a limited ability to express complicated computational operations.

  • SQL does not provide the procedural capabilities (such as flow of control statements) that are required to implement efficient programs.

To achieve maximum flexibility when creating large applications, you must combine SQL with host procedural programming language statements. There are several ways to do this, and these are discussed briefly in the next section.


One way to use a procedural language to access data in a relational database is to embed SQL statements directly in a program written in a host 3GL such as C or C++. After the program is coded, you use a precompiler to translate the SQL statements into calls to a runtime library that processes the SQL, and submits the SQL statements to the database.

See Figure 1-1, "Developing with the Precompilers" for details of this process.

While embedded SQL is very useful, it can have drawbacks when very large applications are being developed. There are several reasons for this:

  • Use of embedded SQL requires study of the technical details of the precompiler.

  • SQL code does not follow the syntactic and semantic constraints of the host language, and can confuse specialized tools, such as syntax-directed editors and "lint" programs.

  • When the precompiler processes code that contains embedded SQL, it introduces extra data structures and code in the generated output code, making source-level debugging more difficult.

  • Techniques for error handling and recovery in embedded SQL programs can be difficult to understand and apply, and subtle bugs can arise when developers do not appreciate all the problems involved in embedded SQL error handling.

shows how you develop applications using the Oracle precompilers.

Figure 1-1 Developing with the Precompilers

Description of Figure 1-1 follows
Description of "Figure 1-1 Developing with the Precompilers"

The Module Language Concept

The ANSI SQL standards committee defined the embedded SQL standard in two steps. A formalism called Module Language was defined, then the embedded SQL standard was derived from Module Language.

Using Module Language is very straightforward: place all SQL code in a separate module, and define an interface between the module containing the SQL code and the host program written in Ada. At the most concrete level, the interface simply consists of

  • a standard way of calling Module Language procedures from Ada.

  • a standard way to return error and warning information

  • specification of conversions between SQL datatypes (such as DOUBLE PRECISION or SMALLINT) and host language datatypes or derived types defined in a standard package in Ada

It is also possible to develop more abstract interfaces between the host language and Module Language. One example of this is the SAMeDL (SQL Ada Module Description Language) developed at Carnegie Mellon and the Software Engineering Institute.

shows how you would develop an application using SQL standard Module Language.

Figure 1-2 Developing with Module Language

Description of Figure 1-2 follows
Description of "Figure 1-2 Developing with Module Language"

A Module Language compiler such as SQL*Module generates a call-level interface to procedures defined within a module, allowing them to be called like any other host language procedure. Details of the implementation of these procedures are hidden from the application.

The most immediate benefit of this approach is specialization. By separating SQL and the host language, an application developer can focus on using the host language to perform application tasks, and a database developer can focus on using SQL to perform database tasks.

The developer of the application does not need to know SQL. The procedures to be called can be treated as canned routines that return variables of well defined datatypes in the host language. Error handling becomes straightforward, since all module procedures can return a single error indicator.

shows the operation of SQL*Module when it is used to compile SQL standard modules. The module file contains a preamble, defining the host 3GL to be Ada, and two simple procedures in Module Language. When SQL*Module compiles this module, it produces two output files: a source code output file, that contains calls to the runtime library SQLLIB that do the work of accessing the database, and a specification file that declares the procedures in the source code file.

You include the specification files in the host application code that calls the module procedures, using the appropriate language-specific mechanism.

Figure 1-3 Compiling a Module

Description of Figure 1-3 follows
Description of "Figure 1-3 Compiling a Module"

SQL*Module as an Interface Builder

In addition to its role as a Module Language compiler, SQL*Module can also build host language interfaces to procedures that are stored in an Oracle database.

shows schematically how SQL*Module functions as an interface builder. The compiler extracts the interfaces to stored procedures, and creates an Ada output file that contains calls to the procedures. Your Ada application then accesses the stored procedures in the database by calling these interface procedures.

When you create interface procedure files (output files that contain interface procedures for calling stored procedures), you can choose either early binding or late binding.

The early binding option creates a time stamp in the interface procedure for the time that the stored procedure was last compiled. If the stored procedure has been recompiled after the interface procedure was generated, a runtime error is generated when the interface procedure is called from the host application.

The late binding option calls the stored procedure through an anonymous PL/SQL block, and no time stamp is used. See "Early and Late Binding" for more information about binding.

Figure 1-4 SQL*Module as an Interface Builder

Description of Figure 1-4 follows
Description of "Figure 1-4 SQL*Module as an Interface Builder"

What Is Supported by SQL*Module?

SQL*Module supports international standards for Module Language. Refer to the Preface to this Guide for more information about supported standards. In addition, Oracle has extended the current standard in several ways. For example, datatype conversions between Oracle datatypes and Ada datatypes are defined, comments can be used in a module, and so forth. Chapter 2, "Module Language" describes the Module Language capabilities of SQL*Module in detail. A compile time option, the FIPS flagger, is available to flag use of non-standard extensions to Module Language and to SQL.

In addition to the complete Module Language standard, SQL*Module also provides a way for a host application to access PL/SQL stored procedures in an Oracle database. If a package exists in an Oracle database that contains procedures, you can use SQL*Module to build interface procedures corresponding to the PL/SQL procedures in the package. Thus the application can call the stored procedures directly.

SQL*Module generates code and specification files that can be compiled with the Ada compiler.

You can compile your stored procedures or modules to get an interface procedures file and call the modules directly from an Ada host program.

What SQL Statements are Not Supported by SQL*Module?