| Programmer's Guide to the Pro*Ada Precompiler |  Library |
 Product |
 Contents |
 Index |
| Programmer's Guide to the Pro*Ada Precompiler | Library |
Product |
Contents |
Index |
proada
followed by one or more of the command arguments.
During precompilation, Pro*Ada generates calls to Ada routines in place of the SQL statements embedded in your program. The precompiler can also issue error messages, which are listed in the Oracle7 Server Messages manual.
INAME=filename
where filename identifies the precompiler input file. The assumed extension is .PAD.
option_name=value
Leave no space around the equal sign because spaces delimit individual options.
Table 11 - 1 is a quick reference to the Ada precompiler options. It summarizes "Using the Precompiler Options" ![[*]](jump.gif)
.
| Syntax | Default | Specifics |
| DBMS = V6 | V7 | NATIVE | NATIVE | sets the database compatibility at precompile time |
| DEFINE = symbol | symbol used in conditional precompilation | |
| ERRORS = YES/NO | YES | whether errors are sent to the terminal |
| HOLD_CURSOR = YES|NO* | NO | how cursor cache handles SQL statements |
| INAME = path & filename | name of input file | |
| IRECLEN = integer | 80 | record length of input file |
| LNAME = path & filename | name of listing file | |
| LRECLEN = integer | 132 | record length |
| LTYPE = LONG | SHORT | NONE | LONG | type of listing |
| MAXLITERAL = integer* | maximum length of string | |
| MAXOPENCURSORS=integer* | 10 | maximum number of cursors cached |
| MODE = ORACLE | ANSI | ANSI13 | ANSI | ISO13 | ISO14 | ORACLE | compliance with ANSI SQL standard |
| ONAME = path & filename | name of output file | |
| ORECLEN = integer | 80 | record length of output file |
| PAGELEN = integer | 66 | lines per page in listing |
| RELEASE_CURSOR = YES | NO* | NO | how cursor cache handles SQL statements |
| SELECT_ERROR = YES|NO* | YES | how SELECT errors are handled |
| SQLCHECK = SYNTAX | LIMITED | NONE | SYNTAX | extent of syntactic checking |
| THREADS = YES | NO | NO | whether multi-thread capability is active |
| USERID = username/password | valid Oracle username and password | |
| XREF = YES | NO | YES | cross-reference section in listing |
| *can be entered inline | ||
The display gives the name, syntax, default value, and purpose of each option. Options marked with an asterisk (*) can be specified inline as well as on the command line.
... [option_name=value] [option_name=value] ...
Separate each option with one or more spaces. For example, you might enter the following:
... ERRORS=no LTYPE=short
EXEC ORACLE OPTION (option_name=value);
For example, you might code the following:
EXEC ORACLE OPTION (RELEASE_CURSOR=YES);
An option entered inline overrides the same option entered on the command line.
Specifying options inline is also helpful if your operating system limits the number of characters you can enter on the command line. You can even store inline options in a separate file, then INCLUDE them at the appropriate place in your program.
with text_io, integer_text_io, float_io, varchar_text_io;
procedure HOLDCURS is
EMP_NAME : ORACLE.VARCHAR(20);
EMP_NUMBER : integer;
SALARY : FLOAT;
DEPT_NUMBER : integer;
USERNAME : string(1..11) := "SCOTT/TIGER";
use text_io, integer_text_io, float_io;
begin
EXEC SQL WHENEVER NOT FOUND GOTO NO_MORE;
EXEC SQL CONNECT :USERNAME;
EXEC SQL OPTION (HOLD_CURSOR=NO);
EXEC SQL DECLARE emp_cursor CURSOR FOR
SELECT empno, deptno FROM emp;
EXEC SQL OPEN emp_cursor;
PUT_LINE("Employee Number Dept");
PUT_LINE("--------------- ----");
loop
EXEC SQL FETCH emp_cursor INTO :EMP_NUMBER, :DEPT_NUMBER;
PUT(EMP_NUMBER, 12);
PUT(DEPT_NUMBER, 4);
NEW_LINE;
end loop;
<<NO_MORE>>
EXEC SQL WHENEVER NOT FOUND CONTINUE;
loop
PUT("Employee number: ");
GET(EMP_NUMBER);
exit when EMP_NUMBER = 0;
EXEC ORACLE OPTION (HOLD_CURSOR=YES);
EXEC SQL SELECT ename, sal
INTO :EMP_NAME, :SALARY
FROM emp
WHERE empno = :EMP_NUMBER;
PUT("Salary for ");
VARCHAR_TEXT_IO.PUT(EMP_NAME);
PUT(" is ");
PUT(SALARY, FORE => 6, AFT => 2, EXP => 0);
NEW_LINE;
end loop;
end HOLDCURS;
With configuration files, you need not enter long strings of options on the command line. Also, if your system limits the length of a command line, configuration files let you specify more options than the command line can hold.
IRECLEN=132
MODE=ANSI
MAXOPENCURSORS=20
The name and location of the configuration file are system specific. For example, under VAX/VMS, the name and location are ORA_PCC:PCCADA.CFG, where ORA_PCC is a VMS logical denoting the path. Under UNIX, the file name and location are $ORACLE_HOME/proada/pccada.cfg, where $ORACLE_HOME is a UNIX environmental variable denoting the path.
Additional Information: Consult your platform-specific Oracle documentation for more information about the name and location of configuration files.
There can only be one system configuration file for the Pro*Ada precompiler, but you can create any number of user configuration files. You use the new command-line option CONFIG to specify the name and location of a particular user configuration file, as follows:
... CONFIG=<filename> ...
You cannot nest configuration files. Therefore, you cannot specify the option CONFIG in a configuration file. Also, you cannot specify CONFIG inline.
For example, the option MAXOPENCURSORS specifies the maximum number of cursors cached. Its built-in default value is 10. However, if the system configuration file specifies MAXOPENCURSORS=20, the default becomes 20. Then, if a user configuration file specifies MAXOPENCURSORS=30, the default becomes 30. Finally, if you specify MAXOPENCURSORS=40 on the command line, the default becomes 40. However, that an option entered inline overrides the same option entered on the command line.
Purpose
Specifies whether Oracle follows the semantic and syntactic rules of Oracle Version 6, Oracle7, or the native version of Oracle (that is, the version to which the application is connected).
Syntax
Default
NATIVE
Usage Notes
Can be entered only on the command line, or in a configuration file.
The DBMS option lets you control the version-specific behavior of Oracle. When DBMS=NATIVE (the default), Oracle follows the semantic and syntactic rules of the database version to which the application is connected.
When DBMS=V6 or DBMS=V7, Oracle follows the rules of Oracle Version 6, or Oracle7, respectively.
Purpose
Specifies a user-defined symbol that is used to include or exclude portions of source code during a conditional precompilation. For more information, see "Conditional Precompilations" .
Syntax
DEFINE=symbol
Default
None
Usage Notes
Can be entered inline or on the command line.
If you enter DEFINE inline, the EXEC ORACLE statement takes the following form:
EXEC ORACLE DEFINE symbol;
Purpose
Specifies whether precompiler error messages are sent to the terminal and listing file, or only to the listing file.
Syntax
ERRORS=YES|NO
Default
YES
Usage Notes
Can be entered inline or on the command line.
If you specify ERRORS=YES, error messages are sent to the terminal and listing file.
If you specify ERRORS=NO, error messages are sent only to the listing file.
Purpose
Specifies how the cursors for SQL statements and PL/SQL blocks are handled in the cursor cache.
Syntax
HOLD_CURSOR=YES|NO
Default
NO
Usage Notes
Can be entered inline or on the command line.
You can use HOLD_CURSOR to improve the performance of your program. For more information, see Appendix D, "Performance Tuning."
When a SQL data manipulation statement is executed, its associated cursor is linked to an entry in the cursor cache. The cursor cache entry is in turn linked to an Oracle context area, which stores information needed to process the statement. HOLD_CURSOR controls what happens to the link between the cursor and cursor cache.
When HOLD_CURSOR=NO, after Oracle executes the SQL statement and the cursor is closed, the precompiler marks the link as reusable. The link is reused as soon as the cursor cache entry to which it points is needed for another SQL statement. This frees memory allocated to the context area and releases parse locks.
When HOLD_CURSOR=YES and RELEASE_CURSOR=NO, the link is maintained; the precompiler does not reuse it. This is useful for SQL statements that are executed often because it speeds up subsequent executions. There is no need to reparse the statement or allocate memory for an Oracle context area.
For inline use with implicit cursors, set HOLD_CURSOR before executing the SQL statement. For inline use with explicit cursors, set HOLD_CURSOR before OPENing the cursor.
Note: RELEASE_CURSOR=YES overrides HOLD_CURSOR=YES and that HOLD_CURSOR=NO overrides RELEASE_CURSOR=NO. For more information on how these two options interact, see Appendix D, "Performance Tuning."
Purpose
Specifies the name of the input file.
Syntax
INAME=path and filename
Default
None
Usage Notes
Can be entered only on the command line.
If you use a non-standard input file extension when specifying INAME, you must also specify HOST.
Purpose
Specifies the record length of the input file.
Syntax
IRECLEN=integer
Default
80
Usage Notes
Can be entered only on the command line.
The value you specify for IRECLEN should not exceed the value of ORECLEN. The maximum value allowed is system-dependent.
Purpose
Specifies a non-default name for the listing file.
Syntax
LNAME=path and filename
Default
input.LIS, where input is the base name of the input file.
Usage Notes
Can be entered only on the command line.
By default, the listing file is written to the current directory.
Purpose
Specifies the record length of the listing file.
Syntax
LRECLEN=integer
Default
132
Usage Notes
Can be entered only on the command line.
The value of LRECLEN can range from 80 through 255. If you specify a value below the range, 80 is used instead. If you specify a value above the range, 255 is used instead. LRECLEN should exceed IRECLEN by at least 8 to allow for the insertion of line numbers.
Purpose
Syntax
LTYPE=LONG|SHORT|NONE
Default
LONG
Usage Notes
Can be entered only on the command line.
If you specify LTYPE=LONG, input lines appear in the listing file. If you specify LTYPE=SHORT, input lines do not appear in the listing file. If you specify LTYPE=NONE, no listing file is created.
Purpose
Specifies the maximum length of string literals generated by the precompiler, so that compiler limits are not exceeded. For example, if your compiler cannot handle string literals longer than 256 characters, you can specify MAXLITERAL=256 on the command line.
Syntax
MAXLITERAL=integer
Default
Compiler-dependent
Usage Notes
Can be entered inline or on the command line.
The default and maximum values of MAXLITERAL are compiler-dependent. Strings that exceed the length specified by MAXLITERAL are divided during precompilation, then concatenated at runtime. You can enter MAXLITERAL inline. For example:
EXEC ORACLE OPTION (MAXLITERAL=512);
Your program can set MAXLITERAL just once, and the EXEC ORACLE statement must precede the first EXEC SQL statement. Otherwise, the precompiler issues a warning message, ignores the extra or misplaced EXEC ORACLE statement, and continues processing.
With previous Oracle Precompilers, your program could set MAXLITERAL more than once, although there was no reason to do so. It is unlikely that your old programs set MAXLITERAL more than once, but you might need to relocate the EXEC ORACLE statement.
Purpose
Specifies the number of concurrently open cursors that the precompiler tries to keep cached.
Syntax
MAXOPENCURSORS=integer
Default
10
Usage Notes
Can be entered inline or on the command line.
You can use MAXOPENCURSORS to improve the performance of your program. For more information, see Appendix D, "Performance Tuning."
The maximum number of open cursors per user process (50 is the default and the range is 5 to 255) is set by the OPEN_CURSORS parameter. You can override this parameter with MAXOPENCURSORS to specify a lower (but not higher) value. To avoid a "maximum open cursors exceeded" Oracle error, MAXOPENCURSORS must be lower than OPEN_CURSORS by at least 6.
MAXOPENCURSORS specifies the initial size of the cursor cache. If a new cursor is needed, and there are no free cache entries, Oracle tries to reuse an entry. Its success depends on the values of HOLD_CURSOR and RELEASE_CURSOR, and, for explicit cursors, on the status of the cursor itself. Oracle allocates an additional cache entry if it cannot find one to reuse. If necessary, Oracle keeps allocating additional cache entries until it runs out of memory or reaches the limit set by OPEN_CURSORS.
As your program's need for concurrently open cursors grows, you might want to update the specification for MAXOPENCURSORS to match the need. A value of 45 to 50 is not uncommon, but remember that each cursor requires another context area in the user process memory space. The default value of 10 is adequate for most programs.
Purpose
Specifies whether your program observes Oracle practices or complies closely with the current ANSI embedded SQL standard.
Syntax
MODE=ANSI|ISO|ANSI14|ISO14|ANSI13|ISO13|ORACLE
Default
ORACLE
Usage Notes
Can be entered only on the command line.
The MODE values ANSI, ISO, ANSI14, and ISO14 are equivalent, as are the values ANSI13 and ISO13. Note, however, that the values ANSI and ISO will specify full ANSI/ISO compliance under Version 1.5 of the Pro*Ada Precompiler. So, for upward compatibility, use ANSI14 or ISO14 instead.
If you specify MODE=ORACLE (the default), your embedded SQL program observes Oracle practices. If you specify MODE=ANSI14, your embedded SQL program becomes more compliant with the current ANSI embedded SQL standard, and the following changes go into effect:
| Change | V1.3 | V1.4 |
| COMMIT or ROLLBACK closes all explicit cursors | no | yes |
| Illegal to OPEN an already open cursor | yes | yes |
| Must append indicator variable when fetching nulls | no | yes |
| Must declare SQLCODE | no | yes |
| No error message if output value is truncated | yes | yes |
| Oracle error code is +100 for "no data found" | yes | yes |
| Colon prefix required in the INTO clause | no | yes |
Purpose
Specifies the name of the output file.
Syntax
ONAME=path and filename
Default
System-dependent
Usage Notes
Can be entered only on the command line.
By default, the output file is written to the current directory.
Purpose
Specifies the record length of the output file.
Syntax
ORECLEN=integer
Default
80
Usage Notes
Can be entered only on the command line.
The value you specify for ORECLEN should equal or exceed the value of IRECLEN. The maximum value allowed is system-dependent.
Purpose
Specifies the number of lines per physical page of the listing file.
Syntax
PAGELEN=integer
Default
66
Usage Notes
Can be entered only on the command line.
The maximum value allowed is system-dependent.
Purpose
Specifies how the cursors for SQL statements and PL/SQL blocks are handled in the cursor cache.
Syntax
RELEASE_CURSOR=YES|NO
Default
NO
Usage Notes
Can be entered inline or on the command line.
You can use RELEASE_CURSOR to improve the performance of your program. See Appendix D, "Performance Tuning."
When a SQL data manipulation statement is executed, its associated cursor is linked to an entry in the cursor cache. The cursor cache entry is in turn linked to an Oracle context area, which stores information needed to process the statement. RELEASE_CURSOR controls what happens to the link between the cursor cache and context area.
If you specify RELEASE_CURSOR=YES, after Oracle executes the SQL statement and the cursor is closed, the precompiler immediately removes the link. This frees memory allocated to the context area and releases parse locks. To make sure that associated resources are freed when you CLOSE a cursor, you must specify RELEASE_CURSOR=YES.
If you specify RELEASE_CURSOR=NO and HOLD_CURSOR=YES, the link is maintained. The precompiler does not reuse the link unless the number of open cursors exceeds the value of MAXOPENCURSORS. This is useful for SQL statements that are executed often because it speeds up subsequent executions. There is no need to reparse the statement or allocate memory for an Oracle context area.
For inline use with implicit cursors, set RELEASE_CURSOR before executing the SQL statement. For inline use with explicit cursors, set RELEASE_CURSOR before OPENing the cursor.
Note that RELEASE_CURSOR=YES overrides HOLD_CURSOR=YES and that HOLD_CURSOR=NO overrides RELEASE_CURSOR=NO. For a table showing how these two options interact, see Appendix D, "Performance Tuning."
Purpose
Specifies whether your program generates an error when a single-row SELECT statement returns more than one row, or more rows than a host array can accommodate.
Syntax
SELECT_ERROR=YES|NO
Default
YES
Usage Notes
Can be entered inline or on the command line.
If you specify SELECT_ERROR=YES, an error is generated when a single-row SELECT returns too many rows, or when an array SELECT returns more rows than the host array can accommodate. The result of the SELECT is indeterminate.
If you specify SELECT_ERROR=NO, no error is generated when a single-row SELECT returns too many rows, or when an array SELECT returns more rows than the host array can accommodate.
Whether you specify YES or NO, a random row is selected from the table. The only way to ensure a specific ordering of rows is to use the ORDER BY clause in your SELECT statement. If you specify SELECT_ERROR=NO and use ORDER BY, Oracle returns the first row, or the first n rows when you are SELECTing into an array. If you specify SELECT_ERROR=YES, whether or not you use ORDER BY, an error is generated when too many rows are returned.
Purpose
Specifies the type and extent of syntactic checking.
Syntax
SQLCHECK=SYNTAX|LIMITED|NONE
Default
SYNTAX
Usage Notes
Can be entered inline or the command line.
The Pro*Ada Precompiler can help you debug a program by checking the syntax of embedded SQL statements and PL/SQL blocks. You control the level of checking by entering the SQLCHECK option inline and/or on the command line. However, the level of checking you specify inline cannot be higher than the level you specify (or accept by default) on the command line.
Note: SQLCHECK={SEMANTICS | FULL} is not implemented with the Pro*Ada Precompiler. The architecture of Pro*Ada prohibits semantic checking by design. This was done to better integrate bind variable typing with Ada's library management mechanisms.
When you specify SQLCHECK={SYNTAX | LIMITED}, the precompiler checks the syntax of
Note: The values LIMITED and SYNTAX are equivalent, that is, they specify the same action. However, under Version 1.3 of the Oracle Precompilers, the value LIMITED specified a different action. With Version 1.3, when you specified SQLCHECK=LIMITED, the precompiler checked the syntax and semantics of data manipulation statements and PL/SQL blocks. Also, to do the semantic check, the precompiler connected to Oracle, so you had to specify the USERID option.
When you specify SQLCHECK=NONE, the precompiler does no semantic checking and minimal syntactic checking, DECLARE TABLE statements are ignored, and PL/SQL blocks are not allowed.
Specify SQLCHECK=NONE only if
Purpose
Specifies whether multi-threading is supported.
Syntax
THREADS=YES | NO
Default
YES
Usage Notes
Can be entered only on the command line.
Specify THREADS=YES when you need multi-thread capability in the Pro*Ada runtime library (SLQLIB). Multi-thread capability is required to use Ada tasking in your Pro*Ada application.
Purpose
Specifying UNSAFE_NULL=YES prevents generation of the ORA-01405 message when fetching NULLs without using indicator variables.
Syntax
UNSAFE_NULL={YES|NO}
Default
NO
Usage Notes
Cannot be entered inline.
The UNSAFE_NULL=YES is allowed only when MODE=ORACLE and DBMS=V7.
The UNSAFE_NULL option has no effect on host variables in an embedded PL/SQL block. In that case, you must use indicator variables to avoid ORA-01405 errors.
Purpose
Specifies an Oracle username and password.
Syntax
USERID=username/password
Default
None
Usage Notes
Can be entered only on the command line.
Do not specify this option when using the automatic logon feature, which accepts your Oracle username prefixed with OPS$.
Purpose
Specifies whether a cross-reference section is included in the listing file.
Syntax
XREF=YES|NO
Default
YES
Usage Notes
Can be entered inline or on the command line.
If you specify XREF=YES, cross references are included for host variables, cursor names, and statement names. The cross references show where each object is defined and referenced in your program.
If you specify XREF=NO, the cross-reference section is not included.
The REBIND option specified how often host variables in SQL statements were bound. You could change the specification for REBIND for each SQL statement or set of SQL statements in your program.
The REENTRANT option specified whether reentrant code was generated. A reentrant program or subroutine can be reentered before it has finished executing. Thus, it can be used simultaneously by two or more processes. On some systems, you had to specify REENTRANT=YES.
However, with the Version 1.4 Oracle Precompilers, context areas are automatically resized, host variables are rebound only when necessary, and reentrant code is generated automatically for systems that require it. These advances make the AREASIZE, REBIND, and REENTRANT options obsolete. You no longer have to worry about using these options correctly. In fact, if you specify AREASIZE, REBIND, or REENTRANT, you get the following warning message:
PCC-I-0093: Invalid or obsolete option, ignored
Conditional sections of code are marked by statements that define the environment and actions to take. You can code host-language statements as well as EXEC SQL statements in these sections. The following statements let you exercise conditional control over precompilation:
EXEC ORACLE DEFINE symbol; -- define a symbol
EXEC ORACLE IFDEF symbol; -- if symbol is defined
EXEC ORACLE IFNDEF symbol; -- if symbol is not defined
EXEC ORACLE ELSE; -- otherwise
EXEC ORACLE ENDIF; -- end this control block
EXEC ORACLE IFDEF site2;
EXEC SQL SELECT DNAME
INTO :dept_name
FROM DEPT
WHERE DEPTNO = :dept_number;
EXEC ORACLE ENDIF;
Blocks of conditions can be nested as shown in the following example:
EXEC ORACLE IFDEF outer;
EXEC ORACLE IFDEF inner;
...
EXEC ORACLE ENDIF;
EXEC ORACLE ENDIF;
You can "comment out" host-language or embedded SQL code by placing it between IFDEF and ENDIF, and not defining the symbol.
EXEC ORACLE DEFINE symbol;
in your host program, or define the symbol on the command line using the syntax
... INAME=filename ... DEFINE=symbol
where symbol is not case-sensitive.
Some port-specific symbols are predefined for you when the Pro*Ada Precompiler is installed on your system. Predefined operating system symbols can include CMS, MVS, MSDOS, UNIX, and VMS. Predefined hardware symbols include IBM and DEC. For example, on a VMS system the symbols DEC and VMS are predefined.
Additional Information: Consult your platform-specific Oracle documentation for more information.
Additional Information: Consult your platform-specific Oracle documentation for instructions on how to build an Ada program library into which you can insert the units that comprise your program.
After setting the appropriate program library, run the precompiler on each file in your program that contains embedded SQL statements or precompiler commands. The precompiler generates a specification file and an Ada source code file for each precompiled file. Use your Ada compiler to compile the specification and source files and build them into your library.
Then, export the object module(s) from the library, and link them using the standard link command on your system. Again, you must check your Oracle installation or user's guide for specific instructions.
Here is a brief example using VAX/VMS Pro*Ada that shows the steps involved for that system. ACS is the Ada library management facility on VMS. In this example, the file DYN4.PAD contains an example Pro*Ada program that contains the main procedure DYN4_SAMPLE.
PROMPT > ACS SET LIBRARY [.ADALIB] ! Set program library
PROMPT > PROADA INAME=DYN4 ! Precompile the file
PROMPT > ADA DYN4.ORA_DCL ! Compile the spec file
PROMPT > ADA DYN4 ! Compile the prog. body
PROMPT > LNPROADA DYN4_SAMPLE ! Export the unit, and
! link it. (LNPROADA
! contains the ACS command
! "ACS export/main ...".)
|
Prev Next |
 Copyright © 1996 Oracle Corporation. All Rights Reserved. |
Library |
Product |
Contents |
Index |