Pro*COBOL Precompiler Programmer's Guide
Release 8.0

A58232-01

Library

Product

Contents

Index

Prev Next

3
Writing a Pro*COBOL Program

This chapter provides the basic information you need to write a Pro*COBOL program, including:

Programming Guidelines

This section deals with embedded SQL syntax, coding conventions, and Pro*COBOL-specific features and restrictions. Topics are arranged alphabetically for quick reference.

Abbreviations

You can use the standard COBOL abbreviations, such as PIC for PICTURE IS and COMP for USAGE IS COMPUTATIONAL.

Case-insensitivity

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.

COBOL Versions

--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.

Coding Area

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.

Note: In this manual, program examples use the FORMAT=TERMINAL setting. The online sample programs use FORMAT=ANSI.

Commas

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.  

Comments

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.

Continuation Lines

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.  

Delimiters

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.

Embedded SQL Syntax

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

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. 

File Length

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

Any valid standard COBOL identifier can be used as a host variable. Variable names can be any length, but only the first 30 characters are significant. The maximum number of significant characters recognized by COBOL compilers is 30.

Hyphenated Names

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. 

Level Numbers

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.

MAXLITERAL Default

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.

Multi-Byte (NCHAR) Datatypes

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

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.

Nulls

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.

Paragraph Names

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.

REDEFINES Clause

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.

Relational Operators

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  

=

< >, !=, ^=

>

<

>=

<=

 

=, EQUAL TO

NOT=, NOT EQUAL TO

>, GREATER THAN

<, LESS THAN

>=, GREATER THAN OR EQUAL TO

<=, LESS THAN OR EQUAL TO

 

Sentence Terminator

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.

FILLER is Allowed

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).

Required Declarations and SQL Statements

Passing data between Oracle8 and your application program requires host variables and error handling. This section shows you how to meet these requirements.

Declare Section is Optional

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.

Precompiler Option 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_SECTION is implicitly set to YES. See the discussion of macro and micro options in "Macro and Micro Options" on page 7-4.

Multiple Declare Sections are allowed per precompiled unit. Furthermore, a host program can contain several independently precompiled units.

An Example

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. 

Using the INCLUDE Statement

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.

Filename Extensions

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.

Search Paths

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.

Caution

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.

Error Handling

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

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.

Declaring 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

PIC X(n)

PIC X...X VARYING

PIC X(n) VARYING

 

fixed-length string of 1-byte characters (1)

n-length string of 1-byte characters

variable-length string of 1-byte characters (1,2)

variable-length (n max.) string of 1-byte characters (2)

 

PIC N...N

PIC G...G

PIC N(n)

PIC G(n)

PIC N...N VARYING

PIC N(n) VARYING

PIC G...G VARYING

PIC G(n) 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(n) BINARY

PIC S9...9 COMP

PIC S9(n) COMP

PIC S9...9 COMP-4

PIC S9(n) COMP-4

 

integer (4,5,7)

 

COMP-1

COMP-2

 

floating-point number (5)

 

PIC S9...9V9...9 COMP-3

PIC S9(n)V9(n) COMP-3

PIC S9...9V9...9

PACKED-DECIMAL

PIC S9(n)V9(n)

PACKED-DECIMAL

 

packed-decimal (4,5)

 

PIC S9...9 COMP-5

PIC S9(n) COMP-5

 

byte-swapped integer (4,5,6,7)

 

PIC S9...9V9...9 DISPLAY

SIGN LEADING SEPARATE

PIC S9(n)V9(m) DISPLAY

SIGN LEADING SEPARATE

PIC S9...9V9...9 DISPLAY

SIGN TRAILING SEPARATE

PIC S9(n)V9(m) DISPLAY

SIGN TRAILING SEPARATE

 

display leading (9,12)

display trailing (9)

 

PIC 9...9 DISPLAY

PIC 9(n)V9(m) DISPLAY

 

unsigned display(10)

 

PIC S9...9V9...9 DISPLAY

SIGN TRAILING

PIC S9(n)V9(m) DISPLAY

SIGN TRAILING

PIC S9...9V9...9 DISPLAY

SIGN LEADING

PIC S9(n)V9(m) DISPLAY

SIGN LEADING

 

over-punch trailing (10,11

over-punch leading (10))

 

SQL-CURSOR

 

cursor variable

 

Notes:

  1. X...X and 9...9 stand for a given number (n) of Xs or 9s. For variable-length strings, n is the maximum length.
  2. The keyword VARYING assigns the VARCHAR external datatype to a character string. For more information, see "Declaring VARCHAR Variables" on page 3-36.
  3. Before using the PIC N or PIC G datatype in your Pro*COBOL source files, verify that it is supported by your COBOL compiler.
  4. Only signed numbers (PIC S...) are allowed. For floating-point numbers, however, PIC strings are not accepted.
  5. Not all COBOL compilers support all of these datatypes.
  6. With COMP or COMP-5, the number cannot have a fractional part; scaled binary numbers are not supported.
  7. The maximum value of n ranges from 9 to 18, depending upon your system.
  8. One-dimensional tables of COBOL types are also supported.
  9. Both DISPLAY and SIGN are optional.
  10. DISPLAY is optional
  11. If TRAILING is omitted, the embedded sign position is operating-system dependent.
  12. LEADING is optional.

Table 3-3 shows the compatible Oracle8 internal datatypes.

