5 Running SQL*Module

This chapter describes

SQL*Module Input and Output

This section reviews the different ways that you can use the SQL*Module compiler. This material was discussed in detail in Chapter 2, "Module Language" and Chapter 3, "Accessing Stored Procedures"; here it is presented in terms of the ways that you run the compiler, using the command-line options to get different SQL*Module functionality.

Input sources

Input to the compiler can come from two sources:

You use a standard text editor to create module files, just as you would create a host language application.

Stored procedures can be standalone procedures, or they can be encapsulated in a stored package. You normally create PL/SQL code for stored packages and procedures using a text editor, and then store it in a database using an Oracle tool such as SQL*Plus. You can also use SQL*Module to encapsulate Module Language procedures in a package, and store them in the database.

Output Files

The output source file is always the host language code file that SQL*Module generates from the input source. There are also other output files, such as the listing file and specification file. You can run SQL*Module and generate no output source file, for example if you just want to store procedures in the database from a Module Language input file, or you just want to generate a listing file.

You compile output source files using the host language compiler, and link the resulting object files together with the host application's object files to produce the executable program. See the section "Compiling and Linking" for more information about handling output files.

Note:

While many of the examples in this chapter assume, for simplicity, that the input and output files are in the same directory, this does not have to be the case. Input and output files can be in separate directories, and you can use the various NAME options to specify the source of input, or the destination of output.

Determining the Input Source

There are three sources of input for SQL*Module, and four ways to determine the input:

  1. When compiling a module written in Module Language, the source is the Module Language code file.

  2. When generating RPC stubs from stored procedures, there is no input file. The source of the input is the stored package in the database.

  3. When creating a stored package in the database from a Module Language module file, the source is the Module Language file.

  4. You can combine methods 1 and 2 in one invocation of SQL*Module. A package in the database is created from the Module Language module file, and an output file that contains RPC stubs to call the database package procedures is produced.

Methods 1 and 4 are the most common ways to use SQL*Module. Method 1 is described in Chapter 2, "Module Language" of this Guide, method 2 in Chapter 3, "Accessing Stored Procedures". Methods 3 and 4 are much more specialized, and are described in Chapter 3, "Accessing Stored Procedures".

STORE_PACKAGE

Determines whether SQL*Module should store a package in the database.

RPC_GENERATE

Determines whether an interface procedure output file is produced. When you specify the option RPC_GENERATE as YES, the option PNAME specifies the name of the package in the database that provides the input source.

Table 5-1 shows the how the command-line option values for STORE_PACKAGE and RPC_GENERATE, together with the values for INAME and PNAME, determine the input source.

Table 5-1 Datatypes

Input Source Options
 

STORE_

PACKAGE

RPC_

GENERATE

INAME

PNAME

(1) Module source file

=NO

=NO

Module file name

N/A

(2) Procedure already stored in database

=NO

=YES

N/A

Stored package or procedure name

(3) Module file to create SPs in database

=YES

=NO

Module file name

Database package name (if not specified, becomes same as module filename)

(4) Store module procedures, then do (2)

=YES

=YES

Module file name

Database package name (if not specified, becomes same as module filename)


See the section "Command-Line Options" for a detailed description of these options. See the section "Compiling and Linking", for examples that show you how you can use these options. For an explanation of the default file naming conventions, see the sections "Input Files" and "Output Files".

Invoking SQL*Module

You can run the SQL*Module compiler interactively from the operating system command line, from a command in a batch file, or for some operating systems, a makefile. The way you invoke the compiler can be system dependent. See your system-specific Oracle documentation to find out the location on your system of the compiler and associated files, such as configuration files and the SQL runtime library.

Running the Compiler

The name of the SQL*Module compiler itself is modada for Ada. The SQL*Module compiler can be invoked from the operating system command line as follows:

modada  <option=value> ... 

where <option=value> is a command-line argument. For example, the command

modada iname=my_test1.mad oname=my_test1_mod.a userid=modtest 

compiles the module file my_test1.mad to produce an output file called my_test1_mod.a. The username is modtest. In this example no password was provided on the command line. As a result, SQL*Module prompts you for one when it starts. SQL*Module requires a valid username and password to compile a Module Language file. The objects referenced in the cursors and procedures in the Module file must be available in the schema named (MODTEST in this example) when you run SQL*Module.

