Pro*COBOL Supplement to the Oracle Precompilers Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index



Go to previous file in sequence Go to next file in sequence

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

COBOL Versions

The Pro*COBOL Precompiler 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 Oracle 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, column 7 indicates comments or continuation lines, paragraph names begin in columns 8 through 11, and statements begin in columns 12 through 72.

If you specify FORMAT=TERMINAL, columns 1 through 6 are dropped, making column 7 the leftmost column. In this manual, program examples reflect the FORMAT=TERMINAL setting. The sample programs are in ANSI format.

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 delimiter for COBOL string constants and literals. If you specify LITDELIM=APOST, the precompiler 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, the precompiler generates the delimiter specified by the LITDELIM value.

Embedded SQL Syntax

To use a SQL statement in your host 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 Oracle7 Server SQL Reference. The precompiler translates all EXEC SQL statements into calls to the runtime library SQLLIB.

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

The Pro*COBOL Precompiler 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

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 31 characters are significant. Your compiler might require a shorter length, so check your COBOL compiler user's guide.

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 in the Declare Section, you can use level numbers 01, 02 through 49, and 77.

MAXLITERAL Default

With the MAXLITERAL option, you can specify the maximum length of string literals generated by the precompiler, so that compiler limits are not exceeded. For Pro*COBOL, the default value is 256, but you might have to specify a lower value.

For example, if your COBOL compiler cannot handle string literals longer than 132 characters, specify MAXLITERAL=132. Check your COBOL compiler user's guide.

Multi-Byte NLS Datatypes

ANSI standard National Language Support (NLS) datatypes are supported for handling multi-byte character data. For example, the PIC N clause declares variables that store fixed-length NLS strings. You can store variable-length, multi-byte NLS strings using COBOL group items consisting of a length field and a string field.

Dynamic SQL

Because dynamic SQL statements are not processed at precompile time, and since the Oracle7 Server, Release 7.3 does not itself process multi-byte NLS strings, you cannot embed multi-byte NLS strings in dynamic SQL statements.

Embedded DDL

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 REDEFINES clause to redefine elementary items, but not group items. For example, the following declaration is 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.

However, the next declaration is invalid:

     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. 

Relational Operators

COBOL relational operators differ from their SQL equivalents, as shown in Table 1 - 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
Table 1 - 1. Relational Operators



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.


Required Declarations and SQL Statements

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

The Declare Section

You must declare all program variables to be 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:

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.

Do not confuse the SQL command INCLUDE with the COBOL COPY command. If a file contains embedded SQL, you must INCLUDE it because only INCLUDEd files are precompiled.

Filename Extensions

If your system uses file extensions but you do not specify one, the Pro*COBOL Precompiler 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.

The precompiler 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, the precompiler 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, the precompiler 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. Also, if your operating system is case sensitive, be sure to specify the same upper/lowercase filename under which the file is stored.

Event and Error Handling

Pro*COBOL provides forward and backward compatibility when checking the outcome of executing SQL statements. However, there are restrictions on using SQLCA, SQLCODE, and SQLSTATE depending on the MODE and DBMS option settings. For more information, see Chapter 2 of this manual and Chapter 8 of the Programmer's Guide to the Oracle Precompilers.


Host Variables

Host variables are the key to communication between your host program and Oracle. Typically, a host program inputs data to Oracle, and Oracle outputs data to the program. Oracle 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 Oracle. COBOL datatypes must be compatible with the source/target database column. The supported COBOL datatypes are shown in Table 1 - 2.

Variable Declaration Description
PIC X...X PIC X(n) fixed-length string of 1-byte characters (1) n-length string of 1-byte characters
PIC X...X VARYING PIC X(n) VARYING variable-length string of 1-byte characters (1,2) variable-length (n max.) string of 1-byte characters (2)
PIC N...N PIC N(n) fixed-length string of 2-byte NLS characters (1,3) n-length string of 2-byte NLS characters (3)
PIC S9...9 BINARY PIC S9(n) BINARY integer (4,5,7)
PIC S9...9 COMP PIC S9(n) COMP
PIC S9...9 COMP-5 PIC S9(n) COMP-5 byte-swapped integer (4,5,6,7)
COMP-1 COMP-2 floating-point number (4,5)
PIC S9...9V9...9 COMP-3 PIC S9(n)V9(n) COMP-3 packed decimal (4,5) integer (if precision, which is optional, is omitted)
PIC S9...9V9...9 DISPLAY SIGN LEADING SEPARATE PIC S9(n)V9(n) DISPLAY SIGN LEADING SEPARATE display
SQL-CURSOR cursor variable
Table 1 - 2. Host Variable Declarations



Notes:

Table 1 - 3 shows the compatible Oracle internal datatypes.

