When you execute an embedded SQL statement, it either succeeds or fails, and might result in an error or warning. You need a way to handle these results. Pro*COBOL provides the following error handling mechanisms:

• SQLCODE status variable

• SQLSTATE status variable

• SQL Communications Area (SQLCA)

• WHENEVER statement

• Oracle Communications Area (ORACA)

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

Pro*COBOL precompiler options and values as well as all EXEC SQL statements, inline commands, and COBOL statements are case-insensitive. The precompiler accepts both upper- and lower-case tokens.

­­Pro*COBOL supports the standard implementation of COBOL for your operating system (usually COBOL-85 or COBOL-74). Some platforms may support both COBOL implementations. For more information, see your Oracle system-specific documentation.

The precompiler option FORMAT, specifies the format of your source code. If you specify FORMAT=ANSI (the default), you are conforming as much as possible to the ANSI standard. In this format, columns 1 through 6 can contain an optional sequence number, and column 7 (indicator area) can indicate comments or continuation lines.

Division headers, section headers, paragraph names, FD and 01 statements begin in columns 8 through 11 (area A). Other statements, including EXEC SQL and EXEC ORACLE statements, must be placed in area B (columns 12 through 72). These guidelines for source code format can be overridden by your compiler's rules.

If you specify FORMAT=TERMINAL, COBOL statements can begin in column 1 (the left-most column), or column 1 can be the indicator area. This format is also subject to the rules of your compiler.

You can specify FORMAT=VARIABLE to allow Flexible B Area Length.

Consult your COBOL compiler documentation for your own platform to determine the actual acceptable formats for COBOL statements.

In SQL, you must use commas to separate list items, as the following example shows:

     EXEC SQL SELECT ENAME, JOB, SAL
         INTO :EMP-NAME, :JOB-TITLE, :SALARY
         FROM EMP
         WHERE EMPNO = :EMP-NUMBER
     END-EXEC.

In COBOL, you can use commas or blanks to separate list items. For example, the following two statements are equivalent:

     ADD AMT1, AMT2, AMT3 TO TOTAL-AMT. 
     ADD AMT1 AMT2 AMT3 TO TOTAL-AMT.  

You can place COBOL comment lines within SQL statements. COBOL comment lines start with an asterisk (*) in the indicator area.

You can also place ANSI SQL-style comments starting with "- - " within SQL statements at the end of a line (but not after the last line of the SQL statement).

COBOL comments continue for the rest of the line after these two characters: "*>".

You can place C-style comments (/* ... */) in SQL statements.

The following example shows all four styles of comments:

     MOVE 12 TO DEPT-NUMBER. *> This is the software development group.
     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

You cannot nest comments or place them on the last line of a SQL statement after the terminator END-EXEC.

You can continue SQL statements from one line to the next, according to the rules of COBOL, as this example shows:

     EXEC SQL SELECT ENAME, SAL INTO :EMP-NAME, :SALARY FROM EMP 
         WHERE DEPTNO = :DEPT-NUMBER 
     END-EXEC. 

No continuation indicator is needed.

To continue a string literal from one line to the next, code the literal through column 72. On the next line, code a hyphen (-) in column 7, a quote in column 12 or beyond, and then the rest of the literal. An example follows:

 WORKING STORAGE SECTION. 
     ... 
     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
         ... 
 01  UPDATE-STATEMENT  PIC X(80) VALUE "UPDATE EMP SET BON 
-               "US = 500 WHERE DEPTNO = 20". 
     EXEC SQL END DECLARE SECTION END-EXEC.  

Copy statements are not parsed by Pro*COBOL. Therefore, files included with the COPY command should not contain definitions of Host variables or contain embedded SQL statements. Instead, use the INCLUDE precompiler statement which is documented in "Using the INCLUDE Statement". Be careful when using INCLUDE and also using DECLARE_SECTION=YES. Group items should be either placed all inside or all outside of a Declare Section.

Pro*COBOL supports the DECIMAL-POINT IS COMMA clause in the ENVIRONMENT DIVISION. If the DECIMAL-POINT IS COMMA clause appears in the source file, then the comma will be allowed as the symbol beginning the decimal part of any numeric literals in the VALUE clauses.

For example, the following is allowed:

            IDENTIFICATION DIVISION.
            PROGRAM-ID.  FOO
            ENVIRONMENT DIVISION.
            CONFIGURATION   SECTION.
            SPECIAL-NAMES.
                DECIMAL-POINT IS COMMA.                *>  <-- **
            DATA DIVISION.
            WORKING-STORAGE SECTION.
        ...
            01  WDATA1          PIC      S9V999 VALUE  +,567. *>  <--- **
            01  WDATA2          PIC      S9V999 VALUE  -,234. *>  <--- **
        ...

The LITDELIM option specifies the delimiters for COBOL string constants and literals. If you specify LITDELIM=APOST, 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.

The following division headers are optional:

• IDENTIFICATION DIVISION

• ENVIRONMENT DIVISION

• DATA DIVISION

Note that the PROCEDURE DIVISION header is not optional. The following source can be precompiled:

*IDENTIFICATION DIVISION header is optional
 PROGRAM-ID.     HELLO.
*ENVIRONMENT DIVISION header is optional
 CONFIGURATION SECTION.
*DATA DIVISION header is optional
 WORKING-STORAGE SECTION.
 PROCEDURE       DIVISION.
     DISPLAY "Hello World!".
     STOP RUN.

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.

Figurative constants, such as HIGH-VALUE, ZERO, and SPACE, cannot be used in SQL statements. For example, the following is invalid:

     EXEC SQL DELETE FROM EMP WHERE COMM = ZERO END-EXEC.
 

Instead, use the following:

     EXEC SQL DELETE FROM EMP WHERE COMM = 0 END-EXEC. 

Pro*COBOL cannot process arbitrarily long source files. Some of the variables used internally limit the size of the generated file. There is no absolute limit to the number of lines allowed, but the following aspects of the source file are contributing factors to the file-size constraint:

• Complexity of the embedded SQL statements (for example, the number of bind and define variables)

• Whether a database name is used (for example, connecting to a database with an AT clause)

• Number of embedded SQL statements

To prevent problems related to this limitation, use multiple program units to sufficiently reduce the size of the source files.

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

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.

For portability, you may wish to restrict the length of host variable names to 18 or fewer characters (the length mandated by the SQL standard).

You can use hyphenated host-variable names in static SQL statements but not in dynamic SQL. For example, the following usage is invalid:

     MOVE "DELETE FROM EMP WHERE EMPNO = :EMP-NUMBER" TO SQLSTMT. 
     EXEC SQL PREPARE STMT1 FROM SQLSTMT END-EXEC. 

When declaring host variables, you can use level numbers 01 through 49, and 77. Pro*COBOL does not allow variables containing the VARYING clause or pseudo-type variables (these datatypes are prefixed with "SQL- ") to be declared level 49 or 77.

With the MAXLITERAL option, you can specify the maximum length of string literals generated by Pro*COBOL, so that compiler limits are not exceeded. For Pro*COBOL, the default value is 256, but you might have to specify a lower value. On IBM-proprietary S370 operating systems it is 120.

ANSI standard National Character Set datatypes are supported for handling multibyte character data. The PIC N or PIC G clause, if supported by your compiler, defines variables that store fixed-length NCHAR strings. You can store variable-length, multibyte National Character Set strings using COBOL group items consisting of a length field and a string field.

The environmental variable NLS_NCHAR is available to specify a client-side Globalization Support National Character Set.

In SQL, a NULL represents a missing, unknown, or inapplicable column value; it equates neither to zero nor to a blank. Use the NVL function to convert NULLs to non-NULL values, use the IS [NOT] NULL comparison operator to search for NULLs, and use indicator variables to insert and test for NULLs.

You can associate standard COBOL paragraph and section 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 and section names in a WHENEVER ... DO or WHENEVER ... GOTO statement, as the next example shows:

 PROCEDURE DIVISION. 
 MAIN. 
     EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. 
     ... 
 SQL-ERROR SECTION. 
     ... 

You must begin all paragraph names in area A.

You can use the COBOL REDEFINES clause to redefine group or elementary items. For example, the following declarations are valid:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
 01  REC-ID   PIC X(4). 
 01  REC-NUM  REDEFINES  REC-ID  PIC S9(4) COMP. 
     EXEC SQL END DECLARE SECTION END-EXEC.

And:

     EXEC SQL BEGIN DECLARE SECTION END-EXEC. 
  01  STOCK. 
       05  DIVIDEND     PIC X(5). 
       05  PRICE        PIC X(6). 
   01  BOND  REDEFINES  STOCK. 
       05  COUPON-RATE  PIC X(4). 
       05  PRICE        PIC X(7). 
     EXEC SQL END DECLARE SECTION END-EXEC.

Pro*COBOL issues no warning or error if a single INTO clause uses items from both a group item host variable and from its re-definition.

COBOL relational operators differ from their SQL equivalents, as shown in Relational Operators. Furthermore, COBOL enables the use of words instead of symbols, whereas SQL does not.

A COBOL sentence includes one or more COBOL or SQL statements, or both of them, 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. 

SQL statements may be ended by a comma, a period, or another COBOL statement.

A Declare Section 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:

• Host-variable and indicator-variable declarations

• Non-host COBOL variables

• EXEC SQL DECLARE statements

• EXEC SQL INCLUDE statements

• EXEC SQL VAR statements

• EXEC ORACLE statements

• COBOL comments

The Declare Section is optional. For backward compatibility with releases prior to 8.0, for which it was required, Pro*COBOL provides a command-line precompiler 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 is 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.

If DECLARE_SECTION is set to YES, then you must declare all program variables used in SQL statements inside the Declare Section. If DECLARE_SECTION is set to NO, then it is optional to use a Declare Section. In this case, declarations of host variables and indicator variables can be made either inside or outside a Declare Section. This optional behavior is a change from Release 8.0 and earlier releases.

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

In the following example, the SELECT statement is precompiled only when the symbol SITE2 is defined:

     EXEC ORACLE IFDEF SITE2 END-EXEC.
     EXEC SQL SELECT DNAME 
        INTO :DEPT-NAME 
        FROM DEPT 
        WHERE DEPTNO = :DEPT-NUMBER
     EXEC ORACLE ENDIF END-EXEC.

Blocks of conditions can be nested as shown in the following example:

     EXEC ORACLE IFDEF OUTER END-EXEC. 
     EXEC ORACLE IFDEF INNER END-EXEC. 
     ... 
     EXEC ORACLE ENDIF END-EXEC. 
     EXEC ORACLE ENDIF END-EXEC. 

You can "Comment out" host-language or embedded SQL code by placing it between IFDEF and ENDIF and not defining the symbol.

You can define a symbol in two ways. Either include the statement

     EXEC ORACLE DEFINE symbol END-EXEC.

in your host program or define the symbol on the command line using the syntax

... INAME=filename ... DEFINE=symbol 

where symbol is not case-sensitive.

Some port-specific symbols are predefined for you when Pro*COBOL is installed on your system. For example, predefined operating system symbols include CMS, MVS, UNIX, and VMS.