Internal Datatype     COBOL Datatype   Description  

CHAR(x) VARCHAR2(y)

 

(13)

(13)

 

PIC [X...X| N...N| G...G]

PIC [X(n)| N(n)| G(n)]

PIC [X(n)|X(n)] VARYING

PIC S9...9 COMP

PIC S9(n) COMP

PIC S9...9 BINARY

PIC S9(n) BINARY

PIC S9...9 COMP-5

PIC S9(n) COMP-5

COMP-1

COMP-2

PIC S9...9V9...9 COMP-3

PIC S9(n)V9(n) COMP-3

PIC S9...9V9...9 DISPLAY

PIC S9(n)V9(n) DISPLAY

 

character string

n-character string

variable-length string

integer

integer

integer

floating point number

packed decimal

display

 

NUMBER

NUMBER (p,s)

 

(14)

 

PIC S9...9 COMP

PIC S9(n) COMP

PIC S9...9 BINARY

PIC S9(n) BINARY

PIC S9...9 COMP-5

PIC S9(n) COMP-5

COMP-1

COMP-2

PIC S9...9V9...9 COMP-3

PIC S9(n)V9(n) COMP-3

PIC S9...9V9...9 DISPLAY

PIC S9(n)V9(n) DISPLAY

PIC [X...X| N...N| G...G]

PIC [X(n)| N(n)| G(n)]

PIC X...X VARYING

PIC X(n) VARYING

 

integer

integer

integer

floating point number

packed decimal

display

character string (15)

n-character string (15)

variable-length string

n-byte variable-length string

 

 

 

 

 

DATE

LONG

RAW

LONG RAW

ROWID

MLSLABEL

 

(16)

(13)

(17)

(18)

 

PIC X(n)

PIC X...X VARYING

 

n-byte character string

n-byte variable-length string

 

Notes:

  1. x ranges from 1 to 255, and 1 is the default. y ranges from 1 to 4000.
  2. p ranges from 2 to 38. s ranges from -84 to 127.
  3. Strings can be converted to NUMBERs only if they consist of convertible characters - 0 to 9, period (.), +, -, E, e. The NLS settings for your system might change the decimal point from a period (.) to a comma (,).
  4. When converted to a string type, the default size of a DATE depends on the NCHAR settings in effect on your system. When converted to a binary value, the length is 7 bytes.
  5. When converted to a string type, a ROWID requires from 18 to 256 bytes.
  6. Trusted Oracle only.

Example Declarations

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. 
     ... 

Initialization

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.

Restrictions

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.
 
     .... 

Referencing Host Variables

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.  

Group Items as Host Variables

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-38.

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. 

Restrictions

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.

Nested Programs

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 ...

Support for Nested Programs

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

Declaring the SQLCA

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.

Sample Nested Program

       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.

Indicator Variables

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.

Declaring Indicator 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. 
         ... 

Referencing Indicator Variables

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.

Restriction

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. 

Oracle8 Restrictions

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

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".

ANSI Requirements

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 Variables

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.

Indicator Variables with Host Group Items

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

Host tables can improve 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.

Declaring Host Tables

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.

Restrictions

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. 
     ... 

Referencing Host Tables

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-33.

Using Indicator Tables

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.

Host Group Item Containing Tables

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.

VARCHAR Variables

COBOL string datatypes are fixed length. However, Pro*COBOL lets you declare a variable-length string pseudotype called VARCHAR.

Declaring VARCHAR Variables

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.

Implicit VARCHAR Group Items

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"

Referencing VARCHAR Variables

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). 

Handling Character Data

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:

New Default for PIC X

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-32 for more details.

Effects of the PICX Option

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. Or, use

     EXEC SQL <varname> IS VARCHAR@ END-EXEC
.

for each variable.

Fixed-Length Character Variables

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.

On Input

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.

On Output

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.

Restrictions When NLS_LOCAL=YES

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.

Variable-Length Variables

VARCHAR variables handle character data based on their roles as input or output variables.

On Input

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.

On Output

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..

Connecting to Oracle

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-55.)

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

Connecting Using Net8

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 Oracle Net8 Administrator's Guide for more information about Net8.

Automatic Logons

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.

The AUTO_CONNECT Precompiler Option

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.

Concurrent Logons

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.

Figure 3-1 Connecting via Net8

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

Some Preliminaries

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

Default Databases and Connections

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.

Explicit Logons

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 "Automatic Logons" on page 3-44.

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.

Single Explicit Logons

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.

Multiple Explicit Logons

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

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.

Single Implicit Logons

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.

Multiple Implicit Logons

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.

Changing Passwords at Runtime

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 ]

Using the Connect Syntax

This section describes the possible outcomes of different variations of the new CONNECT statement.

Standard CONNECT

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:

  1. The application will connect without issue.
  2. The application will connect, but will receive a password warning. The warning indicates that the password has expired but is in a grace period which will allow logins. At this point, the user is encouraged to change the password before the account becomes locked.
  3. The application will fail to connect. Possible causes include the following:
The password is incorrect.The account has expired, and is possibly in a locked state.

Change Password on CONNECT

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:

  1. The application will connect without issue
  2. The application will fail to connect. This could be due to either of the following:

    a. Password verification failed for some reason. In this case the password remains unchanged.

    b. The account is locked. Changes to the password are not permitted.




Prev

Next
Oracle
Copyright © 1997 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index