Internal Type COBOL Type Description
CHAR(x) (1) VARCHAR2(y) (1) PIC [X...X|PIC N...N] character string
PIC [X(n)|PIC N(n)] n-character string
PIC [X(n)|X(n)] VARYING variable-length string
PIC S9...9 COMP PIC S9(n) COMP integer
PIC S9...9 BINARY PIC S9(n) BINARY integer
PIC S9...9 COMP-5 PIC S9(n) COMP-5 integer
COMP-1 COMP-2 floating point number
PIC S9...9V9...9 COMP-3 PIC S9(n)V9(n) COMP-3 packed decimal or integer
NUMBER NUMBER (p,s) (2) PIC S9...9 COMP PIC S9(n) COMP integer
PIC S9...9 BINARY PIC S9(n) BINARY integer
PIC S9...9 COMP-5 PIC S9(n) COMP-5 integer
COMP-1 COMP-2 floating point number
PIC S9...9V9...9 COMP-3 PIC S9(n)V9(n) COMP-3 packed decimal
PIC [X...X|PIC N...N] character string (3)
PIC [X(n)|PIC N(n)] n-character string (3)
PIC X...X VARYING variable-length string
PICX(n) VARYING n-byte variable-length string
DATE (4) LONG RAW (1) LONG RAW ROWID (5) MLSLABEL (6) PIC X(n) PIC X...X VARYING n-byte character string n-byte variable-length string
DISPLAY PIC S9...9V9...9 DISPLAY SIGN LEADING SEPARATE PIC S9(n)V9(n) DISPLAY SIGN LEADING SEPARATE display
CURSOR SQL-CURSOR cursor variable
Table 1 - 3. Compatible Oracle Internal Datatypes



Notes:

Example Declarations

In the following example, you declare several host variables for use later in your Pro*COBOL program:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         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. 
     EXEC SQL END DECLARE SECTION END-EXEC. 

You can also declare one-dimensional tables of simple COBOL types, as the next example shows:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         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. 
     EXEC SQL END DECLARE SECTION END-EXEC. 

Initialization

You can initialize host variables in the Declare Section using the VALUE clause, as shown in the following example:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  USERNAME    PIC X(10) VALUE "SCOTT". 
         01  MAX-SALARY  PIC S9(4) COMP VALUE 5000. 
     EXEC SQL END DECLARE SECTION END-EXEC. 

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 declare unsigned numeric character variables (PIC 9) or alphabetic character (PIC A) variables in the Declare Section. For example, the following declarations are invalid:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  ID-NUMBER   PIC 9(4). 
         01  FIRST-NAME  PIC A(10). 
     EXEC SQL END DECLARE SECTION END-EXEC. 

Nor can you define edited data items in the Declare Section. Therefore, the following declarations are invalid:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  AMOUNT-OF-CHECK  PIC ****9V99. 
         01  BIRTH-DATE       PIC 99/99/99. 
     EXEC SQL END DECLARE SECTION END-EXEC. 

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

Elementary Item versus Group Item

You cannot reference group items in SQL statements (see "Referencing VARCHAR Variables" [*] for an exception). However, you can reference elementary items wherever host variables are allowed. For example, given the declaration

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
        ... 
        01  DEPARTURE. 
            05  HOUR    PIC X(2). 
            05  MINUTE  PIC X(2). 
     EXEC SQL END DECLARE SECTION END-EXEC. 

the following statement is invalid:

     EXEC SQL SELECT DHOUR, DMINUTE 
         INTO :DEPARTURE FROM SCHEDULE 
         WHERE ... 
     END-EXEC.

However, the following statement is valid:

     EXEC SQL SELECT DHOUR, DMINUTE 
         INTO :HOUR, :MINUTE FROM SCHEDULE 
         WHERE ... 
     END-EXEC.

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, given the following declarations:

     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. 

you must qualify HOUR and MINUTE, as in

     EXEC SQL SELECT AHOUR, AMINUTE 
        INTO :ARRIVAL.HOUR, :ARRIVAL.MINUTE 
        FROM SCHEDULE 
        WHERE ... 

This works for items nested deeper than two levels, provided you fully qualify them starting at the highest level -- even if that would be unnecessary in COBOL. For example, given the declarations

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
        01  TIMETABLE. 
            05  DEPARTURE. 
                10  HOUR    PIC X(2). 
                10  MINUTE  PIC X(2). 
            05  ARRIVAL. 
                10  HOUR    PIC X(2). 
                10  MINUTE  PIC X(2). 
     EXEC SQL END DECLARE SECTION END-EXEC. 

you must fully qualify HOUR and MINUTE, as in

     EXEC SQL SELECT AHOUR, AMINUTE 
        INTO :TIMETABLE.ARRIVAL.HOUR, :TIMETABLE.ARRIVAL.MINUTE 
        FROM SCHEDULE 
        WHERE ... 
     END-EXEC.

even though in COBOL, the following would suffice:

     DISPLAY HOUR OF ARRIVAL, ":", MINUTE OF ARRIVAL. 

Restrictions

A host variable cannot substitute for a column, table, or other Oracle object in a SQL statement and must not be an Oracle reserved word. See Appendix B of the Programmer's Guide to the Oracle Precompilers for a list of Oracle reserved words and keywords.


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 in the Declare Section as a 2-byte integer (PIC S9(4) COMP) and must not be an Oracle reserved word. In the following example, you declare an indicator variable named COMM-IND (the name is arbitrary):

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  EMP-NAME    PIC X(10) VALUE SPACE. 
         01  COMMISSION  PIC S9(5)V99 COMP-3. 
         01  COMM-IND    PIC S9(4) COMP. 
     EXEC SQL END DECLARE SECTION END-EXEC. 

You can define an indicator variable anywhere in the Declare Section. It need not follow its associated host variable.

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

Oracle Restrictions

When DBMS=V6, Oracle 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, if you SELECT or FETCH a null into a host variable that has no indicator, Oracle issues the following error message:

ORA-01405: fetched column value is NULL

When precompiling with MODE=ORACLE and DBMS=V7 specified, you can disable the ORA-01405 message by also specifying UNSAFE_NULL=YES on the command line. For more information, see the Programmer's Guide to the Oracle Precompilers.

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, Oracle 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 3 of the Programmer's Guide to the Oracle Precompilers.

Indicator Variables for Multi-Byte NLS Variables

Indicator variables for multi-byte NLS 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 2-byte characters instead of 1-byte characters.


Host Tables

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.

Declaring Host Tables

You declare and dimension host tables in the Declare Section. In the following example, three host tables are declared, each dimensioned with 50 elements:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         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. 
     EXEC SQL END DECLARE SECTION END-EXEC. 

You can use the INDEXED BY phrase in the OCCURS clause to specify an index, as the next example shows:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  EMP-TABLES. 
             05  EMP-NUMBER  PIC X(10) OCCURS 50 TIMES 
                                       INDEXED BY EMP-INDX. 
             ... 
     EXEC SQL END DECLARE SECTION END-EXEC. 

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:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  NATION.
             05  STATE                OCCURS 50 TIMES. 
                 10  STATE-NAME       PIC X(25).
                 10  COUNTY           OCCURS 25 TIMES.
                     15  COUNTY-NAME  PIX X(25).
     EXEC SQL END DECLARE SECTION END-EXEC. 

Variable-length host tables are not allowed either. For example, the following declaration of EMP-REC is invalid:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  EMP-FILE. 
             05  REC-COUNT  PIC S9(3) COMP. 
             05  EMP-REC    OCCURS 0 TO 250 TIMES 
                            DEPENDING ON REC-COUNT. 
     EXEC SQL END DECLARE SECTION END-EXEC.  

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 the Pro*COBOL Precompiler 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 unsubscripted table names in your SQL statement. Oracle treats a SQL statement containing host tables of dimension n like the same statement executed n times with n different scalar host variables. For more information, see Chapter 8 of the Programmer's Guide to the Oracle Precompilers.

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.


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:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         ... 
         01  ENAME  PIC X(15) VARYING. 
         ... 
     EXEC SQL END DECLARE SECTION END-EXEC. 

The VARYING phrase in the preceding example is used in PERFORM and SEARCH statements to increment subscripts and indexes. Do not confuse this with the COBOL VARYING clause.

You can define a VARCHAR variable only in the Declare Section. Think of it as an extended COBOL datatype or predeclared group item. For example, the precompiler 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.

Note: The keyword VARYING cannot be used when declaring multi-byte NLS character data.

The advantage of using VARCHAR variables is that you can explicitly set and reference the length field. With input host variables, Oracle reads the value of the length field and uses that many characters of the string field. With output host variables, Oracle sets the length value to the length of the character string stored in the string field.

Implicit VARCHAR Group Items

The Pro*COBOL Precompiler implicitly recognizes two group-item formats as VARCHAR host variables with VARCHAR=YES specified on the command line. For variable-length single-byte character types, use the following structure (length expressed in single-byte characters):

 01  DATA-NAME-1.
     49  DATA-NAME-2 PIC S9(4) COMP.
     49  DATA-NAME-3 PIC X(<length>).

For variable-length multi-byte NLS character types, use this structure (length expressed in double-byte characters):

 01  DATA-NAME-1.
     49  DATA-NAME-2 PIC S9(4) COMP.
     49  DATA-NAME-3 PIC N(<length>).

The elementary items in these group-item structures must be declared as level 49 for the Pro*COBOL Precompiler to recognize them as VARCHAR host variables.

Note: Tables of multi-byte NLS VARCHAR variables are not supported.

For more information about the Pro*COBOL VARCHAR option, see Chapter 6 of the Programmer's Guide to the Oracle Precompilers.

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.

Remember, except for VARCHAR variables, you cannot reference group items in SQL statements.

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 the Pro*COBOL Precompiler handles character host variables. There are two types of single-byte character host variables and one type of multi-byte NLS character host variables:

Attention: Before using multi-byte NLS datatypes, verify that the PIC N datatype is supported by your COBOL compiler.

Effects of the MODE Option

The MODE option determines how the Pro*COBOL Precompiler treats data in character strings. The MODE option allows your program to use ANSI fixed-length strings or to maintain compatibility with previous versions of the Oracle Server and the Pro*COBOL Precompiler.

With respect to character handling, MODE={ANSI14|ANSI13} is equivalent to MODE=ORACLE. The MODE option affects character data on input (from host variables to Oracle) and on output (from Oracle to host variables).

Note: The MODE option does not affect how Pro*COBOL handles PIC X(n) VARYING variables.

Single-Byte Character Variables

Single-byte character variables are declared using the PIC X(n) and PIC X VARYING datatypes. These types of variables handle character data based on their roles as input or output variables.

On Input

When MODE=ORACLE, the program interface strips trailing blanks before sending the value to the database. If you insert into a fixed-length CHAR column, Oracle re-appends trailing blanks up to the length of the database column. However, if you insert into a variable-length VARCHAR2 column, Oracle never appends blanks.

When MODE=ANSI, 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 MODE=ORACLE 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 MODE=ANSI 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 MODE option has no effect on output to character variables. When you use a PIC X(n) variable as an output host variable, Oracle 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.

Multi-byte NLS Character Variables

Specify MODE=ANSI when using fixed-length NLS host variables (declared with the PIC N clause) are assigned to external datatype 96 (ANSI fixed CHAR).

Warning: Oracle7 SQL functions should not be used on columns or host variables that store multi-byte NLS data. Multi-byte NLS features are supported by the SQLLIB runtime library, but they are not supported by the Oracle7 Server.

On Input

Input host variables are stripped of any trailing double-byte spaces. If the multi-byte NLS input variable contains only spaces, one double-byte space is left in the buffer to act as a sentinel.

To declare a multi-byte NLS character type in Pro*COBOL, use the following PICTURE clause:

 WORKING-STORAGE SECTION. 
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         ...
         01  <nls_variable> PIC N(<length>).
         ... 
     EXEC SQL END DECLARE SECTION END-EXEC. 

where nls_variable is a valid COBOL variable name and length is the maximum number of multi-byte NLS characters the string can hold.

The example in the "Single-Byte Character Variables" section (page 1 - 24) could be rewritten to accept double-byte NLS characters using the PIC N datatype shown in the following example:

 EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
     01  EMPLOYEES. 
         05  EMP-NAME     PIC N(10). 
         05  DEPT-NUMBER  PIC S9(4) VALUE 20 COMP. 
         05  EMP-NUMBER   PIC S9(9) VALUE 9999 COMP. 
         05  JOB-NAME     PIC N(8). 
 EXEC SQL END DECLARE SECTION END-EXEC. 

The EMP-NAME variable now accepts up to ten double-byte characters, while JOB-NAME accepts eight double-byte characters.

On Output

When you use a multi-byte NLS character variable as an output host variable, Oracle blank-pads it with double-byte spaces. This character string can be used without change as input to another SQL statement, providing it is mapped to another multi-byte NLS variable.

In the example for single-byte character data [*], the program fetches the string "MILLER" from the database. If using multi-byte NLS characters, EMP-NAME contains the value "MILLER" with each character allocated two bytes. The string is blank-padded with four double-byte spaces.

Restrictions

Tables Disallowed. Host variables declared using the PIC N datatype must not be tables.

No Odd-Byte Widths. Oracle CHAR columns should not be used to store multi-byte NLS characters. A runtime error is generated if data with an odd number of bytes is FETCHed from a single-byte column into a multi-byte NLS (PIC N) host variable.

No Host Variable Equivalencing. Multi-byte NLS character variables cannot be equivalenced using an EXEC SQL VAR statement.

No Dynamic SQL. Dynamic SQL is not available for NLS multi-byte character string host variables in Pro*COBOL.

VARCHAR 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, Oracle 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, Oracle 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 Oracle 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 NLS data are not padded at all. The length of the buffer is set to the length in characters, not bytes.


The Oracle Datatypes

Oracle recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle stores data in database columns. Oracle also uses internal datatypes to represent database pseudocolumns. An external datatype specifies how data is stored in a host variable. For descriptions of the Oracle datatypes, see Chapter 3 of the Programmer's Guide to the Oracle Precompilers.

Internal Datatypes

For values stored in database columns, Oracle uses the following internal datatypes:

Name Code Description
CHAR 96 <= 255-byte, fixed-length string
DATE 12 7-byte, fixed-length date/time value
LONG 8 <= 2147483647-byte, variable-length string
LONG RAW 24 <= 2147483647-byte, variable-length binary data
MLSLABEL 105 <= 5-byte, variable-length binary label
NUMBER 2 fixed or floating point number
RAW 23 <= 255-byte, variable-length binary data
ROWID 11 fixed-length binary value
VARCHAR2 1 <= 2000-byte, variable-length string
Table 1 - 4. Internal Datatypes



These internal datatypes can be quite different from COBOL datatypes. For example, COBOL has no equivalent to the NUMBER datatype, which was specially designed for portability and high precision.

External Datatypes

As the table below shows, the external datatypes include all the internal datatypes plus several datatypes found in other supported host languages. For example, the STRING external datatype refers to a C null-terminated string. You use the datatype names in datatype equivalencing, and you use the datatype codes in dynamic SQL Method 4.

Name Code Description
CHAR 1 96 <= 65535-byte, variable-length character string (1) <= 65535-byte, fixed-length character string (1)
CHARF 96 <= 65535-byte, fixed-length character string
CHARZ 97 <= 65535-byte, fixed-length, null-terminated string (2)
DATE 12 7-byte, fixed-length date/time value
DECIMAL 7 COBOL packed decimal
DISPLAY 91 COBOL numeric character string
FLOAT 4 4-byte or 8-byte floating-point number
INTEGER 3 2-byte or 4-byte signed integer
LONG 8 <= 2147483647-byte, fixed-length string
LONG RAW 24 <= 217483647-byte, fixed-length binary data
LONG VARCHAR 94 <= 217483643-byte, variable-length string
LONG VARRAW 95 <= 217483643-byte, variable-length binary data
MLSLABEL 106 2..5-byte, variable-length binary data
NUMBER 2 integer or floating-point number
RAW 23 <= 65535-byte, fixed-length binary data (2)
ROWID 11 (typically) 13-byte, fixed-length binary value
STRING 5 <= 65535-byte, null-terminated character string (2)
UNSIGNED 68 2-byte or 4-byte unsigned integer
VARCHAR 9 <= 65533-byte, variable-length character string
VARCHAR2 1 <= 65535-byte, variable-length character string (2)
VARNUM 6 variable-length binary number
VARRAW 15 <= 65533-byte, variable-length binary data
Table 1 - 5. External Datatypes

Notes:


Datatype Conversion

At precompile time, an external datatype is assigned to each host variable in the Declare Section. For example, the precompiler assigns the INTEGER external datatype to host variables of type PIC S9(n) COMP. At run time, the datatype code of every host variable used in a SQL statement is passed to Oracle. Oracle uses the codes to convert between internal and external datatypes.

Before assigning a SELECTed column value to an output host variable, Oracle must convert the internal datatype of the source column to the datatype of the host variable. Likewise, before assigning or comparing the value of an input host variable to a column, Oracle must convert the external datatype of the host variable to the internal datatype of the target column.

Conversions between internal and external datatypes follow the usual data conversion rules. For example, you can convert a CHAR value of "1234" to a PIC S9(4) COMP value. You cannot, however, convert a CHAR value of "65543" (number too large) or "10F" (number not decimal) to a PIC S9(4) COMP value. Likewise, you cannot convert a PIC X(n) value that contains alphabetic characters to a NUMBER value.

For more information about datatype conversion, see Chapter 3 of the Programmer's Guide to the Oracle Precompilers.


Datatype Equivalencing

Datatype equivalencing lets you control the way Oracle interprets input data and the way Oracle formats output data. You can equivalence supported COBOL datatypes to Oracle external datatypes on a variable-by-variable basis.

Attention: Multi-byte NLS character variables cannot be equivalenced using the EXEC SQL VAR statement.

Host Variable Equivalencing

By default, the Pro*COBOL Precompiler assigns a specific external datatype to every host variable. The default assignments are shown in Table 1 - 6. For more information about datatype equivalencing, see Chapter 3 in the Programmer's Guide to the Oracle Precompilers.

Host Type External Type Code
PIC X...X PIC X(n) VARCHAR2 CHARF 1 (when MODE != ANSI) 96 (when MODE=ANSI)
PIC N...N PIC N(n) CHARF 96 (when MODE=ANSI)
PIC X...X VARYING PIC X(n) VARYING VARCHAR 9
PIC S9...9 COMP PIC S9(n) COMP PIC S9...9 COMP-5 PIC S9(n) COMP-5 INTEGER 3
COMP-1 COMP-2 FLOAT 4
PIC S9...9V9...9 COMP-3 PIC S9(n)V9(n) COMP-3 DECIMAL 7
PIC S9...9V9...9 DISPLAY SIGN LEADING SEPARATE PIC S9(n)V9(n) DISPLAY SIGN LEADING SEPARATE DISPLAY 91
Table 1 - 6. Host Variable Equivalencing



With the VAR statement, you can override the default assignments by equivalencing host variables to Oracle external datatypes in the Declare Section. The syntax you use is

     EXEC SQL 
         VAR <host_variable>
         IS <ext_type_name> [({<length> | <precision>,<scale>})]
     END-EXEC.

where host_variable is an input or output host variable (or host table) declared earlier in the Declare Section, ext_type_name is the name of a valid external datatype, and length is an integer literal specifying a valid length in bytes.

When ext_type_name is FLOAT, use length; when ext_type_name is DECIMAL, you must specify precision and scale instead of length.

Host variable equivalencing is useful in several ways. For example, you can use it when you want Oracle to store but not interpret data. Suppose you want to store a host table of 4-byte integers in a RAW database column. Simply equivalence the host table to the RAW external datatype, as follows:

 WORKING-STORAGE SECTION. 
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  EMP-TABLES. 
             05  EMP-NUMBER  PIC S9(9) COMP OCCURS 50 TIMES. 
             ... 
*    Reset default datatype (INTEGER) to RAW.
     EXEC SQL VAR EMP-NUMBER IS RAW (200) END-EXEC.
     EXEC SQL END DECLARE SECTION END-EXEC.

With host tables, the length you specify must match the buffer size required to hold the table. In the last example, you specified a length of 200, which is the buffer size needed to hold 50 4-byte integers.


Embedding PL/SQL

The Pro*COBOL Precompiler treats a PL/SQL block like a single embedded SQL statement. So, you can place a PL/SQL block anywhere in a host program that you can place a SQL statement.

To embed a PL/SQL block in your host program, declare the variables to be shared with PL/SQL and bracket the PL/SQL block with the EXEC SQL EXECUTE and END-EXEC keywords.

Host Variables

Inside a PL/SQL block, host variables are global to the entire block and can be used anywhere a PL/SQL variable is allowed. Like host variables in a SQL statement, host variables in a PL/SQL block must be prefixed with a colon. The colon sets host variables apart from PL/SQL variables and database objects.

VARCHAR Variables

When entering a PL/SQL block, Oracle automatically checks the length fields of VARCHAR host variables, so you must set the length fields before the block is entered. For input variables, set the length field to the length of the value stored in the string field. For output variables, set the length field to the maximum length allowed by the string field.

Multi-Byte NLS Features

Multi-byte NLS features are not supported within a PL/SQL block. These features include N-quoted character literals (see Chapter 3 of the Programmer's Guide to the Oracle Precompilers) and fixed-length character variables.

Indicator Variables

In a PL/SQL block, you cannot refer to an indicator variable by itself; it must be appended to its associated host variable. Also, if you refer to a host variable with its indicator variable, you must always refer to it that way in the same block.

Handling Nulls

When entering a block, if an indicator variable has a value of -1, PL/SQL automatically assigns a null to the host variable. When exiting the block, if a host variable is null, PL/SQL automatically assigns a value of -1 to the indicator variable.

Handling Truncated Values

PL/SQL does not raise an exception when a truncated string value is assigned to a host variable. However, if you use an indicator variable, PL/SQL sets it to the original length of the string.

SQLCHECK

You must specify SQLCHECK=SEMANTICS when precompiling a program with an embedded PL/SQL block. You must also use the USERID option. For more information, see Chapter 6 of the Programmer's Guide to the Oracle Precompilers.


Cursor Variables

Starting with Release 1.7 of the Pro*COBOL Precompiler, you can use cursor variables in your Pro*COBOL programs to process multi-row queries using static embedded SQL. A cursor variable identifies a cursor reference that is defined and opened on the Oracle7 Server, Release 7.2 or later, using PL/SQL. See the PL/SQL User's Guide and Reference for complete information about cursor variables.

The advantages of cursor variables are

Declaring a Cursor Variable

You declare a Pro*COBOL cursor variable using the SQL-CURSOR pseudotype. For example:

 WORKING-STORAGE SECTION.
     ...
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
         ...
         01  CUR-VAR  SQL-CURSOR.
         ...
     EXEC SQL END DECLARE SECTION END-EXEC.

A SQL-CURSOR variable is implemented as a COBOL group item in the code that Pro*COBOL generates. A cursor variable is just like any other Pro*COBOL host variable.

Allocating a Cursor Variable

Before you can OPEN or FETCH from a cursor variable, you must initialize it using the Pro*COBOL ALLOCATE command. For example, to initialize the cursor variable CUR-VAR that was declared in the previous section, write the following statement:

     EXEC SQL 
         ALLOCATE :CUR-VAR 
     END-EXEC.

Allocating a cursor variable does not require a call to the server, either at precompile time or at run time.

Warning: Allocating a cursor variable does cause heap memory to be used. For this reason, avoid allocating a cursor variable in a program loop.

Opening a Cursor Variable

You must use an embedded anonymous PL/SQL block to open a cursor variable on the Oracle Server. The anonymous PL/SQL block may open the cursor either indirectly by calling a PL/SQL stored procedure that opens the cursor (and defines it in the same statement) or directly from the Pro*COBOL program.

Opening Indirectly through a Stored PL/SQL Procedure

Consider the following PL/SQL package stored in the database:

CREATE PACKAGE demo_cur_pkg AS
    TYPE EmpName IS RECORD (name VARCHAR2(10));
    TYPE cur_type IS REF CURSOR RETURN EmpName;
    PROCEDURE open_emp_cur (
               curs     IN OUT curtype,
               dept_num IN     number);
END;
CREATE PACKAGE BODY demo_cur_pkg AS
    CREATE PROCEDURE open_emp_cur (
               curs     IN OUT curtype,
               dept_num IN     number) IS
    BEGIN
        OPEN curs FOR
            SELECT ename FROM emp
                WHERE deptno = dept_num
                ORDER BY ename ASC;
    END;
END;

After this package has been stored, you can open the cursor curs by calling the open_emp_cur stored procedure from your Pro*COBOL program, and FETCH from the cursor variable EMP-CURSOR in the program. For example:

 WORKING-STORAGE SECTION.
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
         01  EMP-CURSOR    SQL-CURSOR.
         01  DEPT-NUM      PIC S9(4).
         01  EMP-NAME      PIC X(10) VARYING.
     EXEC SQL END DECLARE SECTION END-EXEC.
     ...
 PROCEDURE DIVISION.
     ...
*    Allocate the cursor variable.
     EXEC SQL 
         ALLOCATE :EMP-CURSOR 
     END-EXEC.
     ...
     MOVE 30 TO DEPT_NUM.
*    Open the cursor on the Oracle Server.
     EXEC SQL EXECUTE
         BEGIN
             demo_cur_pkg.open_emp_cur(:EMP-CURSOR, :DEPT-NUM);
         END;
     END-EXEC.
     EXEC SQL 
         WHENEVER NOT FOUND DO PERFROM SIGN-OFF
     END-EXEC.
 FETCH-LOOP.
     EXEC SQL 
         FETCH :EMP-CURSOR INTO :EMP-NAME 
     END-EXEC.
     DISPLAY "Employee Name: ",:EMP-NAME.
     GO TO FETCH-LOOP.
     ...

Opening Directly from Your Pro*COBOL Application

To open a cursor using a PL/SQL anonymous block in a Pro*COBOL program, define the cursor in the anonymous block. Consider the following example:

 PROCEDURE DIVISION.
     ...
     EXEC SQL EXECUTE
         BEGIN
             OPEN :EMP-CURSOR FOR SELECT ENAME FROM EMP
                 WHERE DEPTNO = :DEPT-NUM;
         END;
     END-EXEC.
     ...

Return Types

When you define a reference cursor (REF CURSOR) in a PL/SQL stored procedure, you must declare the type that the cursor returns. The return types allowed for reference cursors are described in the PL/SQL User's Guide and Reference.

Fetching from a Cursor Variable

Use the embedded SQL FETCH .... INTO command to retrieve the rows SELECTed when you opened the cursor variable. For example:

     EXEC SQL 
         FETCH :EMP-CURSOR INTO :EMP-INFO:EMP-INFO-IND
     END-EXEC.

Before you can FETCH from a cursor variable, the variable must be initialized and opened. You cannot FETCH from an unopened cursor variable.

Closing a Cursor Variable

Use the embedded SQL CLOSE command to close a cursor variable. For example:

 WORKING-STORAGE SECTION.
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
*    Declare the cursor variable.
         01  CUR-VAR       SQL-CURSOR.
         ...
     EXEC SQL END DECLARE SECTION END-EXEC.

 PROCEDURE DIVISION.
*    Allocate and open the cursor variable, then
*    Fetch one or more rows.
     ...
*    Close the cursor variable.
     EXEC SQL
         CLOSE :CUR-VAR
     END-EXEC.

Restrictions

The following restrictions apply to the use of cursor variables:

Error Conditions

Do not perform any of the following operations:

These operations on cursor variables result in errors.

Sample Programs

The following sample programs -- a SQL script (SAMPLE11.SQL) and a Pro*COBOL program (SAMPLE11.PCO) -- demonstrate how you can use cursor variables in Pro*COBOL.

SAMPLE11.SQL

Following is the PL/SQL source code for a creating a package that declares and opens a cursor variable:

CONNECT SCOTT/TIGER
CREATE OR REPLACE PACKAGE emp_demo_pkg AS
    TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE;
    PROCEDURE open_cur (
        cursor   IN OUT emp_cur_type,
        dept_num IN     number);
END emp_demo_pkg;
/  
CREATE OR REPLACE PACKAGE BODY emp_demo_pkg AS
    PROCEDURE open_cur (
        cursor   IN OUT emp_cur_type, 
        dept_num IN     number) IS
    BEGIN 
        OPEN cursor FOR SELECT * FROM emp
        WHERE deptno = dept_num
        ORDER BY ename ASC;
    END;
END emp_demo_pkg;
/

SAMPLE11.PCO

Following is a Pro*COBOL sample program that uses the cursor declared in the SAMPLE11.SQL example to fetch employee names, salaries, and commissions from the EMP table.

 IDENTIFICATION DIVISION.
 PROGRAM-ID. CURSOR-VARIABLES.
 ENVIRONMENT DIVISION.
 DATA DIVISION.
 WORKING-STORAGE SECTION.
     EXEC ORACLE OPTION (SQLCHECK=FULL) END-EXEC.
     EXEC SQL BEGIN DECLARE SECTION END-EXEC.
         01  USERNAME          PIC X(15) VARYING.
         01  PASSWD            PIC X(15) VARYING.
         01  HOST              PIC X(15) VARYING.
*    Declare the cursor variable.
         01  EMP-CUR           SQL-CURSOR.
         01  EMP-INFO.
             05  EMP-NUM       PIC S9(4) COMP.
             05  EMP-NAM       PIC X(10) VARYING.
             05  EMP-JOB       PIC X(10) VARYING.
             05  EMP-MGR       PIC S9(4) COMP.
             05  EMP-DAT       PIC X(10) VARYING.
             05  EMP-SAL       PIC S9(6)V99 
                               DISPLAY SIGN LEADING SEPARATE.
             05  EMP-COM       PIC S9(6)V99 
                               DISPLAY SIGN LEADING SEPARATE.
             05  EMP-DEP       PIC S9(4) COMP.
         01  EMP-INFO-IND.
             05  EMP-NUM-IND   PIC S9(2) COMP.
             05  EMP-NAM-IND   PIC S9(2) COMP.
             05  EMP-JOB-IND   PIC S9(2) COMP.
             05  EMP-MGR-IND   PIC S9(2) COMP.
             05  EMP-DAT-IND   PIC S9(2) COMP.
             05  EMP-SAL-IND   PIC S9(2) COMP.
             05  EMP-COM-IND   PIC S9(2) COMP.
             05  EMP-DEP-IND   PIC S9(2) COMP.

     EXEC SQL END DECLARE SECTION END-EXEC.

     EXEC SQL INCLUDE SQLCA END-EXEC.
         01  DISPLAY-VARIABLES.
             05  D-DEP-NUM     PIC Z(3)9.
             05  D-EMP-NAM     PIC X(10).
             05  D-EMP-SAL     PIC Z(4)9.99.
             05  D-EMP-COM     PIC Z(4)9.99.

 PROCEDURE DIVISION.
 BEGIN-PGM.
     EXEC SQL
         WHENEVER SQLERROR DO PERFORM SQL-ERROR
     END-EXEC.
     PERFORM LOGON.

*    Initialize the cursor variable.
     EXEC SQL
         ALLOCATE :EMP-CUR
     END-EXEC.
     DISPLAY "Enter department number (0 to exit):  " 
         WITH NO ADVANCING.
     ACCEPT EMP-DEP.
     IF EMP-DEP <= 0
         PERFORM SIGN-OFF
     END-IF.
     MOVE EMP-DEP TO D-DEP-NUM.

*    Open the cursor by calling a PL/SQL stored procedure.
     EXEC SQL EXECUTE
         BEGIN
             emp_demo_pkg.open_cur(:EMP-CUR, :EMP-DEP);
         END;
     END-EXEC.
     DISPLAY " ".
     DISPLAY "For department ", D-DEP-NUM, ":".
     DISPLAY " ".
     DISPLAY "EMPLOYEE   SALARY     COMMISSION".
     DISPLAY "---------- ---------- ----------".

 FETCH-LOOP.
     EXEC SQL
         WHENEVER NOT FOUND DO PERFORM SIGN-OFF
     END-EXEC.
     MOVE SPACES TO EMP-NAM-ARR.
*    Fetch data from the cursor into the host variables.
     EXEC SQL FETCH :EMP-CUR
         INTO :EMP-NUM:EMP-NUM-IND,
              :EMP-NAM:EMP-NAM-IND,
              :EMP-JOB:EMP-JOB-IND,
              :EMP-MGR:EMP-MGR-IND,
              :EMP-DAT:EMP-DAT-IND,
              :EMP-SAL:EMP-SAL-IND,
              :EMP-COM:EMP-COM-IND,
              :EMP-DEP:EMP-DEP-IND
     END-EXEC.
     MOVE EMP-SAL TO D-EMP-SAL.
     MOVE EMP-COM TO D-EMP-COM.
*    Check for commission and print results.
     IF EMP-COM-IND = 0
         DISPLAY EMP-NAM-ARR, "   ", D-EMP-SAL, 
                 "   ", D-EMP-COM
     ELSE
         DISPLAY EMP-NAM-ARR, "   ", D-EMP-SAL, 
                 "        N/A"
     END-IF.
     GO TO FETCH-LOOP. 
      
 LOGON.
     MOVE "SCOTT" TO USERNAME-ARR.
     MOVE 5 TO USERNAME-LEN.
     MOVE "TIGER" TO PASSWD-ARR.
     MOVE 5 TO PASSWD-LEN.
     MOVE "INST1_ALIAS" TO HOST-ARR.
     MOVE 11 TO HOST-LEN.
     EXEC SQL
         CONNECT :USERNAME IDENTIFIED BY :PASSWD
     END-EXEC.
     DISPLAY " ".
     DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR.

 SIGN-OFF.
*    Close the cursor variable.
     EXEC SQL
         CLOSE :EMP-CUR
     END-EXEC.
     DISPLAY " ".
     DISPLAY "HAVE A GOOD DAY.".
     DISPLAY " ".
     EXEC SQL
         COMMIT 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.


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

To supply the Oracle username and password separately, you define two host variables in the Declare Section 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 hardcode 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 hardcode 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. 

Automatic Logons

You can automatically log on to the Oracle using the following 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.

Consider the following example:

 WORKING-STORAGE SECTION.
     ...
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         ... 
         01  ORACLE-ID  PIC X(1) VALUE "/". 
         ...
     EXEC SQL END DECLARE SECTION END-EXEC. 
 PROCEDURE DIVISION.
     ...
     EXEC SQL 
         CONNECT :ORACLE-ID 
     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 the precompiler. However, the string cannot contain trailing blanks. For example, the following CONNECT statement will fail:

 WORKING-STORAGE SECTION.
     ...
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         ... 
         01  ORACLE-ID  PIC X(5) VALUE "/    ". 
     EXEC SQL END DECLARE SECTION END-EXEC. 
 PROCEDURE DIVISION.
     EXEC SQL 
         CONNECT :ORACLE-ID 
     END-EXEC. 
     ...

For more information about operating system authentication, see the Oracle7 Server Administrator's Guide.

Concurrent Logons

Your application can use SQL*Net to access any combination of remote and local databases concurrently or make multiple connections to the same database. In the following example, you connect to two non-default databases concurrently:

 WORKING-STORAGE SECTION. 
     ... 
*    Declare needed host variables. 
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         01  USERNAME    PIC X(5)  VALUE "SCOTT". 
         01  PASSWD      PIC X(5)  VALUE "TIGER". 
         01  DB-STRING1  PIC X(12) VALUE "NEWYORK". 
         01  DB-STRING2  PIC X(12) VALUE "BOSTON". 
     EXEC SQL END DECLARE SECTION END-EXEC. 
     ... 
 PROCEDURE DIVISION. 
     ... 
*    Give each database connection a unique name. 
     EXEC SQL DECLARE DB-NAME1 DATABASE END-EXEC. 
     EXEC SQL DECLARE DB-NAME2 DATABASE END-EXEC. 
*    Connect to the two non-default databases. 
     EXEC SQL 
         CONNECT :USERNAME IDENTIFIED BY :PASSWD 
         AT DB-NAME1 USING :DB-STRING1 
     END-EXEC. 
     EXEC SQL 
         CONNECT :USERNAME IDENTIFIED BY :PASSWD 
         AT DB-NAME2 USING :DB-STRING2 
     END-EXEC. 

The string syntax in DB-STRING1 and DB-STRING2 depends on your network driver and how it is configured. DB-NAME1 and DB-NAME2 name the non-default connections; they can be undeclared identifiers or host variables.

For step-by-step instructions on connecting to Oracle via SQL*Net, see Chapter 3 in the Programmer's Guide to the Oracle Precompilers.




Go to previous file in sequence Go to next file in sequence
Prev Next
Oracle
Copyright © 1997 Oracle Corporation.
All Rights Reserved.
Go to Product Documentation Library
Library
Go to books for this product
Product
Go to Contents for this book
Contents
Go to Index
Index