This chapter describes
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 to the compiler can come from two sources:
module files written according to the SQL standard Module Language specifications, as described in Chapter 2, "Module Language" of this Guide
stored packages and procedures in an Oracle database (see Chapter 3, "Accessing Stored Procedures")
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.
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.There are three sources of input for SQL*Module, and four ways to determine the input:
When compiling a module written in Module Language, the source is the Module Language code file.
When generating RPC stubs from stored procedures, there is no input file. The source of the input is the stored package in the database.
When creating a stored package in the database from a Module Language module file, the source is the Module Language file.
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.
| 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".
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.
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.
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
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.
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.
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.
Most of the options have default values. Three things determine the default value:
values built into the SQL*Module compiler
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".
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.
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.
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
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.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.
By default, modada generates a specification or header file. The specification file contains declarations for the procedures in the generated output file.
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.
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.
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.
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 |
|
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> |
{YES | NO}
NO
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.
{EARLY | LATE}
EARLY
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.
<filename>
None.
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).
{YES | NO}
YES
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.
{YES | NO}
NO
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.
<filename>
None.
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.
<filename>
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.
Specifies the name of the listing file. This option is valid only if the LTYPE option is not NONE.
{NONE | SHORT | LONG}
LONG
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.( ) | OVERLOAD
where ( ) indicates an empty string.
Empty string.
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.
Numeric literal, range 10 to 1024 bytes
255 bytes
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.
<filename>
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.
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.
Any one or more of CODE, LIST, NONE, PACKAGE, SPECIFICATION
CODE, SPECIFICATION
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.
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.
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.
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.
{YES |NO}
NO
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.
{YES | NO}
YES
Specifies whether an error is generated at runtime when a SELECT or FETCH statement returns more than one row.
<filename>
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.
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.
{YES | NO}
NO
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.
{NONE | SYNTAX | SEMANTICS}
Default Value
SEMANTICS
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.
<string>
Default Value
None
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.
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.
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 |