When you use SQL*Module to generate interface procedure files that call stored procedures in the database, you must specify the same USERID as the schema that owns the stored procedures.

Case Sensitivity in Program Names, Option Names, and Values

For operating systems that are case sensitive, such as UNIX, the names of the executables are normally in lowercase. For all systems, the names of the options and their values are not case sensitive. In this Guide, the option name is in uppercase, and the value is in lower case. However, when the option value is a filename, and your operating system is case-sensitive, you must enter the filename using the correct combination of upper and lowercase

Listing Options and Default Values

If you provide no command-line arguments, or the only argument is '?', the compiler prints a list of all the options available, with their current default values. For example, the command

modada ?

runs the SQL*Module compiler for Ada and lists each option with its default value. See "Default Values" for information on what determines the defaults. (Be sure to escape the '?' using '\' if you are running on a UNIX system and you are using the C shell.)

If you just want to see the default value for a single option, you can issue the command:

modada <OPTION>=?

For example, the command

modada OUTPUT=? 

shows the default values for the OUTPUT option for the SQL*Module compiler for Ada.

modada

produces a short help display.

A complete description of each option is given later in this chapter.

How to Specify Command-Line Options

The value of an option is a string literal, which can represent text or numeric values. For example, for the option

INAME=my_test 

the value is a string literal that specifies a filename. But for the option

MAXLITERAL=400 

the value is numeric.

Some options take Boolean values, and these may be represented with the strings "yes" or "no", or "true" or "false" (in upper or lowercase). For example:

...  STORE_PACKAGE=YES 

is equivalent to

...  STORE_PACKAGE=true 

both of which mean that the results of the compilation should be stored as a package in the database.

The option value is always separated from the option name by an equals sign, with no whitespace between the name or the value and the equals sign.

Value Lists

Some options can take multiple values. Multiple option values are specified in a list. The list is a comma-delimited list of values with surrounding parentheses. Do not put any whitespace in the list. The following option specifies that SQL*Module should generate source code and specification output files, but not listing files:

...  OUTPUT=(CODE,SPECIFICATION) 

A value list completely supersedes the value list specified by a previous default or option value list. For example, if the system configuration file contains the line

OUTPUT=(CODE,SPECIFICATION,LIST) 

and there is no user configuration file, and the command line contains the option

...  OUTPUT=(CODE,LIST) 

then the value of OUTPUT is (CODE,LIST). See the section "Configuration Files" for how default values are determined.

If a list-valued option is specified with a single value, that is not in parentheses, the single value is added to the current default list. For example, if the system configuration file contains the line

OUTPUT=(CODE,SPECIFICATION) 

there is no user configuration file that has an OUTPUT= option, and the command line contains the option

...  OUTPUT=LIST 

then "LIST" is appended to the default list, so the value of OUTPUT is (CODE,SPECIFICATION,LIST).

Note: If NONE is a member of the OUTPUT list, then nothing would be generated, regardless of other entries in the list.

Default Values

Most of the options have default values. Three things determine the default value:

  • values built into the SQL*Module compiler

  • values set in the system configuration file

  • values set in a user configuration file

For example, the option MAXLITERAL specifies the maximum length of strings generated by SQL*Module. The built-in SQL*Module default value for this option is 255 bytes. However, if MAXLITERAL=512 is specified in the system configuration file, the default now becomes 512. The user configuration file could set it to yet another value, which then overrides the system configuration value. Finally, if this option is set on the command line, that value will take precedence over the SQL*Module default, the system configuration file specification, and the user configuration file specification. See "Configuration Files" for more information about these files.

Some options, such as USERID, do not have a built-in default value. The built-in default values for options that have them are listed in the section "Command-Line Options".

Configuration Files

A configuration file is a text file that contains SQL*Module options. Each record or line in the file contains one option, with its associated value or list of values. For example, a configuration file might contain the lines

BINDING=LATE
USERID=MODTEST 

to set defaults for the BINDING and USERID options.

Note:

You cannot put comments in a configuration file; there is no character or character combination that lets you comment out a line.

There is one systemwide configuration file associated with each system. The system configuration file is usually maintained by the project or group leader, or the database administrator. The location of this file is system specific. For more information, see your project leader, or your system-specific Oracle documentation.

If there is no system configuration file, the compiler prints a warning message, but compilation continues normally.

In addition, each SQL*Module user can have one or more user (or local) configuration files. To activate the user configuration file, its name and path must be specified using the CONFIG= command-line option. See "Command-Line Options". The user configuration file is optional.

The CONFIG= option never specifies the system configuration file. The location of the system configuration file is built into the SQL*Module compiler, and can vary from system to system.

Input Files

A SQL*Module input file is a text file containing Module Language statements. You specify the input filename using the INAME= command-line option.

Input files have default file extensions, also referred to as filetypes in some operating systems. However, not all operating systems support file extensions. If your system does not support file extensions, the last few characters of the filename might serve as the extension. Refer to your operating system documentation and to your system-specific Oracle documentation for more information about filenames and file extensions.

If you do not specify an extension for the module input file, and your operating system uses file extensions, the compiler assumes a default extension, .mad.

Output Files

SQL*Module can generate four types of output files:

  • a source code file

  • a specification (or header) file

  • a listing file

  • a PL/SQL source file for a stored procedure or a package

Source code files contain generated code in the host language. modada generates Ada code. Specification or header files contain declarations for the procedures in the code files

Source Code Output File

This file contains the host language code produced by the compiler. It is a source file in the host language, and you must compile it using your host language compiler to produce an object module. The object modules are in turn linked with the application's object modules and the SQL runtime library to form the executable program.

Note:

Oracle recommends that you name output files explicitly, either in a configuration file or on the command line.

Default File Names for Ada

If you do not specify an output code filename when you run modada, the output code filename defaults to a system-specific name. For example, on Sun workstations running the Solaris 1.0 Sun Ada compiler, the command

modada iname=my_test1.mad

generates an output code file named my_test1.a. On other platforms, a different name might be generated. See your system-specific Oracle documentation for complete information.

Specification File

By default, modada generates a specification or header file. The specification file contains declarations for the procedures in the generated output file.

Default Specification Filenames for Ada

The default specification filename is the name of the input file, or the package name, followed by a system-dependent appendix, followed by a system-dependent file extension. For example, on a Sun workstation running Solaris 1.0, the command

modada iname=my_test1.mad

generates a default specification output file with the name my_test1s.a. This is the value of iname minus the extension, with "s" appended.

On other platforms, the filename appendix and the filename extension might be different. See your system-specific Oracle documentation for complete information.

See Chapter 6, "Demonstration Programs" for language-dependent information about the content of specification files.

Listing File

If OUTPUT=LIST, SQL*Module produces a listing of the Module Language source code, with errors, if any, flagged. Error codes and messages are interspersed in the list file, at the point where the SQL*Module parser first detected the error. The line length in the listing file defaults to 255 characters. If no end-of-line character is received before 255 characters are received, a system-specific end-of-line character or character sequence is output.

PL/SQL Source Files

When you are generating interface procedure files from a stored package or procedure, and you specify the option OUTPUT=PACKAGE, SQL*Module generates PL/SQL source code output files. If the output is from a package, two files are generated. One file has the default file extension .pks, and contains the package specification code. The second file has the default extension .pkb, and contains the package body code. See the Oracle Database PL/SQL Language Reference for more information on package specifications and package bodies.

Avoid Default Output Filenames

Use the ONAME and SNAME options to generate nondefault output filenames. They are described later.

Oracle strongly recommends that you use these options, rather than letting the output filenames be generated by default.

Command-Line Options

When an option is entered on the command line, its value overrides SQL*Module defaults, any values specified in a configuration file, or values specified in a module file (for example, the AUTHORIZATION clause). The order of precedence is

  • command-line options

  • statements in the module file preamble

  • user configuration file options

  • system configuration file options

  • default options built into the compiler

The format of a command-line option is:

OPTION_NAME=VALUE 

There should be no whitespace around the equals sign. For example:

modada INAME=my_app3_mod ONAME=my_app3_mod SNAME=my_app3_pkg 

compiles the input file my_app3_mod.mad to produce an output file named my_app3_mod.a, and a specification file named my_app3_mod_pkgs.a.

Note:

The actual filename extensions are system specific. See your system-specific Oracle documentation for more information.

If the option can take a list of values and more than one value is being supplied, a comma-delimited list of values is placed inside parentheses. For example:

OUTPUT=(CODE,SPECIFICATION) 

There should be no whitespace anywhere in the list.

The names as well as arguments of the command-line options can be abbreviated. Instead of

OUTPUT=SPECIFICATION 

you could enter

OUT=SPEC 

or even

OU=SP 

since neither "OU", "SPEC", nor "SP" is ambiguous. Filenames and package names cannot be abbreviated.

The command-line options, together with their default values, are listed in Table 5-2, and are described in the remainder of this chapter.

Table 5-2 The Command-line options

Option Name Option Purpose Values

AUTO_CONNECT

Connect on first SQL statement if not already connected

YES | NO

BINDING

Early or late binding?

EARLY | LATE

CONFIG

Name of a user configuration file

<filename>

ERRORS

Destination of error messages

YES | NO

FIPS

Turns on FIPS flagger

YES | NO

INAME

Name of input file

<filename>

LNAME

Name of listing file

<filename>

LTYPE

Kind of listing file

NONE | SHORT | LONG

MAPPING

Resolves overloaded procedure names for the default WITH INTERFACE PROCEDURE clause

( ) | OVERLOAD

MAXLITERAL

Maximum length of string literal in generated host language code

10..1024

ONAME

Name of source code output file

<filename>

OUTPUT

Kinds of output files generated

One of, or list of two or more of (NONE | CODE | SPECIFICATION | LIST | PACKAGE

PNAME

Name of package in the database

<package_name>

RPC_GENERATE

Generate stubs from stored package or procedure?

YES | NO

SELECT_ERROR

Should a query returning more than one row generate a runtime error?

YES | NO

SNAME

Name of specification output file

<filename>

SQLCHECK

Kind of compile-time checking done

NONE | SYNTAX | SEMANTICS

STORE_PACKAGE

Store module as a package in the database

YES | NO

USERID

Username and password

<string>


AUTO_CONNECT

Values

{YES | NO}

Default Value

NO

Meaning

If AUTO_CONNECT=YES, and you are not already connected to a database, when SQLLIB processes the first executable SQL statement, it attempts to connect using the userid

OPS$<username>

where username is your current operating system user or task name and nullusername is a valid Oracle userid.

When AUTO_CONNECT=NO, you must use the CONNECT statement to connect to Oracle.

Can be entered only on the command line or in a configuration file.

BINDING

Values

{EARLY | LATE}

Default Value

EARLY

Meaning

The BINDING option is used when generating interface procedure files, that is, when RPC_GENERATE=YES. Early binding means that a time stamp is derived from the time of compilation of the stored procedure, and the time stamp is saved in the interface procedures file.

When a stored procedure is called through a stub (specified in the interface procedures file), if the current time stamp on the procedure in the database is later than that specified in the stub, the message "time stamp of <stored procedure name> has been changed" (ORA-04062) is returned.

The stored package must have WITH INTERFACE clauses specified for each procedure when RPC_GENERATE=YES, regardless of whether you choose early or late binding using the BINDING option. See the section "Early and Late Binding" for more information.

CONFIG

Values

<filename>

Default Value

None.

Meaning

Specifies the name of a user configuration file that contains options. The user configuration file is a text file. Each option in the file must be on a separate line (record).

ERRORS

Values

{YES | NO}

Default Value

YES

Meaning

Specifies the destination for error message output. If ERRORS=YES, the output is both to the terminal and to the listing (.lis) file. If ERRORS=NO, error messages are sent only to the listing file.

FIPS

Values

{YES | NO}

Default Value

NO

Meaning

Specifies whether instances of non-compliance with the ANSI/ISO SQL standards will be flagged at compile time. If FIPS=YES, Oracle extensions to standard Module Language and standard SQL, as well as use of standard constructs in ways that violate the SQL standard format or syntax rules, are flagged by the FIPS flagger.

INAME

Values

<filename>

Default Value

None.

Meaning

Specifies the name of the input file. If the specified filename does not contain an extension, the compiler supplies the default extension for the host language. Only one input file is allowed. If more than one INAME option is specified, the last one prevails, and the earlier ones are ignored.

If STORE_PACKAGE=NO and the PNAME option is specified, the INAME option cannot be specified. In this case, there is no input file, since the input comes from the stored package. If INAME is specified under these circumstances, SQL*Module generates a warning message and continues, if possible.

LNAME

Values

<filename>

Default Value

The base name of the listing file first defaults to the base name of INAME or, if INAME is not specified, it defaults to the name of the package specified in the PNAME option. The default file extension is .lis.

Meaning

Specifies the name of the listing file. This option is valid only if the LTYPE option is not NONE.

LTYPE

Values

{NONE | SHORT | LONG}

Default Value

LONG

Meaning

Specifies the listing type. The OUTPUT option list must contain the VALUE LIST, otherwise this option has no effect.

If the LTYPE value is NONE, no list file is generated, regardless of the setting of the OUTPUT option. If the LTYPE value is SHORT, the list file contains no code, only error messages. LTYPE=LONG generates a complete listing file, with errors and code.

Note:

When INAME is specified, the listing file shows Module Language code, not the generated host language code. When compiling an interface procedure, the listing output contains only error messages, regardless of the LTYPE specification. See the OUTPUT option for more information on how to generate PL/SQL output source.

MAPPING

Values

( ) | OVERLOAD

where ( ) indicates an empty string.

Default Value

Empty string.

Meaning

The MAPPING option is used when generating prototypes for the default WITH INTERFACE PROCEDURE clause. See "The Default WITH INTERFACE Clause" for more information.

When MAPPING=OVERLOAD, SQL*Module resolves overloaded stored procedure and function names when generating stubs. It does this by prefixing MODn_ to the second and subsequent procedure names, where n starts with 2, and increments by 1 until all stubs for all overloaded procedures of that name have been resolved.

MAXLITERAL

Values

Numeric literal, range 10 to 1024 bytes

Default Value

255 bytes

Meaning

Specifies the maximum length of string literals generated by the SQL*Module compiler, so that host language compiler limits are not exceeded. For example, if your system's compiler cannot handle string literals longer than 512 bytes, specify MAXLITERAL=512 in the system configuration file.

ONAME

Values

<filename>

Default Value

The base name of the output file first defaults to the base name of INAME. If INAME is not specified, then ONAME defaults to the name of the package specified in the PNAME option, if present. The default file extension is system dependent, but is generally .a. The default output directory is the current directory.

Meaning

Specifies the name of the code output file. Whether an output file is actually generated depends on the values of the OUTPUT option. The OUTPUT list must contain the value CODE.

OUTPUT

Values

Any one or more of CODE, LIST, NONE, PACKAGE, SPECIFICATION

Default Values

CODE, SPECIFICATION

Meaning

Specifies what output files SQL*Module generates. The values are

CODE An interface procedures file is generated.

LIST A listing file is generated. See the LNAME and LTYPE options for more information.

NONE No files are generated. This option is used to do syntactic and semantic checking of the input file, as error output is always generated.

PACKAGE PL/SQL source files are generated. These files contain the PL/SQL package generated by SQL*Module. The default base filename is the same as the name specified in either the INAME or the PNAME option. If both are specified, the default is taken from INAME.

The default extensions are .pks (package specification) and .pkb (package body).

SPECIFICATION A specification file containing procedure declarations is generated. The filename extension is language specific. See "Output Files" for more information.

Note: If the value NONE is included in the list, then no output of any kind is generated, regardless of any other values that might be in the list.

PNAME

Values

Name of a stored package or a standalone stored procedure in the Oracle database, or the name to be given to a stored package to be created in the database when STORE_PACKAGE=YES.

Default Value

For Output (when RPC_GENERATE=YES) There is no default value. You must specify the name of a package in the database. However, you can specify a complete path name, including an extension, to serve as a default for ONAME. In this case, the directory hierarchy and the filename extension are ignored, and the basename is taken as the package name for database lookup.

For Input (when STORE_PACKAGE=YES) The default value is the module name in the MODULE clause of the input file. If there is no module name, the default is taken from the INAME value.

Meaning

Specifies the name of the package stored in the database (if STORE_PACKAGE=NO), or the name of a package to be created by SQL*Module (if STORE_PACKAGE=YES). The name must be a valid database object name.

RPC_GENERATE

Values

{YES |NO}

Default Value

NO

Meaning

Specifies whether SQL*Module should produce an interface procedures file so that a host program can call stored procedures. You can use this option with STORE_PACKAGE=NO and PNAME=<package_name> to generate interface procedures for standalone or packaged procedures that are already stored in the database. You can also use this option with INAME=<filename> and STORE_PACKAGE=YES to store procedures in a module file in the database, and generate an interface procedures file to access them.

SELECT_ERROR

Values

{YES | NO}

Default Value

YES

Meaning

Specifies whether an error is generated at runtime when a SELECT or FETCH statement returns more than one row.

SNAME

Values

<filename>

Default Value

The base name of the input file, if specified, plus the appropriate extension for a specification file for the host language. For Ada, a system-specific filename addition and extension is used, such as ora_dcl for VAX/OPEN VMS Ada, or *s.a for Verdix Ada.

Meaning

Specifies the name of the specification or header file. If INAME is not specified, SNAME must be specified to get a specification file. The file is not generated if the OUTPUT option does not include SPECIFICATION in its list of values.

STORE_PACKAGE

Values

{YES | NO}

Default Value

NO

Meaning

If STORE_PACKAGE=YES, SQL*Module compiles the module file specified in the mandatory INAME option, and stores the packaged procedures in the database schema specified by the USERID option. The name of the package is specified by the PNAME option.

If you do not specify a PNAME option, the default package name becomes the name of the module, as specified in the MODULE clause of the module file. If neither the PNAME option nor the MODULE clause is specified, the package name is the base name (omitting any path specification or file extension) of the input file specified in the INAME option.

Note:

When STORE_PACKAGE=YES, SQL*Module performs a CREATE OR REPLACE PACKAGE statement. This statement overwrites, without any warning any package of that name in the schema.

SQLCHECK

Values

{NONE | SYNTAX | SEMANTICS}

Default Value

SEMANTICS

Meaning

Determines the way SQL*Module processes the input file when INAME is specified. This option has no meaning if there is no input file.

NONE SQL*Module processes the command line, issues any error messages produced by configuration file or command-line options, then exits without compiling any input and does not produce any output files.

SYNTAX SQL*Module compiles the input file specified in the INAME option, using its own SQL parser. Errors detected are flagged, but no source code, specification, or listing output files are produced.

SEMANTICS The input file is compiled on the server side, all syntactic and semantic errors are flagged, and all appropriate output files are generated.

USERID

Values

<string>

Default Value

None

Meaning

Specifies an Oracle username and, optionally, a password and a database to connect to. The syntax of this option is

USERID=USER_NAME[/PASSWORD][@DATABASE_NAME] 

SQL*Module must be able to connect to a server when compiling an input file, to parse the SQL and PL/SQL statements, do syntactic and semantic checking, and to store packages if required. If the password is omitted, SQL*Module prompts for one. If a database is not specified, the default (local) database for the user is used.

If you do not specify the USERID option, the default becomes the user name (and possibly the password) specified in the AUTHORIZATION clause of the Module Language input file, or the USERID value specified in a configuration file.

Note: SQL*Module always prompts for a password if one has not been supplied in a configuration file, in an AUTHORIZATION clause in the module file, or on the command line. So, there is no need to hard code passwords into text files.

Compiling and Linking

To produce an executable program, you must compile source code output files that SQL*Module generates, then link these together with the compiled object files of any sources that call modules or interface procedures, with SQLLIB, and with other Oracle libraries. The details are necessarily both system and language dependent. The tables in the next three sections show a few examples.

An Example (Module Language)

There is a Module Language file to be compiled. No stored database packages are involved. The steps to take are shown in Table 5-3.

Note: This example is specific to VAX/OPEN VMS. For other Ada implementations, using a linker for all Ada files might be required.

Table 5-3 Development Scenario

Step File Name How Developed Action to Take

1

tst_app_drv.ada

by Ada developer

compile into Ada library using host Ada compiler

2

tst_app_mod.mad

by SQL developer

compile using SQL*Module

3

tst_app_mod.ora_dd

generated by SQL*module in Step 2

compile into Ada library using host Ada compiler

4

tst_app_mod.ada

generated by module from Step 2

compile into Ada library using host Ada compiler; make sure to with this package in tst_app_drv.ada

5

tst_app_drv.o

extracted from Adalib

link (with SQLLIB)

6

tst_app_drv

linked from step 5

run and test