Release 8.0
A54659-01
 Library |
 Product |
 Contents |
 Index |
| Programmer's Guide to the Oracle Pro*COBOL Precompiler Release 8.0 A54659-01 |
|
This chapter provides the basic information you need to write a Pro*COBOL program, including:
This section deals with embedded SQL syntax, coding conventions, and Pro*COBOL-specific features and restrictions. Topics are arranged alphabetically for quick reference.
You can use the standard COBOL abbreviations, such as PIC for PICTURE IS and COMP for USAGE IS COMPUTATIONAL.
Pro*COBOL precompiler options and values as well as all EXEC SQL statements, inline commands, and COBOL statements are case-insensitive. The precompiler accepts both upper- and lower-case tokens.
--Pro*COBOL supports the standard implementation of COBOL for your operating system (usually COBOL-85 or COBOL-74). Some platforms may support both COBOL implementations. For more information, see your Oracle8 system-specific documentation.
You must code EXEC SQL and EXEC ORACLE statements in columns 12 through 72 (columns 73 through 80 are ignored).
Note: The precompiler option FORMAT, specifies the format of COBOL input lines. If you specify FORMAT=ANSI (default), columns 1 through 6 can contain an optional sequence number, and column 7 indicates comments or continuation lines. Division headers, section headers, paragraph names, FD and 01 statements begin in columns 8 through 11 (area A). Other statements begin in columns 12 through 72 (area B).
If you specify FORMAT=TERMINAL, columns 1 through 6 are omitted, making column 7 the left-most column. Area B and area A begin in column 8.
Note: In this manual, program examples use the FORMAT=TERMINAL setting. The online sample programs use FORMAT=ANSI.
In SQL, you must use commas to separate list items, as the following example shows:
EXEC SQL SELECT ENAME, JOB, SAL
INTO :EMP-NAME, :JOB-TITLE, :SALARY
FROM EMP
WHERE EMPNO = :EMP-NUMBER
END-EXEC.
In COBOL, you can use commas or blanks to separate list items. For example, the following two statements are equivalent:
ADD AMT1, AMT2, AMT3 TO TOTAL-AMT. ADD AMT1 AMT2 AMT3 TO TOTAL-AMT.
You can place COBOL Comment lines within SQL statements. COBOL Comment lines start with an asterisk (*) in column 7. You can also place ANSI SQL-style Comments (-- ...) within SQL statements at the end of a line (but not after the last line of the SQL statement), and you can place C-style Comments (/* ... */) in SQL statements.
The following example shows all three styles of Comments:
EXEC SQL SELECT ENAME, SAL
* assign column values to output host variables
INTO :EMP-NAME, :SALARY -- output host variables
/* column values assigned to output host variables */
FROM EMP
WHERE DEPTNO = :DEPT-NUMBER
END-EXEC. -- illegal Comment
However, you cannot nest Comments or place them on the last line of a SQL statement after the terminator END-EXEC.
You can continue SQL statements from one line to the next, according to the rules of COBOL, as this example shows:
EXEC SQL SELECT ENAME, SAL INTO :EMP-NAME, :SALARY FROM EMP
WHERE DEPTNO = :DEPT-NUMBER
END-EXEC.
No continuation indicator is needed.
To continue a string literal from one line to the next, code the literal through column 72. On the next line, code a hyphen (-) in column 7, a quote in column 12 or beyond, and then the rest of the literal. An example follows:
WORKING STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
...
01 UPDATE-STATEMENT PIC X(80) VALUE "UPDATE EMP SET BON
- "US = 500 WHERE DEPTNO = 20".
EXEC SQL END DECLARE SECTION END-EXEC.
The LITDELIM option specifies the delimiters for COBOL string constants and literals. If you specify LITDELIM=APOST, the Pro*COBOL uses apostrophes when generating COBOL code. If you specify LITDELIM=QUOTE (default), quotation marks are used, as in
CALL "SQLROL" USING SQL-TMP0.
In SQL statements, you must use quotation marks to delimit identifiers containing special or lowercase characters, as in
EXEC SQL CREATE TABLE "Emp2" END-EXEC.
However, you must use apostrophes to delimit string constants, as in
EXEC SQL SELECT ENAME FROM EMP WHERE JOB = 'CLERK' END-EXEC.
Regardless of which delimiter is used in the Pro*COBOL source file, Pro*COBOL generates the delimiter specified by the LITDELIM value.
To use a SQL statement in your Pro*COBOL program, precede the SQL statement with the EXEC SQL clause, and end the statement with the END-EXEC keyword. Embedded SQL syntax is described in the Oracle8 Server SQL Reference.
Figurative constants, such as HIGH-VALUE, ZERO, and SPACE, cannot be used in SQL statements. For example, the following is invalid:
EXEC SQL DELETE FROM EMP WHERE COMM = ZERO END-EXEC.
Instead, use the following:
EXEC SQL DELETE FROM EMP WHERE COMM = 0 END-EXEC.
Pro*COBOL cannot process arbitrarily long source files. Some of the variables used internally limit the size of the generated file. There is no absolute limit to the number of lines allowed, but the following aspects of the source file are contributing factors to the file-size constraint:
To prevent problems related to this limitation, use multiple program units to sufficiently reduce the size of the source files.
Host-variable names must consist only of letters, digits, and hyphens, and must begin with a letter. They can be any length, but only the first 30 characters are significant. The maximum number of significant characters recognized by COBOL compilers is 30.
You can use hyphenated host-variable names in static SQL statements but not in dynamic SQL. For example, the following usage is invalid:
MOVE "DELETE FROM EMP WHERE EMPNO = :EMP-NUMBER" TO SQLSTMT.
EXEC SQL PREPARE STMT1 FROM SQLSTMT END-EXEC.
When declaring host variables, you can use level numbers 01 through 49, and 77. Pro*COBOL does not allow variables containing the VARYING clause, or SQL-CURSOR variables to be declared level 49 or 77.
With the MAXLITERAL option, you can specify the maximum length of string literals generated by Pro*COBOL, so that compiler limits are not exceeded. For Pro*COBOL, the default value is 256, but you might have to specify a lower value.
ANSI standard National Character (NCHAR) datatypes are supported for handling multi-byte character data. The PIC N or PIC G clause declares variables that store fixed-length NCHAR strings. You can store variable-length, multi-byte NCHAR strings using COBOL group items consisting of a length field and a string field, or using the modifier VARYING.
Beginning with Oracle8, the environmental variable NLS_NCHAR is made available to specify a client-side National Character Set.
When NLS_LOCAL=YES, because dynamic SQL statements are not processed at precompile time, and the Oracle8 Server does not itself process multi-byte NLS strings, you cannot embed multi-byte NLS strings in dynamic SQL statements.
Also, when NLS_LOCAL=YES, columns storing multi-byte NLS data cannot be used in embedded data definition language (DDL) statements. This restriction cannot be enforced when precompiling, so the use of these column types within embedded DDL statements results in an execution error rather than a precompile error.
In SQL, a null represents a missing, unknown, or inapplicable column value; it equates neither to zero nor to a blank. Use the NVL function to convert nulls to non-null values, use the IS [NOT] NULL comparison operator to search for nulls, and use indicator variables to insert and test for nulls.
You can associate standard COBOL paragraph names with SQL statements, as shown in the following example:
LOAD-DATA.
EXEC SQL
INSERT INTO EMP (EMPNO, ENAME, DEPTNO)
VALUES (:EMP-NUMBER, :EMP-NAME, :DEPT-NUMBER)
END-EXEC.
Also, you can reference paragraph names in a WHENEVER ... DO or WHENEVER ... GOTO statement, as the next example shows:
PROCEDURE DIVISION.
MAIN.
EXEC SQL WHENEVER SQLERROR GOTO SQL-ERROR END-EXEC.
...
SQL-ERROR.
...
You must begin all paragraph names in columns 8 through 11.
You can use the COBOL REDEFINES clause to redefine group or elementary items. For example, the following declarations are valid:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 REC-ID PIC X(4).
01 REC-NUM REDEFINES REC-ID PIC S9(4) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
And:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 STOCK.
05 DIVIDEND PIC X(5).
05 PRICE PIC X(6).
01 BOND REDEFINES STOCK.
05 COUPON-RATE PIC X(4).
05 PRICE PIC X(7).
EXEC SQL END DECLARE SECTION END-EXEC.
Pro*COBOL issues no warning or error if a single INTO clause uses items from both a group item host variable and from its re-definition.
COBOL relational operators differ from their SQL equivalents, as shown in Table 3-1. Furthermore, COBOL allows the use of words instead of symbols, whereas SQL does not.
| SQL Operators | COBOL Operators |
|
<= |
<=, LESS THAN OR EQUAL TO |
A COBOL sentence includes one or more COBOL and/or SQL statements and ends with a period. In conditional sentences, only the last statement must end with a period, as the following example shows:
IF EMP-NUMBER = ZERO
MOVE FALSE TO VALID-DATA
PERFORM GET-EMP-NUM UNTIL VALID-DATA = TRUE
ELSE
EXEC SQL DELETE FROM EMP
WHERE EMPNO = :EMP-NUMBER
END-EXEC
ADD 1 TO DELETE-TOTAL.
END-IF.
With COBOL-74, however, if you use WHENEVER ... GOTO or WHENEVER ... STOP to handle errors for a SQL statement, the SQL statement must be terminated by a period or followed by an ELSE.
The DELETE statement below is repositioned to meet this requirement:
EXEC SQL WHENEVER SQLERROR GOTO SQL-ERROR END-EXEC.
IF EMP-NUMBER = ZERO
MOVE FALSE TO VALID-DATA
PERFORM GET-EMP-NUM UNTIL VALID-DATA = TRUE
ELSE
ADD 1 TO DELETE-TOTAL
EXEC SQL DELETE FROM EMP
WHERE EMPNO = :EMP-NUMBER
END-EXEC.
Alternatively, you can place the SQL statement in a separate paragraph and PERFORM that paragraph.
The word FILLER is allowed in host variable declarations. The word FILLER is used to specify an elementary item of a group that cannot be referred to explicitly. The following declaration is valid:
01 STOCK.
05 DIVIDEND PIC X(5).
05 FILLER PIC X.
05 PRICE PIC X(6).
Passing data between Oracle8 and your application program requires host variables and error handling. This section shows you how to meet these requirements.
When DECLARE_SECTION is set to NO (the default), the Declare Section is optional. This is a change from Pro*COBOL prior to release 8.0. (See Chapter 7, "Running the Pro*COBOL Precompiler" for details of the precompiler options.)
If DECLARE_SECTION is YES, you must declare all program variables used in SQL statements in the Declare Section, which begins with the statement
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
and ends with the statement
EXEC SQL END DECLARE SECTION END-EXEC.
Between these two statements only the following are allowed:
If DECLARE_SECTION is set to NO, you may or may not use a Declare Section. Declarations of host variables and indicator variables can be made either inside or outside a Declare Section.
For backward compatibility with releases prior to 8.0, Pro*COBOL provides this command-line option for explicit control over whether only declarations in the Declare Section are allowed as host variables. This option is
DECLARE_SECTION = YES | NO (default NO)
You must use the DECLARE_SECTION option on the command line or in a configuration file. When MODE=ORACLE and DECLARE_SECTION=YES, only variables declared inside the Declare Section are allowed as host variables. When MODE=ANSI then DECLARE_SETION is implicitly set to YES. See the discussion of macro and micro options in "Macro and Micro Options" on page 7-5.
Multiple Declare Sections are allowed per precompiled unit. Furthermore, a host program can contain several independently precompiled units.
In the following example, you declare four host variables for use later in your program.
WORKING-STORAGE SECTION.
...
* The next line is optional
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
...
01 EMP-NUMBER PIC 9(4) COMP VALUE ZERO.
01 EMP-NAME PIC X(10) VARYING.
01 SALARY PIC S9(5)V99 COMP-3 VALUE ZERO.
01 COMMISSION PIC S9(5)V99 COMP-3 VALUE ZERO.
* The next line is optional
EXEC SQL END DECLARE SECTION END-EXEC.
The INCLUDE statement lets you copy files into your host program, as the following example shows:
* Copy in the SQL Communications Area (SQLCA)
EXEC SQL INCLUDE SQLCA END-EXEC.
* Copy in the Oracle Communications Area (ORACA)
EXEC SQL INCLUDE ORACA END-EXEC.
You can INCLUDE any file. When you precompile your Pro*COBOL program, each EXEC SQL INCLUDE statement is replaced by a copy of the file named in the statement.
If your system uses file extensions but you do not specify one, Pro*COBOL assumes the default extension for source files (usually COB). The default extension is system-dependent. For more information, see your Oracle system-specific documentation.
If your system uses directories, you can set a search path for included files using the INCLUDE option, as follows:
INCLUDE=path
where path defaults to the current directory.
Pro*COBOL first searches the current directory, then the directory specified by the INCLUDE option, and finally the directory for standard INCLUDE files. You need not specify a path for standard files such as the SQLCA and ORACA. However, a path is required for nonstandard files unless they are stored in the current directory.
You can also specify multiple paths on the command line, as follows:
... INCLUDE=<path1> INCLUDE=<path2> ...
When multiple paths are specified, Pro*COBOL searches the current directory first, then the path1 directory, then the path2 directory, and so on. The directory containing standard INCLUDE files is searched last. The path syntax is system specific. For more information, see your Oracle system-specific documentation.
Remember that Pro*COBOL searches for a file in the current directory first even if you specify a search path. If the file you want to INCLUDE is in another directory, make sure no file with the same name is in the current directory or any other directory that precedes it in the search path. If your operating system is case sensitive, be sure to specify the same upper/lowercase filename under which the file is stored.
Oracle returns the success or failure of SQL statements in status variables, SQLSTATE and SQLCODE. With Oracle mode, you can declare SQLCODE by including the SQLCA. With ANSI mode. you must declare either SQLSTATE or SQLCODE. For more information, see Chapter 9, "Error Handling and Diagnostics".
Host variables are the key to communication between your host program and Oracle8. Typically, a host program inputs data to Oracle8, and Oracle8 outputs data to the program. Oracle8 stores input data in database columns and stores output data in program host variables.
Host variables are declared according to COBOL rules, using the COBOL datatypes that are supported by Oracle8. COBOL datatypes must be compatible with the source/target database column.
The supported COBOL datatypes are shown in Table 3-2.
| Variable Declaration | Description |
|
PIC X...X VARYING |
fixed-length string of 1-byte characters (1)
variable-length string of 1-byte characters (1,2) |
|
PIC N...N VARYING
PIC G...G VARYING |
fixed-length string of multi-byte NCHAR characters (1,3)
n-length string of multi-byte NCHAR characters (3)
variable-length string of multi-byte characters (2,3) variable-length (n max.) string of multi-byte characters (2,3) |
|
PIC S9...9 BINARY
PIC S9...9 COMP-4 |
|
|
COMP-1 |
floating-point number (5) |
|
PIC S9...9V9...9 COMP-3 |
|
|
PIC S9...9 COMP-5 |
|
|
PIC S9...9V9...9 DISPLAY
PIC S9(n)V9(m) DISPLAY
PIC S9...9V9...9 DISPLAY
PIC S9(n)V9(m) DISPLAY |
display trailing (9) |
|
PIC 9...9 DISPLAY |
unsigned display(10) |
|
PIC S9...9V9...9 DISPLAY
PIC S9...9V9...9 DISPLAY |
over-punch leading (10)) |
|
SQL-CURSOR |
cursor variable |
Notes:
Table 3-3 shows the compatible Oracle8 internal datatypes.
| Internal Datatype | COBOL Datatype | Description | |
|---|---|---|---|
|
CHAR(x) VARCHAR2(y) |
PIC S9...9 BINARY
PIC S9...9 COMP-5
PIC S9...9V9...9 COMP-3
PIC S9...9V9...9 DISPLAY
|
|
|
|
NUMBER |
|
PIC S9...9 BINARY
PIC S9...9 COMP-5
PIC S9...9V9...9 COMP-3
PIC S9...9V9...9 DISPLAY PIC X(n) VARYING |
character string (15) n-character string (15) n-byte variable-length string |
|
|
|
|
|
|
DATE |
PIC X...X VARYING |
n-byte variable-length string |
Notes:
In the following example, you declare several host variables for use later in your Pro*COBOL program:
...
01 STR1 PIC X(3).
01 STR2 PIC X(3) VARYING.
01 NUM1 PIC S9(5) COMP.
01 NUM2 COMP-1.
01 NUM3 COMP-2.
...
You can also declare one-dimensional tables of simple COBOL types, as the next example shows:
...
01 XMP-TABLES.
05 TAB1 PIC XXX OCCURS 3 TIMES.
05 TAB2 PIC XXX VARYING OCCURS 3 TIMES.
05 TAB3 PIC S999 COMP-3 OCCURS 3 TIMES.
...
No error or warning is issued, but any VALUES clause on a pseudo-type variable is ignored and discarded.
You can initialize host variables, except pseudo-type host variables, using the VALUE clause, as shown in the following example:
01 USERNAME PIC X(10) VALUE "SCOTT".
01 MAX-SALARY PIC S9(4) COMP VALUE 5000.
If a string value assigned to a character variable is shorter than the declared length of the variable, the string is blank-padded on the right. If the string value assigned to a character variable is longer than the declared length, the string is truncated.
You cannot use alphabetic character (PIC A) variables or edited data items as host variables. Therefore, the following variable declarations cannot be made for host variables:
....
01 AMOUNT-OF-CHECK PIC ****9.99.
01 FIRST-NAME PIC A(10).
01 BIRTH-DATE PIC 99/99/99.
....
You use host variables in SQL data manipulation statements. A host variable must be prefixed with a colon (:) in SQL statements but must not be prefixed with a colon in COBOL statements, as this example shows:
WORKING-STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 EMP-NUMBER PIC S9(4) COMP VALUE ZERO.
01 EMP-NAME PIC X(10) VALUE SPACE.
01 SALARY PIC S9(5)V99 COMP-3.
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
DISPLAY "Employee number? " WITH NO ADVANCING.
ACCEPT EMP-NUMBER.
EXEC SQL SELECT ENAME, SAL
INTO :EMP-NAME, :SALARY FROM EMP
WHERE EMPNO = :EMP-NUMBER
END-EXEC.
COMPUTE BONUS = SALARY / 10.
...
Though it might be confusing, you can give a host variable the same name as an Oracle8 table or column, as the following example shows:
WORKING-STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 EMPNO PIC S9(4) COMP VALUE ZERO.
01 ENAME PIC X(10) VALUE SPACE.
01 COMM PIC S9(5)V99 COMP-3.
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
EXEC SQL SELECT ENAME, COMM
INTO :ENAME, :COMM FROM EMP
WHERE EMPNO = :EMPNO
END-EXEC.
Pro*COBOL now allows the use of group items in embedded SQL statements. Group items with elementary items (containing only one level) can be used as host variables. The host group items (also referred to as host structures) can be referenced in the INTO clause of a SELECT or a FETCH statement, and in the VALUES list of an INSERT statement. When a group item is used as a host variable, only the group name is used in the SQL statement. For example, given the following declaration
01 DEPARTURE.
05 HOUR PIC X(2).
05 MINUTE PIC X(2).
the following statement is valid:
EXEC SQL SELECT DHOUR, DMINUTE
INTO :DEPARTURE
FROM SCHEDULE
WHERE ...
The order that the members are declared in the group item must match the order that the associated columns occur in the SQL statement, or in the database table if the column list in the INSERT statement is omitted. Using a group item as a host variable has the semantics of substituting the group item with elementary items. In the above example, it would mean substituting :DEPARTURE with :DEPARTURE.HOUR, :DEPARTURE.MINUTE.
Group items used as host variables can contain host tables. In the following example, the group item containing tables is used to INSERT three entries into the SCHEDULE table:
01 DEPARTURE.
05 HOUR PIC X(2) OCCURS 3 TIMES.
05 MINUTE PIC X(2) OCCURS 3 TIMES.
...
EXEC SQL INSERT INTO SCHEDULE ( DHOUR, DMINUTE)
VALUES ( :DEPARTURE) END-EXEC.
If VARCHAR=YES is specified, Pro*COBOL will recognize implicit VARCHARs. If the nested group item declaration resembles a VARCHAR host variable, then the entire group item is treated like an elementary item of VARYING type. See "VARCHAR" on page 7-34.
When referencing elementary items instead of the group items as host variables elementary names need not be unique because you can qualify them using the following syntax:
<group_item>.<elementary_item>
This naming convention is allowed only in SQL statements. It is similar to the IN (or OF) clause in COBOL, examples of which follow:
MOVE MINUTE IN DEPARTURE TO MINUTE-OUT.
DISPLAY HOUR OF DEPARTURE.
The COBOL IN (or OF) clause is not allowed in SQL statements. Qualify elementary names to avoid ambiguity. For example:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 DEPARTURE.
05 HOUR PIC X(2).
05 MINUTE PIC X(2).
01 ARRIVAL.
05 HOUR PIC X(2).
05 MINUTE PIC X(2).
EXEC SQL END DECLARE SECTION END-EXEC.
A host variable cannot substitute for a column, table, or other Oracle8 object in a SQL statement and must not be an Oracle8 reserved word. See Appendix C, "Oracle8 Reserved Words, Keywords, and Namespaces"for a list of Oracle8 reserved words and keywords.
Nesting programs in COBOL means that you place one program inside another. The contained programs may reference some of the resources of the programs within which they are contained. The names within the higher-level program and the nested program can be the same, and describe different data items without conflict, because the names are known only within the programs. However, names described in the Configuration Section of the higher-level program can be referenced in the nested program.
The higher-level program can contain several nested programs. Likewise, nested programs can have programs nested within them. You must place the nested program directly before the END PROGRAM header of the program in which it is nested.
You can call a nested program only by a program in which it is either directly or indirectly nested. If you want a nested program to be called by any program, even one on a different branch of the nested tree structure, you code the COMMON clause in the PROGRAM-ID paragraph of the nested program. You can code COMMON only for nested programs:
PROGRAM-ID. <nested-program-name> COMMON.
You can code the GLOBAL phrase for File Definitions and level 01 data items (any subordinate items automatically become global). This allows them to be referenced in all subprograms directly or indirectly contained within them. You code GLOBAL on the higher-level program. If the nested program defines the same name as one declared GLOBAL in a higher-level program, COBOL uses the declaration within the nested program. If the data item contains a REDEFINES clause, GLOBAL must follow it.
FD file-name GLOBAL ...
01 data-name1 GLOBAL ...
01 data-name2 REDEFINES data-name3 GLOBAL ...
Pro*COBOL allows nested programs with embedded SQL within a single source file. All 01 level items which are marked as global in a containing program and are valid host variables at the containing program level are usable as valid host variables in any programs directly or indirectly contained by the containing program. Consider the following example:
IDENTIFICATION DIVISION.
PROGRAM-ID. MAINPROG.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 REC1 GLOBAL.
05 VAR1 PIC X(10).
05 VAR2 PIC X(10).
01 VAR1 PIC X(10) GLOBAL.
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
...
<main program statements>
...
IDENTIFICATION DIVISION.
PROGRAM-ID. NESTEDPROG.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 VAR1 PIC S9(4).
PROCEDURE DIVISION.
...
EXEC SQL SELECT X, Y INTO :REC1 FROM ... END-EXEC.
EXEC SQL SELECT X INTO :VAR1 FROM ... END-EXEC.
EXEC SQL SELECT X INTO :REC1.VAR1 FROM ... END-EXEC.
...
END PROGRAM NESTEDPROG.
END PROGRAM MAINPROG.
The main program declares the host variable REC1 as global and thus the nested program can use REC1 in the first select statement without having to declare it. Since VAR1 is declared as a global variable and also as a local variable in the nested program, the second select statement will use the VAR1 declared as S9(4), overriding the global declaration. In the third select statement, the global VAR1 of REC1 declared as PIC X(10) is used.
The previous paragraph describes the results when DECLARE_SECTION=NO is used. When DECLARE_SECTION=YES, Pro*COBOL will not recognize host variables unless they are declared inside a Declare Section. If the above program is precompiled with DECLARE_SECTION=YES, then the second select statement would result in an ambiguous host variable error. The first and third select statements would function the same.
Note: Recursive nested programs are not supported
About declaring the SQLCA for nested programs, (see "SQLCA" on page 9-3 and later), the included SQLCA definition provided will be declared as global, so the declaration of SQLCA is only required in the higher-level program. The SQLCA can change each time a new SQL statement is executed. The SQLCA provided can always be modified to remove the global specification if you want to declare additional SQLCA areas in the nested programs. The same will apply to SQLDA and ORACA.
IDENTIFICATION DIVISION.
PROGRAM-ID. NESTED.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) VARYING.
01 PASSWD PIC X(10) VARYING.
01 GEMP-REC-VAR1 GLOBAL.
05 EMP-NUM PIC S9(4) COMP.
05 EMP-NAME PIC X(10) VARYING.
05 SALARY PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
05 COMMISSION PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
01 EMP-NAME PIC X(10) VARYING GLOBAL.
01 EMP-NUM PIC S9(4) COMP GLOBAL.
01 EMP-REC-VAR1.
05 EMP-NUM PIC S9(4) COMP.
05 EMP-NAME PIC X(10) VARYING.
05 SALARY PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
05 COMMISSION PIC S9(6)V99
DISPLAY SIGN LEADING SEPARATE.
01 GEMP-REC-IND1 GLOBAL.
05 EMP-NUM-IND PIC S9(4) COMP.
05 EMP-NAME-IND PIC S9(4) COMP.
05 EMP-SAL-IND PIC S9(4) COMP.
05 EMP-COMM-IND PIC S9(4) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE SQLCA END-EXEC.
01 DISPLAY-VARIABLES GLOBAL.
05 D-EMP-NUM PIC Z(3)9.
05 D-EMP-NAME PIC X(10).
05 D-SALARY PIC Z(4)9.99.
05 D-COMMISSION PIC Z(4)9.99.
PROCEDURE DIVISION.
BEGIN-PGM.
EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC.
PERFORM LOGON.
DISPLAY "In Main Program".
CALL "INNER1".
CALL "NESTED1".
GO TO SIGN-OFF.
LOGON.
MOVE "SCOTT" TO USERNAME-ARR.
MOVE 5 TO USERNAME-LEN.
MOVE "TIGER" TO PASSWD-ARR.
MOVE 5 TO PASSWD-LEN.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
DISPLAY " ".
DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR.
DISPLAY " ".
SIGN-OFF.
DISPLAY " ".
DISPLAY "HAVE A GOOD DAY.".
DISPLAY " ".
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
STOP RUN.
SQL-ERROR.
EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
DISPLAY " ".
DISPLAY "ORACLE ERROR DETECTED:".
DISPLAY " ".
DISPLAY SQLERRMC.
EXEC SQL ROLLBACK WORK RELEASE END-EXEC.
STOP RUN.
IDENTIFICATION DIVISION.
PROGRAM-ID. INNER1 COMMON.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 EMP-NAME IS GLOBAL PIC X(15) VARYING.
PROCEDURE DIVISION.
P1.
DISPLAY "In Inner1 Nested Program".
* Using a global host var.
EXEC SQL SELECT EMPNO, ENAME, SAL, COMM
INTO :GEMP-REC-VAR1:GEMP-REC-IND1
FROM EMP WHERE EMPNO = 7566 END-EXEC.
DISPLAY " ".
DISPLAY "EMPNO SALESPERSON SALARY COMMISSION".
DISPLAY "----- ----------- ---------- ----------".
MOVE EMP-NUM OF GEMP-REC-VAR1 TO D-EMP-NUM.
MOVE EMP-NAME-ARR OF GEMP-REC-VAR1 TO D-EMP-NAME.
MOVE SALARY OF GEMP-REC-VAR1 TO D-SALARY.
MOVE COMMISSION OF GEMP-REC-VAR1 TO D-COMMISSION.
DISPLAY D-EMP-NUM, " ", D-EMP-NAME, " ", D-SALARY,
" ", D-COMMISSION.
DISPLAY "Answers should be 7566, JONES, 2975, 0".
* overriding global host var with a local one.
* should use PIC X(15) decl.
DISPLAY " ".
EXEC SQL SELECT ENAME INTO :EMP-NAME
FROM EMP WHERE EMPNO = 7499 END-EXEC.
DISPLAY "Emp Name: ", EMP-NAME, "**".
DISPLAY "Emp Name should be ALLEN".
* Using the element of a global host var.
DISPLAY " ".
EXEC SQL SELECT ENAME INTO :GEMP-REC-VAR1.EMP-NAME
FROM EMP WHERE EMPNO = 7499 END-EXEC.
DISPLAY "Emp Name: ", EMP-NAME, "**".
DISPLAY "Emp Name should be ALLEN".
CALL "INNER2".
IDENTIFICATION DIVISION.
PROGRAM-ID. INNER2 COMMON.
ENVIRONMENT DIVISION.
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 EMP-NUM PIC S9(4) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
PROCEDURE DIVISION.
P2.
DISPLAY "In Inner2 Nested Program".
* Using a global host var even when not directly within the
* main program.
EXEC SQL SELECT EMPNO, ENAME, SAL, COMM
INTO :GEMP-REC-VAR1:GEMP-REC-IND1
FROM EMP WHERE EMPNO = 7566 END-EXEC.
DISPLAY " ".
DISPLAY "EMPNO SALESPERSON SALARY COMMISSION".
DISPLAY "----- ----------- ---------- ----------".
MOVE EMP-NUM OF GEMP-REC-VAR1 TO D-EMP-NUM.
MOVE EMP-NAME-ARR OF GEMP-REC-VAR1 TO D-EMP-NAME.
MOVE SALARY OF GEMP-REC-VAR1 TO D-SALARY.
MOVE COMMISSION OF GEMP-REC-VAR1 TO D-COMMISSION.
DISPLAY D-EMP-NUM, " ", D-EMP-NAME, " ", D-SALARY,
" ", D-COMMISSION.
DISPLAY "Answers should be 7566, JONES, 2975, 0".
* Using a global host var in a nested function and a
* local host var. Should use PIC X(15) decl from
* INNER1 for EMP-NAME, and local EMP-NUM.
DISPLAY " ".
EXEC SQL SELECT ENAME, EMPNO INTO :EMP-NAME, :EMP-NUM
FROM EMP WHERE EMPNO = 7499 END-EXEC.
DISPLAY "Emp Name: ", EMP-NAME, "Emp Number: ", EMP-NUM.
DISPLAY "Emp name should be ALLEN and emp number 7499".
* Using the element of a global host var even when indirectly
* within the main program.
DISPLAY " ".
EXEC SQL SELECT ENAME INTO :GEMP-REC-VAR1.EMP-NAME
FROM EMP WHERE EMPNO = 7499 END-EXEC.
DISPLAY "Emp Name: ", EMP-NAME, "**".
DISPLAY "Emp Name should be ALLEN".
END PROGRAM INNER2.
END PROGRAM INNER1.
IDENTIFICATION DIVISION.
PROGRAM-ID. NESTED1.
DATA DIVISION.
WORKING-STORAGE SECTION.
01 EMP-NAME PIC X(15) VARYING GLOBAL.
PROCEDURE DIVISION.
N1.
DISPLAY "In Nested1 Nested Program".
DISPLAY "Calling inner1".
* Should work since INNER1 has the COMMON clause.
CALL "INNER1".
END PROGRAM NESTED1.
When you execute the COBOL program created by running Pro*COBOL, the resulting output is as follows:
> nested
CONNECTED TO ORACLE AS USER: SCOTT
In Main Program
In Inner1 Nested Program
EMPNO SALESPERSON SALARY COMMISSION
----- ----------- ---------- ----------
7566 JONES 2975.00 0.00
Answers should be 7566, JONES, 2975, 0
Emp Name: ALLEN **
Emp Name should be ALLEN
Emp Name: ALLEN **
Emp Name should be ALLEN
In Inner2 Nested Program
EMPNO SALESPERSON SALARY COMMISSION
----- ----------- ---------- ----------
7566 JONES 2975.00 0.00
Answers should be 7566, JONES, 2975, 0
Emp Name: ALLEN Emp Number: +7499
Emp name should be ALLEN and emp number 7499
Emp Name: ALLEN **
Emp Name should be ALLEN
In Nested1 Nested Program
Calling inner1
In Inner1 Nested Program
EMPNO SALESPERSON SALARY COMMISSION
----- ----------- ---------- ----------
7566 JONES 2975.00 0.00
Answers should be 7566, JONES, 2975, 0
Emp Name: ALLEN **
Emp Name should be ALLEN
Emp Name: ALLEN **
Emp Name should be ALLEN
In Inner2 Nested Program
EMPNO SALESPERSON SALARY COMMISSION
----- ----------- ---------- ----------
7566 JONES 2975.00 0.00
Answers should be 7566, JONES, 2975, 0
Emp Name: ALLEN Emp Number: +7499
Emp name should be ALLEN and emp number 7499
Emp Name: ALLEN **
Emp Name should be ALLEN
HAVE A GOOD DAY.
You can associate any host variable with an optional indicator variable. Each time the host variable is used in a SQL statement, a result code is stored in its associated indicator variable. Thus, indicator variables let you monitor host variables.
You use indicator variables in the VALUES or SET clause to assign nulls to input host variables and in the INTO clause to detect nulls or truncated values in output host variables.
An indicator variable must be explicitly declared as PIC S9(4) COMP and must not be an Oracle8 reserved word. In the following example, you declare an indicator variable named COMM-IND (the name is arbitrary):
WORKING-STORAGE SECTION.
...
01 EMP-NAME PIC X(10) VALUE SPACE.
01 SALARY PIC S9(5)V99 COMP-3.
01 COMMISSION PIC S9(5)V99 COMP-3.
01 COMM-IND PIC S9(4) COMP.
...
In SQL statements, an indicator variable must be prefixed with a colon and appended to its associated host variable. In COBOL statements, an indicator variable must not be prefixed with a colon or appended to its associated host variable. An example follows:
EXEC SQL SELECT SAL, COMM
INTO :SALARY, :COMMISSION:COMM-IND FROM EMP
WHERE EMPNO = :EMP-NUMBER
END-EXEC.
IF COMM-IND = -1
COMPUTE PAY = SALARY
ELSE
COMPUTE PAY = SALARY + COMMISSION.
To improve readability, you can precede any indicator variable with the optional keyword INDICATOR. You must still prefix the indicator variable with a colon. The correct syntax is
:<host_variable> INDICATOR :<indicator_variable>
and is equivalent to
:<host_variable>:<indicator_variable>
You can use both forms of expression in your host program.
Indicator variables cannot be used in the WHERE clause to search for nulls. For example, the following DELETE statement triggers an error at run time:
* Set indicator variable.
COMM-IND = -1
EXEC SQL
DELETE FROM EMP WHERE COMM = :COMMISSION:COMM-IND
END-EXEC.
The correct syntax follows:
EXEC SQL
DELETE FROM EMP WHERE COMM IS NULL
END-EXEC.
When DBMS=V6, Oracle8 does not issue an error message if you SELECT or FETCH a null into a host variable that is not associated with an indicator variable. However, when DBMS=V7 or V8, if you SELECT or FETCH a null into a host variable that has no indicator, Oracle8 issues the following error message:
ORA-01405: fetched column value is NULL
When precompiling with MODE=ORACLE and DBMS=V7 or V8, you can disable the ORA-01405 message by also specifying UNSAFE_NULL=YES on the command line. For more information, see Chapter 7, "Running the Pro*COBOL Precompiler".
When MODE=ORACLE, if you SELECT or FETCH a truncated column value into a host variable that is not associated with an indicator variable, Oracle8 issues the following error message:
ORA-01406: fetched column value was truncated
However, when MODE={ANSI|ANSI14|ANSI13}, no error is generated. Values for indicator variables are discussed in Chapter 5, "Using Embedded SQL".
Indicator variables for multi-byte NCHAR character variables can be used as with any other host variable. However, a positive value (the result of a SELECT or FETCH was truncated) represents the string length in multi-byte characters instead of 1-byte characters.
To use indicator variables with a host group item, either setup a second group item that contains an indicator variable for each nullable variable in the group item or use a table of half-word integer variables. You do NOT have to have an indicator variable for each variable in the group item, but the nullable fields which you wish to use indicators for must be placed at the beginning of the data group item. The following indicator group item can be used with the DEPARTURE group item:
01 DEPARTURE-IND.
05 HOUR-IND PIC S9(4) COMP.
05 MINUTE-IND PIC S9(4) COMP.
If you use an indicator table, you do NOT have to declare a table of as many elements as there are members in the host group item. The following indicator table can be used with the DEPARTURE group item:
01 DEPARTURE-IND PIC S9(4) COMP OCCURS 2 TIMES.
Reference the indicator group item in the SQL statement in the same way that a host indicator variable is referenced:
EXEC SQL SELECT DHOUR, DMINUTE
INTO :DEPARTURE:DEPARTURE-IND
FROM SCHEDULE
WHERE ...
When the query completes, the NULL/NOT NULL status of each selected component is available in the host indicator group item. The restrictions on indicator host variables and the ANSI requirements also apply to host indicator group items.
Host tables can boost performance by letting you manipulate an entire collection of data items with a single SQL statement. With few exceptions, you can use host tables wherever scalar host variables are allowed. Also, you can associate an indicator table with any host table.
You declare and dimension host tables in the Data Division. In the following example, three host tables are declared, each dimensioned with 50 elements:
....
01 EMP-TABLES.
05 EMP-NUMBER OCCURS 50 TIMES PIC S9(4) COMP.
05 EMP-NAME OCCURS 50 TIMES PIC X(10.
05 SALARY OCCURS 50 TIMES PIC S9(5)V99 COMP-3.
....
You can use the INDEXED BY phrase in the OCCURS clause to specify an index, as the next example shows:
...
01 EMP-TABLES.
05 EMP-NUMBER PIC X(10) OCCURS 50 TIMES
INDEXED BY EMP-INDX.
...
...
The INDEXED BY phrase implicitly declares the index item EMP-INDX.
Multi-dimensional host tables are not allowed. Thus, the two-dimensional host table declared in the following example is invalid:
...
01 NATION.
05 STATE OCCURS 50 TIMES.
10 STATE-NAME PIC X(25).
10 COUNTY OCCURS 25 TIMES.
15 COUNTY-NAME PIX X(25).
...
Variable-length host tables are not allowed either. For example, the following declaration of EMP-REC is invalid for a host variable:
...
01 EMP-FILE.
05 REC-COUNT PIC S9(3) COMP.
05 EMP-REC OCCURS 0 TO 250 TIMES
DEPENDING ON REC-COUNT.
...
If you use multiple host tables in a single SQL statement, their dimensions should be the same. This is not a requirement, however, because Pro*COBOL always uses the smallest dimension for the SQL operation. In the following example, only 25 rows are INSERTed:
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 EMP-TABLES.
05 EMP-NUMBER PIC S9(4) COMP OCCURS 50 TIMES.
05 EMP-NAME PIC X(10) OCCURS 50 TIMES.
05 DEPT-NUMBER PIC S9(4) COMP OCCURS 25 TIMES.
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
* Populate host tables here.
...
EXEC SQL INSERT INTO EMP (EMPNO, ENAME, DEPTNO)
VALUES (:EMP-NUMBER, :EMP-NAME, :DEPT-NUMBER)
END-EXEC.
Host tables must not be subscripted in SQL statements. For example, the following INSERT statement is invalid:
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 EMP-TABLES.
05 EMP-NUMBER PIC S9(4) COMP OCCURS 50 TIMES.
05 EMP-NAME PIC X(10) OCCURS 50 TIMES.
05 DEPT-NUMBER PIC S9(4) COMP OCCURS 50 TIMES.
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
PERFORM LOAD-EMP VARYING J FROM 1 BY 1 UNTIL J > 50.
...
LOAD-EMP.
EXEC SQL INSERT INTO EMP (EMPNO, ENAME, DEPTNO)
VALUES (:EMP-NUMBER(J), :EMP-NAME(J),
:DEPT-NUMBER(J))
END-EXEC.
You need not process host tables in a PERFORM VARYING statement. Instead, use the un-subscripted table names in your SQL statement. Oracle8 treats a SQL statement containing host tables of dimension n like the same statement executed n times with n different scalar host variables, except its more efficient. For more information, see "Host Tables" on page 3-30.
You can use indicator tables to assign nulls to elements in input host tables and to detect nulls or truncated values in output host tables. The following example shows how to INSERT with indicator tables:
WORKING-STORAGE SECTION.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 EMP-TABLES.
05 EMP-NUMBER PIC S9(4) COMP OCCURS 50 TIMES.
05 DEPT-NUMBER PIC S9(4) COMP OCCURS 50 TIMES.
05 COMMISSION PIC S9(5)V99 COMP-3 OCCURS 50 TIMES.
05 COMM-IND PIC S9(4) COMP OCCURS 50 TIMES.
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
* Populate the host and indicator tables.
* Set indicator table to all zeros.
...
EXEC SQL INSERT INTO EMP (EMPNO, DEPTNO, COMM)
VALUES (:EMP-NUMBER, :DEPT-NUMBER,
:COMMISSION:COMM-IND)
END-EXEC.
The dimension of the indicator table must be greater than, or equal to, the dimension of the host table.
Note: If you have a host group item containing tables, you cannot use a table of half-word integer variables for an indicator. You must use a corresponding group item of tables for an indicator. For example, if your group item is the following:
01 DEPARTURE.
05 HOUR PIC X(2) OCCURS 3 TIMES.
05 MINUTE PIC X(2) OCCURS 3 TIMES.
the following indicator variable cannot be used:
01 DEPARTURE-IND PIC S9(4) COMP OCCURS 6 TIMES.
The indicator variable you use with the group item of tables must itself be a group item of tables such as the following:
01 DEPARTURE-IND.
05 HOUR-IND PIC S9(4) COMP OCCURS 3 TIMES.
05 MINUTE-IND PIC S9(4) COMP OCCURS 3 TIMES.
COBOL string datatypes are fixed length. However, Pro*COBOL lets you declare a variable-length string pseudotype called VARCHAR.
You define a VARCHAR host variable by adding the keyword VARYING to its declaration, as shown in the following example:
...
01 ENAME PIC X(15) VARYING.
...
The COBOL VARYING phrase is used in PERFORM and SEARCH statements to increment subscripts and indexes. Do not confuse this with the Pro*COBOL VARYING clause in the preceding example.
VARCHAR is an extended Pro*COBOL datatype or pre-declared group item. For example, Pro*COBOL expands the VARCHAR declaration
01 ENAME PIC X(15) VARYING.
into a group item with length and string fields, as follows:
01 ENAME.
05 ENAME-LEN PIC S9(4) COMP.
05 ENAME-ARR PIC X(15).
The length field (suffixed with -LEN) holds the current length of the value stored in the string field (suffixed with -ARR). The maximum length in the VARCHAR host-variable declaration must be in the range of 1 to 65533 bytes.
The advantage of using VARCHAR variables is that you can explicitly set and reference the length field. With input host variables, Oracle8 reads the value of the length field and uses that many characters of the string field. With output host variables, Oracle8 sets the length value to the length of the character string stored in the string field.
Pro*COBOL implicitly recognizes some group items as VARCHAR host variables when the precompiler option VARCHAR=YES is specified on the command line. For variable-length single-byte character types, use the following structure (length expressed in single-byte characters):
<nn> data-name-1.
49 data-name-2 PIC S9(4) COMP.
49 data-name-3 PIC X(<length>).
nn must be 01 through 48. For variable-length multi-byte NCHAR character types, use these formats (length expressed in double-byte characters):
<nn> data-name-1.
49 data-name-2 PIC S9(4) COMP.
49 data-name-3 PIC N(<length>).
or,
<nn> data-name-1.
49 data-name-2 PIC S9(4) COMP.
49 data-name-3 PIC G(<length>).
The elementary items in these group-item structures must be declared as level 49 for Pro*COBOL to recognize them as VARCHAR host variables.
The VARCHAR=YES command line option must be specified for Pro*COBOL to recognize the extended form of the VARCHAR group items. If VARCHAR=NO, then any declarations that resemble the above formats will be interpreted as regular group items. If VARCHAR=YES and a group item declaration format looks similar (but not identical) to the extended VARCHAR format, then the item will be interpreted as a regular group item rather than a VARCHAR group item. For example, if VARCHAR=YES is specified and you write the following:
01 lastname
48 lastname-len PIC S9(4) USAGE COMP.
48 lastname-text PIC X(15).
then, since level 48 instead of 49 is used for the group item elements, the item is interpreted as a regular group item rather than a VARCHAR group item.
For more information about the Pro*COBOL VARCHAR option, see Chapter 7, "Running the Pro*COBOL Precompiler"
In SQL statements, you reference a VARCHAR variable using the group name prefixed with a colon, as the following example shows:
WORKING-STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
...
01 PART-NUMBER PIC X(5).
01 PART-DESC PIC X(20) VARYING.
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
EXEC SQL
SELECT PDESC INTO :PART-DESC FROM PARTS
WHERE PNUM = :PART-NUMBER
END-EXEC.
After the query executes, PART-DESC-LEN holds the actual length of the character string retrieved from the database and stored in PART-DESC-ARR.
In COBOL statements, you can reference VARCHAR variables using the group name or the elementary items, as this example shows:
WORKING-STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
...
01 EMP-TABLES.
05 EMP-NAME OCCURS 50 TIMES PIC X(15) VARYING.
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
PERFORM DISPLAY-NAME
VARYING J FROM 1 BY 1 UNTIL J > NAME-COUNT.
...
DISPLAY-NAME.
DISPLAY EMP-NAME-ARR OF EMP-NAME(J).
This section explains how Pro*COBOL handles character host variables. There are two kinds of single-byte character host variables and two kinds of multi-byte NLS character host variables:
Attention: Before using multi-byte NCHAR datatypes, verify that the PIC N or PIC G datatype is supported by your COBOL compiler.
Starting in Pro*COBOL 8.0, the default datatype of PIC X variables is changed from VARCHAR2 to CHARF. The new precompiler command line option, PICX, is provided for backward compatibility. PICX can be entered only on the command line or in a configuration file. See "PICX" on page 7-30 for more details.
The PICX option determines how Pro*COBOL treats data in character strings. The PICX option allows your program to use ANSI fixed-length strings or to maintain compatibility with previous versions of the Oracle8 Server and Pro*COBOL.
You must use PICX=VARCHAR2 (not the default) to obtain the same results as releases of Pro*COBOL before 8.0.
Fixed-length character variables are declared using the PIC X(n) and PIC G(n) and PIC N(n) datatypes. These types of variables handle character data based on their roles as input or output variables.
When PICX=VARCHAR2, the program interface strips trailing blanks before sending the value to the database. If you insert into a fixed-length CHAR column, Oracle8 re-appends trailing blanks up to the length of the database column. However, if you insert into a variable-length VARCHAR2 column, Oracle8 never appends blanks.
When PICX=CHARF, trailing blanks are never stripped.
Make sure that the input value is not trailed by extraneous characters. For example, nulls are not stripped and are inserted into the database. Normally, this is not a problem because when a value is ACCEPTed or MOVEd into a PIC X(n) variable, COBOL appends blanks up to the length of the variable.
The following example illustrates the point:
WORKING-STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 EMPLOYEES.
05 EMP-NAME PIC X(10).
05 DEPT-NUMBER PIC S9(4) VALUE 20 COMP.
05 EMP-NUMBER PIC S9(9) VALUE 9999 COMP.
05 JOB-NAME PIC X(8).
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
DISPLAY "Employee name? " WITH NO ADVANCING.
ACCEPT EMP-NAME.
* Assume that the name MILLER was entered
* EMP-NAME contains "MILLER " (4 trailing blanks)
MOVE "SALES" TO JOB-NAME.
* JOB-NAME now contains "SALES " (3 trailing blanks)
EXEC SQL INSERT INTO EMP (EMPNO, ENAME, DEPTNO, JOB)
VALUES (:EMP-NUMBER, :EMP-NAME, :DEPT-NUMBER, :JOB-NAME
END-EXEC.
...
If you precompile the last example with PICX=VARCHAR2 and the target database columns are VARCHAR2, the program interface strips the trailing blanks on input and inserts just the 6-character string "MILLER" and the 5-character string "SALES" into the database. However, if the target database columns are CHAR, the strings are blank-padded to the width of the columns.
If you precompile the last example with PICX=CHARF and the JOB column is defined as CHAR(10), the value inserted into that column is "SALES#####" (five trailing blanks). However, if the JOB column is defined as VARCHAR2(10), the value inserted is "SALES###" (three trailing blanks), because the host variable is declared as PIC X(8). This might not be what you want, so be careful.
The PICX option has no effect on output to fixed-length character variables. When you use a PIC X(n) variable as an output host variable, Oracle8 blank-pads it. In our example, when your program fetches the string "MILLER" from the database, EMP-NAME contains the value "MILLER####" (with four trailing blanks). This character string can be used without change as input to another SQL statement.
Tables Disallowed. Host variables declared using the PIC N or PIC G datatype must not be tables.
No Odd-Byte Widths. Oracle8 CHAR columns should not be used to store multi-byte NCHAR characters. A run-time error is generated if data with an odd number of bytes is FETCHed from a single-byte column into a multi-byte NCHAR host variable.
No Host Variable Equivalencing. Multi-byte NCHAR character variables cannot be equivalenced using an EXEC SQL VAR statement.
No Dynamic SQL. Dynamic SQL is not available for NCHAR multi-byte character string host variables in Pro*COBOL.
Functions should not be used on columns that store multi-byte NLS data.
VARCHAR variables handle character data based on their roles as input or output variables.
When you use a VARCHAR variable as an input host variable, your program must assign values to the length and string fields of the expanded VARCHAR declaration, as shown in the following example:
IF ENAME-IND = -1
MOVE "NOT AVAILABLE" TO ENAME-ARR
MOVE 13 TO ENAME-LEN.
You need not blank-pad the string variable. In SQL operations, Oracle8 uses exactly the number of characters given by the length field, counting any spaces.
Host input variables for multi-byte NLS data are not stripped of trailing double-byte spaces. The length component is assumed to be the length of the data in characters, not bytes.
When you use a VARCHAR variable as an output host variable, Oracle8 sets the length field. An example follows:
WORKING-STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 EMPNO PIC S9(4) COMP.
01 ENAME PIC X(15) VARYING.
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
...
EXEC SQL
SELECT ENAME INTO :ENAME FROM EMP
WHERE EMPNO = :EMPNO
END-EXEC.
IF ENAME-LEN = 0
MOVE FALSE TO VALID-DATA.
An advantage of VARCHAR variables over fixed-length strings is that the length of the value returned by Oracle8 is available right away. With fixed-length strings, to get the length of the value, your program must count the number of characters.
Host output variables for multi-byte NCHAR data are not padded at all. The length of the buffer is set to the length in characters, not bytes..
Your Pro*COBOL program must log on to Oracle before querying or manipulating data. To log on, you use the CONNECT statement, as in
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
where USERNAME and PASSWD are PIC X(n) or PIC X(n) VARYING host variables. Alternatively, you can use the statement
EXEC SQL
CONNECT :USR-PWD
END-EXEC.
where the host variable USR-PWD contains your username and password separated by a slash (/).
The syntax for the CONNECT statement has an optional ALTER AUTHORIZATION clause. The syntax (Oracle8 and later) for CONNECT is shown here:
EXEC SQL CONNECT { :user IDENTIFIED BY :oldpswd | :usr_psw }
[ [ AT { dbname | :host_variable }] USING :connect_string ]
[ ALTER AUTHORIZATION :newpswd ]
(The ALTER AUTHORIZATION clause is explained in "Changing Passwords at Runtime" on page 3-52.)
The CONNECT statement must be the first SQL statement executed by the program. That is, other executable SQL statements can positionally, but not logically, precede the CONNECT statement. If the precompiler option AUTO_CONNECT=YES, a CONNECT statement is not needed.)
To supply the Oracle username and password separately, you define two host variables as character strings or VARCHAR variables. If you supply a userid containing both username and password, only one host variable is needed.
Make sure to set the username and password variables before the CONNECT is executed or it will fail. Your program can prompt for the values or you can hard-code them, as follows:
WORKING STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) VARYING.
01 PASSWD PIC X(10) VARYING.
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
LOGON.
MOVE "SCOTT" TO USERNAME-ARR.
MOVE 5 TO USERNAME-LEN.
MOVE "TIGER" TO PASSWD-ARR.
MOVE 5 TO PASSWD-LEN.
EXEC SQL WHENEVER SQLERROR GOTO LOGON-ERROR END-EXEC.
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWD
END-EXEC.
However, you cannot hard-code a username and password into the CONNECT statement or use quoted literals. For example, the following statements are invalid:
EXEC SQL
CONNECT SCOTT IDENTIFIED BY TIGER
END-EXEC.
EXEC SQL
CONNECT "SCOTT" IDENTIFIED BY "TIGER"
END-EXEC.
See "Sample Tables" on page 2-15
To connect using a Net8 driver, substitute a service name, as defined in your tnsnames.ora configuration file or in Oracle Names, in place of the SQL*Net V1 connect string.
If you are using Oracle Names, the name server obtains the service name from the network definition database.
Note: SQL*Net V1 does work with Oracle8.
See Net3 Administrator's Guide for more information about Net8.
You can log on to Oracle automatically with the userid:
<prefix><username>
where prefix is the value of the Oracle initialization parameter OS_AUTHENT_PREFIX (the default value is OPS$) and username is your operating system user or task name. For example, if the prefix is OPS$, your user name is TBARNES, and OPS$TBARNES is a valid Oracle userid, you log on to Oracle as user OPS$TBARNES.
To take advantage of the automatic logon feature, you simply pass a slash (/) character to Pro*COBOL, as follows:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 ORACLEID PIC X.
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
MOVE '/' TO ORACLEID.
EXEC SQL CONNECT :ORACLEID END-EXEC.
This automatically connects you as user OPS$username. For example, if your operating system username is RHILL, and OPS$RHILL is a valid Oracle username, connecting with a slash (/) automatically logs you on to Oracle as user OPS$RHILL.
You can also pass a character string to Pro*COBOL. However, the string cannot contain trailing blanks. For example, the following CONNECT statement will fail:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 ORACLEID PIC X(5).
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
MOVE '/ ' TO ORACLEID.
EXEC SQL CONNECT :ORACLEID END-EXEC.
Pro*COBOL lets your program log on to the default database without using the CONNECT statement. Simply specify the precompiler option AUTO_CONNECT on the command line.
Assume that the default value of OS_AUTHENT_PREFIX is OPS$, your username is TBARNES, and OPS$TBARNES is a valid Oracle userid. When AUTO_CONNECT=YES, as soon as Pro*COBOL encounters an executable SQL statement, your program logs on to Oracle automatically with the userid OPS$TBARNES.
When AUTO_CONNECT=NO (the default), you must use the CONNECT statement to log on to Oracle.
Pro*COBOL supports distributed processing via Net8. Your application can concurrently access any combination of local and remote databases or make multiple connections to the same database. In Figure 3-1, an application program communicates with one local and three remote Oracle8 databases. ORA2, ORA3, and ORA4 are simply logical names used in CONNECT statements.
By eliminating the boundaries in a network between different machines and operating systems, Net8 provides a distributed processing environment for Oracle tools. This section shows you how the Pro*COBOL supports distributed processing via Net8. You learn how your application can
The communicating points in a network are called nodes. Net8 lets you transmit information (SQL statements, data, and status codes) over the network from one node to another.
A protocol is a set of rules for accessing a network. The rules establish such things as procedures for recovering after a failure and formats for transmitting data and checking errors.
The Net8 syntax for connecting to the default database in the local domain is simply to use the service name for the database.
If the service name is not in the default (local) domain, you must use a global specification (all domains specified). For example:
HR.US.ORACLE.COM
Each node has a default database. If you specify a node but no database in your CONNECT statement, you connect to the default database on the named local or remote node. If you specify no database and no node, you connect to the default database on the current node. Although it is unnecessary, you can specify the default database and current node in your CONNECT statement.
A default connection is made using a CONNECT statement without an AT clause. The connection can be to any default or non-default database at any local or remote node. SQL statements without an AT clause are executed against the default connection. Conversely, a non-default connection is made by a CONNECT statement that has an AT clause. A SQL statement with an AT clause is executed against the non-default connection.
All database names must be unique, but two or more database names can specify the same connection. That is, you can have multiple connections to any database on any node.
Usually, you establish a connection to Oracle as follows:
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD END-EXEC.
Or, you can use:
EXEC SQL CONNECT :USR-PWD END-EXEC.
where USR-PWD contains USERNAME/PASSWORD.
You can also log on automatically as shown on page 3-41.
If you do not specify a database and node, you are connected to the default database at the current node. If you want to connect to a different database, you must explicitly identify that database.
With explicit logons, you connect to another database directly, giving the connection a name that will be referenced in SQL statements. You can connect to several databases at the same time and to the same database multiple times.
In the following example, you connect to a single non-default database at a remote node:
* -- Declare necessary host variables
WORKING-STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) .
01 PASSWORD PIC X(10) .
01 DB-STRING PIC X(20) .
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
MOVE "scott" TO USERNAME.
MOVE "tiger" TO PASSSWORD.
MOVE "nyremote" TO DB-STRING.
...
* -- Assign a unique name to the database connection.
EXEC SQL DECLARE DBNAME DATABASE END-EXEC.
* -- Connect to the non-default database
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWORD
AT DBNAME USING :DB-STRING
END-EXEC.
The identifiers in this example serve the following purposes:
The USING clause specifies the network, machine, and database to be associated with DBNAME. Later, SQL statements using the AT clause (with DBNAME) are executed at the database specified by DB-STRING.
Alternatively, you can use a character host variable in the AT clause, as the following example shows:
* -- Declare necessary host variables
WORKING-STORAGE SECTION.
...
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10).
01 PASSWORD PIC X(10).
01 DB-NAME PIC X(10).
01 DB-STRING PIC X(20).
...
EXEC SQL END DECLARE SECTION END-EXEC.
...
PROCEDURE DIVISION.
MOVE "scott" TO USERNAME.
MOVE "tiger" TO PASSSWORD.
MOVE "oracle1" TO DB-NAME.
MOVE "nyremote" TO DB-STRING.
...
* -- Connect to the non-default database
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWORD
AT :DB-NAME USING :DB-STRING
END-EXEC.
If DB-NAME is a host variable, the DECLARE DATABASE statement is not needed. Only if DBNAME is an undeclared identifier must you execute a DECLARE DBNAME DATABASE statement before executing a CONNECT ... AT DBNAME statement.
SQL Operations. If granted the privilege, you can execute any SQL data manipulation statement at the non-default connection. For example, you might execute the following sequence of statements:
EXEC SQL AT DBNAME SELECT ... EXEC SQL AT DBNAME INSERT ... EXEC SQL AT DBNAME UPDATE ...
In the next example, DB-NAME is a host variable:
EXEC SQL AT :DB-NAME DELETE ...
If DB-NAME is a host variable, all database tables referenced by the SQL statement must be defined in DECLARE TABLE statements.
Cursor Control. Cursor control statements such as OPEN, FETCH, and CLOSE are exceptions-they never use an AT clause. If you want to associate a cursor with an explicitly identified database, use the AT clause in the DECLARE CURSOR statement, as follows:
EXEC SQL AT :DB-NAME DECLARE emp_cursor CURSOR FOR ... EXEC SQL OPEN EMP-CURSOR ... EXEC SQL FETCH EMP-CURSOR ... EXEC SQL CLOSE EMP-CURSOR END-EXEC.
If DB-NAME is a host variable, its declaration must be within the scope of all SQL statements that refer to the declared cursor. For example, if you open the cursor in one subprogram, then fetch from it in another, you must declare DB-NAME globally or pass it to each subprogram.
When opening, closing, or fetching from the cursor, you do not use the AT clause. The SQL statements are executed at the database named in the AT clause of the DECLARE CURSOR statement or at the default database if no AT clause is used in the cursor declaration.
The AT :host-variable clause allows you to change the connection associated with a cursor. However, you cannot change the association while the cursor is open. Consider the following example:
EXEC SQL AT :DB-NAME DECLARE EMP-CURSOR CURSOR FOR ...
MOVE "oracle1" TO DB-NAME.
EXEC SQL OPEN EMP-CURSOR END-EXEC.
EXEC SQL FETCH EMP-CURSOR INTO ...
MOVE "oracle2" TO DB-NAME.
* -- illegal, cursor still open
EXEC SQL OPEN EMP-CURSOR END-EXEC.
EXEC SQL FETCH EMP-CURSOR INTO ...
This is illegal because EMP-CURSOR is still open when you try to execute the second OPEN statement. Separate cursors are not maintained for different connections; there is only one EMP-CURSOR, which must be closed before it can be reopened for another connection. To debug the last example, simply close the cursor before reopening it, as follows:
* -- close cursor first
EXEC SQL CLOSE EMP-CURSOR END-EXEC.
MOVE "oracle2" TO DB-NAME.
EXEC SQL OPEN EMP-CUROR END-EXEC.
EXEC SQL FETCH EMP-CURSOR INTO ...
Dynamic SQL. Dynamic SQL statements are similar to cursor control statements in that some never use the AT clause. For dynamic SQL Method 1, you must use the AT clause if you want to execute the statement at a non-default connection. An example follows:
EXEC SQL AT :DB-NAME EXECUTE IMMEDIATE :SQL-STMT END-EXEC.
For Methods 2, 3, and 4, you use the AT clause only in the DECLARE STATEMENT statement if you want to execute the statement at a non-default connection. All other dynamic SQL statements such as PREPARE, DESCRIBE, OPEN, FETCH, and CLOSE never use the AT clause. The next example shows Method 2:
EXEC SQL AT :DB-NAME DECLARE SQL-STMT STATEMENT END-EXEC.
EXEC SQL PREPARE SQL-STMT FROM :SQL-STRING END-EXEC.
EXEC SQL EXECUTE SQL-STMT END-EXEC.
The following example shows Method 3:
EXEC SQL AT :DB-NAME DECLARE SQL-STMT STATEMENT END-EXEC.
EXEC SQL PREPARE SQL-STMT FROM :SQL-STRING END-EXEC.
EXEC SQL DECLARE EMP-CURSOR CURSOR FOR SQL-STMT END-EXEC.
EXEC SQL OPEN EMP-CURSOR ...
EXEC SQL FETCH EMP-CURSOR INTO ...
EXEC SQL CLOSE EMP-CURSOR END-EXEC.
You need not use the AT clause when connecting to a remote database unless you open two or more connections simultaneously (in which case the AT clause is needed to identify the active connection). To make the default connection to a remote database, use the following syntax:
EXEC SQL
CONNECT :USERNAME IDENTIFIED BY :PASSWORD USING :DB-STRING
END-EXEC.
You can use the AT db_name clause for multiple explicit logons, just as you would for a single explicit logon. In the following example, you connect to two non-default databases concurrently:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) .
01 PASSWORD PIC X(10) .
01 DB-STRING1 PIC X(20) .
01 DB-STRING2 PIC X(20) .
EXEC SQL END DECLARE SECTION END-EXEC.
...
MOVE "scott" TO USERNAME.
MOVE "tiger" TO PASSWORD.
MOVE "New-York" TO DB-STRING1.
MOVE "Boston" TO DB-STRING2.
* -- give each database connection a unique name
EXEC SQL DECLARE DBNAME1 DATABASE END-EXEC.
EXEC SQL DECLARE DBNAME2 DATABASE;
* -- connect to the two non-default databases
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD
AT DBNAME1 USING :DB-STRING1 END-EXEC.
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD
AT DBNAME2 USING :DB-STRING2 END-EXEC.
The undeclared identifiers DBNAME1 and DBNAME2 are used to name the default databases at the two non-default nodes so that later SQL statements can refer to the databases by name.
Alternatively, you can use a host variable in the AT clause, as the following example shows:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 USERNAME PIC X(10) .
01 PASSWORD PIC X(10) .
01 DB-NAME PIC X(10) .
01 DB-STRING PIC X(20) .
EXEC SQL END DECLARE SECTION END-EXEC.
...
MOVE "scott" TO USERNAME.
MOVE "tiger" TO PASSWORD.
PERFORM GETDB 2 TIMES.
...
* -- get next database name and Net8 string
GETDB.
DISPLAY "Database Name? ".
ACCEPT DB-NAME.
DISPLAY "Net8 String? ".
ACCEPT DB-STRING.
* -- connect to the non-default database
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD
AT :DB-NAME USING :DB-STRING
END-EXEC.
...
You can also use this method to make multiple connections to the same database, as the following example shows:
MOVE "scott" TO USERNAME.
MOVE "tiger" TO PASSWORD.
MOVE "nyremote" TO DB-STRING.
PERFORM GETDB 2 TIMES
...
GETDB.
* -- get next database name
DISPLAY 'Database Name? '.
ACCEPT DB-NAME.
* -- connect to the non-default database
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD
AT :DB-NAME USING :DB-STRING
END-EXEC.
...
You must use different database names for the connections, even if they use the same Net8 string.
Implicit logons are supported through the Oracle8 distributed database option, which does not require explicit logons. For example, a distributed query allows a single SELECT statement to access data on one or more non-default databases.
The distributed query facility depends on database links, which assign a name to a CONNECT statement rather than to the connection itself. At run time, the embedded SELECT statement is executed by the specified Oracle8 Server, which connects implicitly to the non-default database(s) to get the required data.
In the next example, you connect to a single non-default database. First, your program executes the following statement to define a database link (database links are usually established interactively by the DBA or user):
EXEC SQL CREATE DATABASE LINK db_link
CONNECT TO username IDENTIFIED BY password
USING 'nyremote'
END-EXEC.
Then, the program can query the non-default EMP table using the database link, as follows:
EXEC SQL SELECT ENAME, JOB INTO :EMP-NAME, :JOB-TITLE
FROM emp@db_link
WHERE DEPTNO = :DEPT-NUMBER
END-EXEC.
The database link is not related to the database name used in the AT clause of an embedded SQL statement. It simply tells Oracle where the non-default database is located, the path to it, and what Oracle username and password to use. The database link is stored in the data dictionary until it is explicitly dropped.
In our example, the default Oracle8 Server logs on to the non-default database via Net8 using the database link db_link. The query is submitted to the default server, but is "forwarded" to the non-default database for execution.
To make referencing the database link easier, you can create a synonym as follows (again, this is usually done interactively):
EXEC SQL CREATE SYNONYM emp FOR emp@db_link END-EXEC.
Then, your program can query the non-default EMP table, as follows:
EXEC SQL SELECT ENAME, JOB INTO :EMP-NAME, :JOB-TITLE
FROM emp
WHERE DEPTNO = :DEPT-NUMBER
END-EXEC.
This provides location transparency for emp.
In the following example, you connect to two non-default databases concurrently. First, you execute the following sequence of statements to define two database links and create two synonyms:
EXEC SQL CREATE DATABASE LINK db_link1
CONNECT TO username1 IDENTIFIED BY password1
USING 'nyremote'
END-EXEC.
EXEC SQL CREATE DATABASE LINK db_link2
CONNECT TO username2 IDENTIFIED BY password2
USING 'chiremote'
END-EXEC.
EXEC SQL CREATE SYNONYM emp FOR emp@db_link1 END-EXEC.
EXEC SQL CREATE SYNONYM dept FOR dept@db_link2 END-EXEC.
Then, your program can query the non-default EMP and DEPT tables, as follows:
EXEC SQL SELECT ENAME, JOB, SAL, LOC
FROM emp, dept
WHERE emp.DEPTNO = dept.DEPTNO AND DEPTNO = :dept_number
END-EXEC.
Oracle8 executes the query by performing a join between the non-default EMP table at db_link1 and the non-default DEPT table at db_link2.
Pro*COBOL now provides client applications with a convenient way to change a user password at runtime through a simple extension to the EXEC SQL CONNECT statement.
The syntax for the CONNECT statement now has an optional ALTER AUTHORIZATION clause. The new syntax for CONNECT is shown here:
EXEC SQL CONNECT { :user IDENTIFIED BY :oldpswd | :usr_psw }
[ [ AT { dbname | :host_variable }] USING :connect_string ]
[ ALTER AUTHORIZATION :newpswd ]
This section describes the possible outcomes of different variations of the new CONNECT statement.
If an application issues the following statement
EXEC SQL CONNECT .. /* No ALTER AUTHORIZATION clause */
it performs a normal connection attempt. The possible results include the following:
The following CONNECT statement
EXEC SQL CONNECT .. ALTER AUTHORIZATION :newpswd END-EXEC
indicates that the application wants to change the account password to the value indicated by newpswd. After the change is made, an attempt is made to connect as user/newpswd. This can have the following results:
