Pro*COBOL Precompiler Programmer's Guide
Release 8.1.5

A68023-01

Library

Product

Contents

Index

Prev Next

4
Datatypes and Host Variables

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

The Oracle8i Datatypes

Oracle8i recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle8i stores data in database columns. Oracle8i also uses internal datatypes to represent dtabase pseudocolumns. An external datatype specifies how data is stored in a host varable

Internal Datatypes

Oracle uses the following internal datatypes to store values in database colum

Table 4-1 Internal datatypes
Name  Code  Description 

CHAR  

96  

<= 2000-byte, fixed-length string  

NCHAR  

96  

<= 2000-byte, fixed-length single-byte or fixed-width multi-byte 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  

NUMBER  

2  

fixed or floating point number, represented in binary coded decimal format  

RAW  

23  

<= 255-byte, variable-length binary data  

ROWID  

11  

fixed-length binary value  

VARCHAR2  

1  

<= 4000-byte, variable-length string  

NVARCHAR2  

1  

<= 4000-byte, variable-length single-byte or fixed-width multi-byte string  

CHAR

You use the CHAR datatype to store fixed-length character data. How the data is represented internally depends on the database character set. The CHAR datatype takes an optional parameter that lets you specify a maximum width up to 2000 bytes. The syntax follows:

CHAR[(maximum_width)]

If you do not specify the maximum width, it defaults to 1. Remember, you specify the maximum width of a CHAR(n) column in bytes, not characters. So, if a CHAR(n) column stores multi-byte (2-byte) characters, its maximum width is less than n/2 characters.

NCHAR

Use this datatype to store NLS (National Language Support) strings. See "National Language Support". NCHAR values can not be converted to an internal datatype and are only used in the Declare Table when performing a semantics check with SQLCHECK=SEMANTICS (or FULL). See "Specifying SQLCHECK=SEMANTICS" for a discussion of semantics checking.

See "DECLARE TABLE (Oracle Embedded SQL Directive)" for a discussion and syntax diagram of this embedded SQL directive. You can not insert CHAR values into an NCHAR column. You can not insert NCHAR values into a CHAR column. This datatype can not be used in VAR statements for datatype equivalences.

DATE

You use the DATE datatype to store dates and times in 7-byte, fixed-length fields. The date portion defaults to the first day of the current month; the time portion defaults to midnight.

Internally, DATEs are stored in a binary format. When converting a DATE column value to a character string in your program, Oracle8i uses the default format mask for your session. If you need other date/time information such as the date in Julian days, use the TO_CHAR function with a format mask. Always convert DATE column values to and from character strings using (external) character datatypes such as VARCHAR2 or STRING.

LONG

You use the LONG datatype to store variable-length character strings. LONG columns can store text, arrays of characters, or even short documents. The LONG datatype is like the VARCHAR2 datatype, except the maximum width of a LONG column is 2147483647 bytes or two gigabytes.

You can use LONG columns in UPDATE, INSERT, and (most) SELECT statements, but not in expressions, function calls, or SQL clauses such as WHERE, GROUP BY, and CONNECT BY. Only one LONG column is allowed per database table and that column cannot be indexed.

LONG RAW

You use the LONG RAW datatype to store variable-length binary data or byte strings. The maximum width of a LONG RAW column is 2147483647 bytes or two gigabytes.

LONG RAW data is like LONG data, except that Oracle8i assumes nothing about the meaning of LONG RAW data and does no character set conversions when you transmit LONG RAW data from one system to another. The restrictions that apply to LONG data also apply to LONG RAW data.

NUMBER

You use the NUMBER datatype to store fixed or floating point numbers of virtually any size. You can specify precision, which is the total number of digits, and scale, which determines where rounding occurs.

The maximum precision of a NUMBER value is 38; the magnitude range is 1.0E-129 to 9.99E125. Scale can range from -84 to 127. For example, a scale of -3 means the number is rounded to the nearest thousand (3456 becomes 3000). A scale of 2 means the value is rounded to the nearest hundredth (3.456 becomes 3.46).

When you specify precision and scale, Oracle8i does extra integrity checks before storing the data. If a value exceeds the precision, Oracle8i issues an error message; if a value exceeds the scale, Oracle8i rounds the value.

RAW

You use the RAW datatype to store binary data or byte strings (a sequence of graphics characters, for example). RAW data is not interpreted by Oracle8i.

The RAW datatype takes a required parameter that lets you specify a maximum width up to 255 bytes. The syntax follows:

RAW(maximum_width)

You cannot use a constant or variable to specify the maximum width; you must use an integer literal.

RAW data is like CHAR data, except that Oracle8i assumes nothing about the meaning of RAW data and does no character set conversions (from 7-bit ASCII to EBCDIC Code Page 500 for example) when you transmit RAW data from one system to another.

ROWID

Internally, every table in an Oracle8i database has a pseudocolumn named ROWID, which stores binary values called ROWIDs. ROWIDs uniquely identify rows and provide the fastest way to access particular rows.

For more about how to use ROWIDs, see "Universal ROWIDs".

VARCHAR2

You use the VARCHAR2 datatype to store variable-length character strings. How the strings are represented internally depends on the database character set, which might be 7-bit ASCII or EBCDIC Code Page 500 for example.

The maximum width of a VARCHAR2 database column is 4000 bytes. To define a VARCHAR2 column, you use the syntax

VARCHAR2(maximum_width)

where maximum_width is an integer literal in the range 1 .. 2000.

You specify the maximum width of a VARCHAR2(n) column in bytes, not characters. So, if a VARCHAR2(n) column stores multi-byte (2-byte) characters, its maximum width is less than n/2 characters.

NVARCHAR2

Use NVARCHAR2 to store variable-length NLS character data. For fixed-width character sets, specify the maximum length in characters. For variable-width character sets, specify the maximum length in bytes. See "National Language Support". NVARCHAR2 values can not be converted to an internal datatype and are only used in the Declare Table when performing a semantics check with SQLCHECK=SEMANTICS (or FULL). See "Specifying SQLCHECK=SEMANTICS" for a discussion of semantics checking. See "DECLARE TABLE (Oracle Embedded SQL Directive)" for a discussion and syntax diagram of this embedded SQL directive. You can not insert VARCHAR2 values into an NVARCHAR2 column. You can not insert NVARCHAR2 values into a VARCHAR2 column.This datatype can not be used in VAR statements for datatype equivalences.

SQL Pseudocolumns and Functions

SQL recognizes the pseudocolumns in Table 4-2, which return specific data items:

Table 4-2 Pseudocolumns and Internal Datatypes
Pseudocolumn  Internal Datatype 

CURRVAL  

NUMBER  

LEVEL  

NUMBER  

NEXTVAL  

NUMBER  

ROWID  

ROWID  

ROWNUM  

NUMBER  

Pseudocolumns are not actual columns in a table. However, pseudocolumns are treated like columns, so their values must be SELECTed from a table. Sometimes it is convenient to select pseudocolumn values from a dummy table.

In addition, SQL recognizes the functions without parameters in Table 4-3, which also return specific data items:

Table 4-3 Functions and Internal Datatypes
Function  Internal Datatype 

SYSDATE  

DATE  

UID  

NUMBER  

USER  

VARCHAR2  

You can refer to SQL pseudocolumns and functions in SELECT, INSERT, UPDATE, and DELETE statements. In the following example, you use SYSDATE to compute the number of months since an employee was hired:

     EXEC SQL SELECT MONTHS_BETWEEN(SYSDATE, HIREDATE) 
         INTO :MONTHS-OF-SERVICE 
         FROM EMP 
         WHERE EMPNO = :EMP-NUMBER
     END EXEC. 

Brief descriptions of the SQL pseudocolumns and functions follow. For details, see the Oracle8i SQL Reference.

CURRVAL returns the current number in a specified sequence. Before you can reference CURRVAL, you must use NEXTVAL to generate a sequence number.

LEVEL returns the level number of a node in a tree structure. The root is level 1, children of the root are level 2, grandchildren are level 3, and so on.

LEVEL is used in the SELECT CONNECT BY statement to incorporate some or all the rows of a table into a tree structure. In an ORDER BY or GROUP BY clause, LEVEL segregates the data at each level in the tree.

You specify the direction in which the query walks the tree (down from the root or up from the branches) with the PRIOR operator. In the START WITH clause, you specify a condition that identifies the root of the tree.

NEXTVAL returns the next number in a specified sequence. After creating a sequence, you can use it to generate unique sequence numbers for transaction processing. In the following example, you use the sequence named partno to assign part numbers:

     EXEC SQL INSERT INTO PARTS 
         VALUES (PARTNO.NEXTVAL, :DESCRIPTION, :QUANTITY, :PRICE
     END EXEC.

If a transaction generates a sequence number, the sequence is incremented when you commit or rollback the transaction. A reference to NEXTVAL stores the current sequence number in CURRVAL.

ROWNUM returns a number indicating the sequence in which a row was selected from a table. The first row selected has a ROWNUM of 1, the second row has a ROWNUM of 2, and so on. If a SELECT statement includes an ORDER BY clause, ROWNUMs are assigned to the selected rows before the sort is done.

You can use ROWNUM to limit the number of rows returned by a SELECT statement. Also, you can use ROWNUM in an UPDATE statement to assign unique values to each row in a table. Using ROWNUM in the WHERE clause does not stop the processing of a SELECT statement; it just limits the number of rows retrieved. The only meaningful use of ROWNUM in a WHERE clause is

     ... WHERE ROWNUM < constant END-EXEC. 

because the value of ROWNUM increases only when a row is retrieved. The following search condition can never be met because the first four rows are not retrieved:

     ... WHERE ROWNUM = 5 END-EXEC. 

SYSDATE returns the current date and time.

UID returns the unique ID number assigned to an Oracle user.

USER returns the username of the current Oracle user.

External Datatypes

As Table 4-4 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.

Table 4-4 External Datatypes
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  

DISPLAY TRAILING  

152  

COBOL numeric with trailing sign  

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  

NUMBER  

2  

integer or floating-point number  

OVER-PUNCH LEADING  

172  

numeric with embedded leading sign  

OVER-PUNCH TRAILING  

154  

numeric with embedded trailing sign  

RAW  

23  

<= 65535-byte, fixed-length binary data (2)  

ROWID  

11  

fixed-length binary value (system-specific)  

STRING  

5  

<= 65535-byte, null-terminated character string (2)  

UNSIGNED  

68  

2-byte or 4-byte unsigned integer  

UNSIGNED DISPLAY  

153  

COBOL unsigned numeric  

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  

Notes:

  1. CHAR is datatype 1 when PICX=VARCHAR2 and datatype 96 when PICX=CHARF.

  2. Maximum size is 32767 (32K) on some platforms.

CHAR

CHAR behavior depends on the settings of the option PICX. See "PICX".

CHARF

By default, Oracle8i assigns the CHARF datatype to all non-varying character host variables. You use the CHARF datatype to store fixed-length character strings. On most platforms, the maximum length of a CHARF value is 65535 (64K) bytes. See "PICX".

On Input. Oracle8i reads the number of bytes specified for the input host variable, does not strip trailing blanks, then stores the input value in the target database column.

If the input value is longer than the defined width of the database column, Oracle8i generates an error. If the input value is all-blank, Oracle8i treats it like a character value.

On Output. Oracle8i returns the number of bytes specified for the output host variable, blank-padding if necessary, then assigns the output value to the target host variable. If a NULL is returned, Oracle8i fills the host variable with blanks.

If the output value is longer than the declared length of the host variable, Oracle8i truncates the value before assigning it to the host variable. If an indicator variable is available, Oracle8i sets it to the original length of the output value.

CHARZ

Use the CHARZ datatype to store fixed-length, null-terminated character strings. On most platforms, the maximum length of a CHARZ value is 65535 bytes. You should not need this external type in Pro*COBOL.

On input, the CHARZ and STRING datatypes work the same way. You must null-terminate the input value. The null terminator serves only to delimit the string; it is not part of the data.

On output, the CHARZ and CHAR datatypes work the same way. Oracle8i appends a null terminator to the output value, which is also blank-padded if necessary.

DATE

Use the DATE datatype to store dates and times in 7-byte, fixed-length fields. As Table 4-5 shows, the century, year, month, day, hour (in 24-hour format), minute, and second are stored in that order from left to right.

Table 4-5 Date Format
Byte 

Meaning  

Century  

Year  

Month  

Day  

Hour  

Minute  

Second  

Example 17-OCT-1994 at 1:23:12 PM  

119  

194  

10  

17  

14  

24  

13  

The century and year bytes are in excess-100 notation. The hour, minute, and second are in excess-1 notation. Dates before the Common Era (B.C.E.) are less than 100. The epoch is January 1, 4712 B.C.E. For this date, the century byte is 53 and the year byte is 88. The hour byte ranges from 1 to 24. The minute and second bytes range from 1 to 60. The time defaults to midnight (1, 1, 1).

DECIMAL

With Pro*COBOL, use the DECIMAL datatype to store packed decimal numbers for calculation. In COBOL, the host variable must be a signed COMP-3 field with an implied decimal point. If significant digits are lost during data conversion, Oracle8i fills the host variable with asterisks.

DISPLAY

With Pro*COBOL, use the DISPLAY datatype to store numeric character data. The DISPLAY datatype refers to a COBOL "DISPLAY SIGN LEADING SEPARATE" number, which requires n + 1 bytes of storage for PIC S9(n), and n + d + 1 bytes of storage for PIC S9(n)V9(d).

FLOAT

Use the FLOAT datatype to store numbers that have a fractional part or that exceed the capacity of the INTEGER datatype. The number is represented using the floating-point format of your computer and typically requires 4 or 8 bytes of storage. You must specify a length for input and output host variables.

Oracle8i can represent numbers with greater precision than floating point implementations because the internal format of Oracle8i numbers is decimal.

Note: In SQL statements, when comparing FLOAT values, use the SQL function ROUND because FLOAT stores binary (not decimal) numbers; so, fractions do not convert exactly.

INTEGER

Use the INTEGER datatype to store numbers that have no fractional part. An integer is a signed, 2- or 4-byte binary number. The order of the bytes in a word is platform-dependent. You must specify a length for input and output host variables. On output, if the column value is a floating point number, Oracle8i truncates the fractional part.

LONG

Use the LONG datatype to store fixed-length character strings. The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG value is 2147483647 bytes (two gigabytes).

LONG RAW

Use the LONG RAW datatype to store fixed-length, binary data or byte strings. The maximum length of a LONG RAW value is 2147483647 bytes (two gigabytes).

LONG RAW data is like LONG data, except that Oracle8i assumes nothing about the meaning of LONG RAW data and does no character set conversions when you transmit LONG RAW data from one system to another.

LONG VARCHAR

Use the LONG VARCHAR datatype to store variable-length character strings. LONG VARCHAR variables have a 4-byte length field followed by a string field. The maximum length of the string field is 2147483643 bytes. In an EXEC SQL VAR statement, do not include the 4-byte length field.

LONG VARRAW

Use the LONG VARRAW datatype to store binary data or byte strings. LONG VARRAW variables have a 4-byte length field followed by a data field. The maximum length of the data field is 2147483643 bytes. In an EXEC SQL VAR statement, do not include the 4-byte length field.

NUMBER

Use the NUMBER datatype to store fixed or floating point numbers. You can specify precision and scale. The maximum precision of a NUMBER value is 38; the magnitude range is 1.0E-129 to 9.99E125. Scale can range from -84 to 127.

NUMBER values are stored in variable-length format, starting with an exponent byte and followed by up to 20 mantissa bytes. The high-order bit of the exponent byte is a sign bit, which is set for positive numbers. The low-order 7 bits represent the exponent, which is a base-100 digit with an offset of 65.

Each mantissa byte is a base-100 digit in the range 1 .. 100. For positive numbers, 1 is added to the digit. For negative numbers, the digit is subtracted from 101, and, unless there are 20 mantissa bytes, a byte containing 102 is appended to the data bytes. Each mantissa byte can represent two decimal digits. The mantissa is normalized and leading zeros are not stored. You can use up to 20 data bytes for the mantissa but only 19 are guaranteed accurate. The 19 bytes, each representing a base-100 digit, allow a maximum precision of 38 digits.

On output, the host variable contains the number as represented internally by Oracle8i. To accommodate the largest possible number, the output host variable must be 21 bytes long. Only the bytes used to represent the number are returned. Oracle8i does not blank-pad or null-terminate the output value. If you need to know the length of the returned value, use the VARNUM datatype instead.

Normally, there is little reason to use this datatype.

RAW

Use the RAW datatype to store fixed-length binary data or byte strings. On most platforms, the maximum length of a RAW value is 65535 bytes.

RAW data is like CHAR data, except that Oracle8i assumes nothing about the meaning of RAW data and does no character set conversions when you transmit RAW data from one system to another.

ROWID

Before the release of Oracle8, the ROWID datatype was used to store the physical address of each row of each table, as a hexadecimal number. The ROWID contained the physical address of the row and allowed you to retrieve the row in a single efficient block access.

With Oracle8, the logical ROWID was introduced. Rows in Index-Organized tables do not have permanent physical addresses. The logical ROWID is accessed using the same syntax as the physical ROWID. For this reason, the physical ROWID was expanded in size to include a data object number (schema objects in the same segment).

To support both logical and physical ROWIDs (as well as ROWIDs of non-Oracle tables) the Universal ROWID was defined.

You can use VARCHAR2 host variables to store ROWIDs in a readable format. When you select or fetch a ROWID into a VARCHAR2 host variable, Oracle8i converts the binary value to an 18-byte character string and returns it in the format

BBBBBBBB.RRRR.FFFF 

where BBBBBBBB is the block in the database file, RRRR is the row in the block (the first row is 0), and FFFF is the database file. These numbers are hexadecimal. For example, the ROWID

0000000E.000A.0007 

points to the 11th row in the 15th block in the 7th database file.

Typically, you fetch a ROWID into a VARCHAR2 host variable, then compare the host variable to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement. That way, you can identify the latest row fetched by a cursor. For an example, see "Mimicking the CURRENT OF Clause".

Note: If you need full portability or your application communicates with a non-Oracle database via Transparent Gateway, specify a maximum length of 256 (not 18) bytes when declaring the VARCHAR2 host variable. If your application communicates with a non-Oracle data source via Oracle Open Gateway, specify a maximum length of 256 bytes. Though you can assume nothing about its contents, the host variable will behave normally in SQL statements.

STRING

The STRING datatype is like the VARCHAR2 datatype, except that a STRING value is always null-terminated.

On Input. Oracle8i uses the specified length to limit the scan for a null terminator. If a null terminator is not found, Oracle8i generates an error. If you do not specify a length, Oracle8i assumes the maximum length, which is 65535 on most platforms.

The minimum length of a STRING value is 2 bytes. If the first character is a null terminator and the specified length is 2, Oracle8i inserts a NULL unless the column is defined as NOT NULL. An all-blank or null-terminated value is stored intact.

On Output. Oracle8i appends a null byte to the last character returned. If the string length exceeds the specified length, Oracle8i truncates the output value and appends a null byte.

UNSIGNED

Use the UNSIGNED datatype to store unsigned integers. An unsigned integer is a binary number of 2 or 4 bytes. The order of the bytes in a word is system-dependent. You must specify a length for input and output host variables. On output, if the column value is a floating point number, Oracle8i truncates the fractional part.

VARCHAR

Use the VARCHAR datatype to store variable-length character strings. VARCHAR variables have a 2-byte length field followed by a 65533-byte string field. However, for VARCHAR array elements, the maximum length of the string field is 65530 bytes. When you specify the length of a VARCHAR variable, be sure to include 2 bytes for the length field. For longer strings, use the LONG VARCHAR datatype. In an EXEC SQL VAR statement, do not include the 2-byte length field.

VARCHAR2

Use the VARCHAR2 datatype to store variable-length character strings. On most platforms, the maximum length of a VARCHAR2 value is 65535 bytes.

Specify the maximum length of a VARCHAR2(n) value in bytes, not characters. So, if a VARCHAR2(n) variable stores multi-byte characters, its maximum length is less than n characters.

On Input. Oracle8i reads the number of bytes specified for the input host variable, strips any trailing blanks, then stores the input value in the target database column. Be careful. An un-initialized host variable can contain nulls. So, always blank-pad a character input host variable to its declared length. (COBOL PIC X(n) variables do this automatically.)

If the input value is longer than the defined width of the database column, Oracle8i generates an error. If the input value is all-blank, Oracle8i treats it like a NULL.

Oracle8i can convert a character value to a NUMBER column value if the character value represents a valid number. Otherwise, Oracle8i generates an error.

On Output. Oracle8i returns the number of bytes specified for the output host variable, blank-padding if necessary, then assigns the output value to the target host variable. If a NULL is returned, Oracle8i fills the host variable with blanks.

If the output value is longer than the declared length of the host variable, Oracle8i truncates the value before assigning it to the host variable. If an indicator variable is available, Oracle8i sets it to the original length of the output value.

Oracle8i can convert NUMBER column values to character values. The length of the character host variable determines precision. If the host variable is too short for the number, scientific notation is used. For example, if you select the column value 123456789 into a host variable of length 6, Oracle8i returns the value "1.2E08" to the host variable.

VARNUM

The VARNUM datatype is like the NUMBER datatype, except that the first byte of a VARNUM variable stores the length of the value.

On input, you must set the first byte of the host variable to the length of the value. On output, the host variable contains the length followed by the number as represented internally by Oracle8i. To accommodate the largest possible number, the host variable must be 22 bytes long. After selecting a column value into a VARNUM host variable, you can check the first byte to get the length of the value.

VARRAW

Use the VARRAW datatype to store variable-length binary data or byte strings. The VARRAW datatype is like the RAW datatype, except that VARRAW variables have a 2-byte length field followed by a <= 65533-byte data field. For longer strings, use the LONG VARRAW datatype. In an EXEC SQL VAR statement, do not include the 2-byte length field. To get the length of a VARRAW variable, simply refer to its length field.

Host Variables

Host variables are the key to communication between your host program and the server. Typically, a host program inputs data to the server, and the server outputs data to the program. The server 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 Pro*COBOL. COBOL datatypes must be compatible with the source/target database column.

The supported COBOL datatypes are shown in Table 4-6

Table 4-6 Host Variable Declarations
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  

SQL-CONTEXT  

runtime context  

SQL-ROWID  

universal ROWID  

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

  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.

See Table 4-7, "Compatible Oracle Internal Datatypes" below.

Table 4-7 Compatible Oracle Internal Datatypes
Internal Datatype  Notes  COBOL Datatype  Description 

CHAR(x) VARCHAR2(y)  

(1)

(1)  

PIC X...X

PIC X(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

 

NCHAR(u) NVARCHAR2(v)  

(2)
{2}
 

PIC {N...N | G...G}

PIC { N(n) | G(n)}  

national character string

n-national character string  

BLOB

CLOB

NCLOB

BFILE  


 

SQL-BLOB

SQL-CLOb

SQL-NCLOB

SQL-BFILE  

binary LOB

character LOB

national character LOB

external binary file  

NUMBER

NUMBER (p,s)  

(3)

 

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 (4)

n-character string (4)

variable-length string

n-byte variable-length string  

DATE

LONG

RAW

LONG RAW

ROWID  

(5)

(1)

(6)  

PIC X(n)

PIC X...X VARYING

SQL-ROWID  

n-byte character string

n-byte variable-length string

universal rowid  

Notes:

  1. <= x < =2000 bytes, default is 1. 1<=y <=4000 bytes, default is 1.

  2. 1<=u<=2000 bytes, default is 1. 1<=v<=4000 bytes, default is 1.

  3. p ranges from 2 to 38. s ranges from -84 to 127.

  4. 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 (,).

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

  6. When converted to a string type, a ROWID requires from 18 to 4000 bytes.

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

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.

No error or warning is issued, but any VALUES clause on a pseudo-type variable is ignored and discarded.

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

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. 
  ...
     EXEC SQL SELECT DHR, DMIN INTO :DEPARTURE.HOUR, :DEPARTURE.MINUTE
         FROM TIMETABLE
         WHERE ...

Restrictions

A host variable cannot substitute for a column, table, or other object in a SQL statement and must not be an Oracle8i reserved word. See Appendix C, "Reserved Words, Keywords, and Namespaces"for a list of 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 for character columns) in output host variables.

Using Indicator Variables

Here are the values indicator variables can take on.

On Input

The values your program can assign to an indicator variable have the following meanings:

-1  

Oracle will assign a NULL to the column, ignoring the value of the host variable.  

>=0  

Oracle will assign the value of the host variable to the column.  

On Output

T he values Oracle can assign to an indicator variable have the following meanings:

-1  

The column value is NULL, so the value of the host variable is indeterminate.  

0  

Oracle assigned an intact column value to the host variable.  

>0  

Oracle assigned a truncated column value to the host variable. The integer returned by the indicator variable is the original length of the column value, and SQLCODE in SQLCA is set to zero.  

-2  

Oracle assigned a truncated column variable to the host variable, but the original column value could not be determined (a LONG column, for example).  

Declaring Indicator Variables

An indicator variable must be explicitly declared as PIC S9(4) COMP and must not be a 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. 

Oracle8i Restrictions

If you SELECT or FETCH a NULL into a host variable that has no indicator, Oracle8i issues an error message.

You can disable the error message by also specifying UNSAFE_NULL=YES on the command line. For more information, see Chapter 14, "Precompiler Options".

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, Oracle8i issues an error message.

However, when MODE={ANSI | ANSI14 | ANSI13}, no error is generated. Values for indicator variables are discussed in Chapter 5, "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.

VARCHAR Variables

COBOL string datatypes are fixed length. However, Pro*COBOL lets you declare a variable-length string pseudo-type 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. 

Note: PIC N and PIC G are not allowed in definitions that use VARYING. To see how to correctly use PIC N and PIC G in VARCHAR variables, see "Implicit VARCHAR Group Items"

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, Pro*COBOL reads the value of the length field and uses that many characters of the string field. With output host variables, Pro*COBOL 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 characters, use these formats (length is expressed in double-byte characters):

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

<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 14, "Precompiler Options"

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:

Default for PIC X

The default datatype of PIC X variables is CHARF (was VARCHAR2 before release 8.0.) The 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" 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 database 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 VARCHAR2 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, Pro*COBOL re-appends trailing blanks up to the length of the database column. However, if you insert into a variable-length VARCHAR2 column, Pro*COBOL 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, Pro*COBOL 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.

Varrying-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, Pro*COBOL 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, Pro*COBOL 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 Pro*COBOL 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.

User-Specified Runtime Contexts

Starting with release 8.1, Pro*COBOL allows multithreaded Pro*C/C++ programs to call COBOL subprograms, using the arguments defined in the Linkage Section of the subprogram. One of these arguments can be the context.

Note: Multithreaded applications are not supported in Pro*COBOL.

A runtime context, usually simply called a context, is a handle to an area in client memory which contains zero or more connections, zero or more cursors, their inline options (such as MODE, HOLD_CURSOR, RELEASE_CURSOR, SELECT_CURSOR, etc.) and other additional state information.

To define a context host variable use pseudotype SQL-CONTEXT. For example:

 01  MY-CONTEXT  SQL-CONTEXT.

Use the CONTEXT ALLOCATE precompiler directive to allocate and initialize memory for a context:

     EXEC SQL CONTEXT ALLOCATE :context END-EXEC.

where context is a host variable that is a handle to the runtime context. An example is:

     EXEC SQL CONTEXT ALLOCATE :MY-CONTEXT END-EXEC.

Use the CONTEXT USE precompiler directive to define which context is to be used by the embedded SQL statements from that point on in the source file, not in the flow of program logic. That context value is used until another CONTEXT USE statement is encountered. The syntax is:

     EXEC SQL CONTEXT USE {:context | DEFAULT} END-EXEC.

The keyword DEFAULT specifies that the default (also known as the global) context is to be used in all the embedded SQL statements that will be executed subsequently, until another CONTEXT USE directive is encountered. A simple example is:

     EXEC SQL CONTEXT USE :MY-CONTEXT END_EXEC.

If the context variable MY-CONTEXT has not been defined and allocated already, an error is returned.

The CONTEXT FREE statement frees the memory used by the context after it is no longer needed:

     EXEC SQL CONTEXT FREE :context END-EXEC.

An example using our variable is:

     EXEC SQL CONTEXT FREE :MY-CONTEXT END-EXEC.

Universal ROWIDs

There are two kinds of table organization used in the database server: heap tables and index-organized tables.

Heap tables are the default. This is the organization used in all tables before Oracle8. The physical row address (ROWID) is a permanent property that is used to identify a row in a heap table. The external character format of the physical ROWID is an 18-byte character string in base-64 encoding.

An index-organized table does not have physical row addresses as permanent identifiers. A logical ROWID is defined for these tables. When you use a SELECT ROWID ... statement from an index-organized table the ROWID is an opaque structure that contains the primary key of the table, control information, and an optional physical "guess". You can use this ROWID in a SQL statement containing a clause such as "WHERE ROWID = ..." to retrieve values from the table.

The universal ROWID was introduced in the Oracle 8.1 release. Universal ROWID can be used for both physical ROWID and logical ROWID. You can use universal ROWIDs to access data in heap tables, or index-organized tables, since the table organization can change with no effect on applications. The column datatype used for ROWID is UROWID(length), where length is optional.

Use the universal ROWID in all new applications.

For more information on universal ROWIDs, see Oracle8i Concepts.

Declare a universal ROWID, which uses the pseudotype SQL-ROWID, this way:

 01  MY-ROWID SQL-ROWID.

Memory for the universal ROWID is allocated with the ALLOCATE statement:

     EXEC SQL ALLOCATE :MY-ROWID END-EXEC.

Use MY-ROWID in SQL DML statements like this:

     EXEC SQL SELECT ROWID INTO :MY-ROWID FROM MYTABLE WHERE ... END-EXEC.
...
     EXEC SQL UPDATE MYTABLE SET ... WHERE ROWID = :MY-ROWID END-EXEC.
...

Free the memory when you no longer need it with the FREE directive:

     EXEC SQL FREE :MY-ROWID END-EXEC.

You also have the option of using a character host variable of width between 1 and 4000 as the host bind variable for universal ROWID. Character-based universal ROWIDs are supported for heap tables only for backwards compatibility. Because a universal ROWID can be variable length there can be truncation when it is selected. For a more complete discussion of this variable see Oracle8i Concepts.

Use the character variable like this:

 01  MY-ROWID-CHAR PIC X(4000) VARYING.
... 
     EXEC SQL ALLOCATE :MY-ROWID-CHAR;
     EXEC SQL SELECT ROWID INTO :MY-ROWID-CHAR FROM MYTABLE WHERE ... END-EXEC.
...
     EXEC SQL UPDATE MYTABLE SET ... WHERE ROWID = :MY-ROWID-CHAR END-EXEC.
...
     EXEC SQL FREE :MY-ROWID-CHAR;

Subprogram SQLROWIDGET

The Oracle subprogram SQLROWIDGET allows you to retrieve the ROWID of the last row inserted, updated, or selected. SQLROWIDGET requires a context and a ROWID that were both declared as its arguments. To use the default context, move ZERO to a variable of type SQL-CONTEXT first.

Note: The universal ROWID must be declared and allocated before the call. The context must be declared and allocated before the call. Here is the syntax of the call:

     CALL "SQLROWIDGET" USING context rowid. 

where

context(IN)

is the runtime context variable, of pseudotype SQL-CONTEXT.

rowid (OUT)

is a universal ROWID variable, of pseudotype SQL-ROWID. When a normal execution finishes, this will point to a valid universal ROWID. In case of an error, MY-ROWID is undefined.

Here is a sample showing this subprogram's usage:

 01  MY-ROWID   SQL-ROWID. 
 01  MY-CONTEXT SQL-CONTEXT. 
 ...
     EXEC SQL ALLOCATE :MY-ROWID END-EXEC. 
     EXEC SQL CONTEXT ALLOCATE :MY-CONTEXT END-EXEC. 
     EXEC SQL CONTEXT USE :MY-CONTEXT END-EXEC. 
* INSERT, or UPDATE or DELETE Goes here: 
 ...
    CALL "SQLROWIDGET" USING MY-CONTEXT MY-ROWID. 
* MY-ROWID now has the universal rowid descriptor for the last row
 ...
    EXEC SQL CONTEXT FREE :MY-CONTEXT END-EXEC.
    EXEC SQL FREE :MY-ROWID END-EXEC. 
 ... 

National Language Support

Although the widely-used 7-bit or 8-bit ASCII and EBCDIC character sets are adequate to represent the Roman alphabet, some Asian languages, such as Japanese, contain thousands of characters. These languages require 16 bits or more, to represent each character. How does Oracle8i deal with such dissimilar languages?

Oracle8i provides National Language Support (NLS), which lets you process single-byte and multi-byte character data and convert between character sets. It also lets your applications run in different language environments. With NLS, number and date formats adapt automatically to the language conventions specified for a user session. Thus, NLS allows users around the world to interact with Oracle8i in their native languages.

You control the operation of language-dependent features by specifying various NLS parameters. You can set default parameter values in the initialization file. Table 4-8 shows what each NLS parameter specifies.

Table 4-8 NLS Parameters
NLS Parameter  Specifies 

NLS_LANGUAGE  

language-dependent conventions  

NLS_TERRITORY  

territory-dependent conventions  

NLS_DATE_FORMAT  

date format  

NLS_DATE_LANGUAGE  

language for day and month names  

NLS_NUMERIC_CHARACTERS  

decimal character and group separator  

NLS_CURRENCY  

local currency symbol  

NLS_ISO_CURRENCY  

ISO currency symbol  

NLS_SORT  

sort sequence  

The main parameters are NLS_LANGUAGE and NLS_TERRITORY. NLS_LANGUAGE specifies the default values for language-dependent features, which include

NLS_TERRITORY specifies the default values for territory-dependent features, which include

You can control the operation of language-dependent NLS features for a user session by specifying the parameter NLS_LANG as follows

NLS_LANG = <language>_<territory>.<character set> 

where language specifies the value of NLS_LANGUAGE for the user session, territory specifies the value of NLS_TERRITORY, and character set specifies the encoding scheme used for the terminal. An encoding scheme (usually called a character set or code page) is a range of numeric codes that corresponds to the set of characters a terminal can display. It also includes codes that control communication with the terminal.

You define NLS_LANG as an environment variable (or the equivalent on your system). For example, on UNIX using the C shell, you might define NLS_LANG as follows:

setenv NLS_LANG French_France.WE8ISO8859P1
 

To change the values of NLS parameters during a session, you use the ALTER SESSION statement as follows:

ALTER SESSION SET <nls_parameter> = <value> 

Pro*COBOL fully supports all the NLS features that allow your applications to process multilingual data stored in an Oracle8i database. For example, you can declare foreign-language character variables and pass them to string functions such as INSTRB, LENGTHB, and SUBSTRB. These functions have the same syntax as the INSTR, LENGTH, and SUBSTR functions, respectively, but operate on a per-byte basis rather than a per-character basis.

You can use the functions NLS_INITCAP, NLS_LOWER, and NLS_UPPER to handle special instances of case conversion. And, you can use the function NLSSORT to specify WHERE-clause comparisons based on linguistic rather than binary ordering. You can even pass NLS parameters to the TO_CHAR, TO_DATE, and TO_NUMBER functions. For more information about NLS, see the Oracle8i Application Developer's Guide - Fundamentals.

Multi-Byte NLS Character Sets

Pro*COBOL extends support for multi-byte NLS character sets through

Variable-width National Character Sets are not supported.

Restrictions When NLS_LOCAL=YES

When the precompiler option NLS_LOCAL is YES, the runtime library (SQLLIB) performs blank-padding and blank-stripping for NLS multi-byte datatypes.

When NLS_LOCAL=YES, multi-byte NCHAR features are not supported within a PL/SQL block. These features include N-quoted character literals and fixed-length character variables.

These restrictions then apply:

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

No Odd-Byte Widths. Oracle8i 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.

Character Strings in Embedded SQL

A multi-byte NLS character string in an embedded SQL statement consists of the letter N, followed by the string enclosed in single quotes.

For example,

     EXEC SQL 
         SELECT EMPNO INTO :EMP-NUM FROM EMP
         WHERE ENAME=N'<NLS_string>'
     END-EXEC.

Embedded DDL

When the precompiler option, NLS_LOCAL=YES, columns storing NCHAR data cannot be used in embedded data definition language (DDL) statements. This restriction cannot be enforced when precompiling, so the use of extended column types, such as NCHAR, within embedded DDL statements results in an execution error rather than a precompile error.

For more information about these options, see their entries in Chapter 14, "Precompiler Options".

Blank Padding

When a Pro*COBOL character variable is defined as a multi-byte NLS variable, the following blank padding and blank stripping rules apply, depending on the external datatype of the variable. See the section "Handling Character Data".

CHARF. Input data is stripped of any trailing double-byte spaces. However, if a string consists only of multi-byte spaces, a single multi-byte space is left in the buffer to act as a sentinel.

Output host variables are blank padded with multi-byte spaces.

VARCHAR. On input, host variables 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, the host variable is not blank padded at all. The length of the buffer is set to the length of the data in characters, not bytes.

STRING/LONG VARCHAR. These host variables are not supported for NLS data, since they can only be specified using dynamic SQL or datatype equivalencing, neither of which is supported for NLS data.

Indicator Variables

You can use indicator variables with multi-byte NLS character variables as use you would with any other variable, except column length values are expressed in characters instead of bytes. For a list of possible values, see "Using Indicator Variables".

Datatype Conversion

At precompile time, an external datatype is assigned to each host variable. For example, Pro*COBOL 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 Oracle8i. Oracle8i uses the codes to convert between internal and external datatypes.

Before assigning a SELECTed column value to an output host variable, Oracle8i 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, Oracle8i 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.

The datatype of the host variable must be compatible with that of the database column. It is your responsibility to make sure that values are convertible. For example, if you try to convert the string value "YESTERDAY" to a DATE column value, you get an error. Conversions between internal and external datatypes follow the usual data conversion rules. For instance, you can convert a CHAR value of "1234" to a 2-byte integer. But, you cannot convert a CHAR value of "65543" (number too large) or "10F" (number not decimal) to a 2-byte integer. Likewise, you cannot convert a string value that contains alphabetic characters to a NUMBER value.

Number conversion follows the conventions specified by National Language Support (NLS) parameters in the Oracle8i initialization file. For example, your system might be configured to recognize a comma (,) instead of a period (.) as the decimal character. For more information about NLS, see the Oracle8i Application Developer's Guide - Fundamentals.

The following table shows the supported conversions between internal and external datatypes.

Table 4-9 Conversions Between Internal and External Datatypes
  Internal  
External  

CHAR  

DATE  

LONG  

LONG RAW  

 

NUMBER  

RAW  

ROWID  

VARCHAR2  

CHAR  

I/O  

I/O (2)  

I/O  

I(3)  

 

I/O  

I/O (3)  

I/O (1)  

I/O  

CHARF  

I/O  

I/O (2)  

I/O  

I (3)  

 

I/O  

I/O (3)  

I/O (1)  

I/O  

CHARZ  

I/O  

I/O (2)  

I/O  

I (3)  

 

I/O  

I/O (3)  

I/O (1)  

I/O  

DATE  

I/O  

I/O  

I  

 

 

 

 

 

I/O  

DECIMAL  

I/O (4)  

 

I  

 

 

I/O  

 

 

I/O (4)  

DISPLAY  

I/O (4))  

 

I  

 

 

I/O  

 

 

I/O (4)  

FLOAT  

I/O (4)  

 

I  

 

 

I/O  

 

 

I/O (4)  

INTEGER  

I/O (4)  

 

I  

 

 

I/O  

 

 

I/O (4)  

LONG  

I/O  

I/O (2)  

I/O  

I (3.5)  

 

I/O  

I/O (3)  

I/O (1)  

I/O  

LONG RAW  

O(6)  

 

I (5,6)  

I/O  

 

 

I/O  

 

O (6)  

LONG VARCHAR  

I/O  

I/O(2)  

I/O  

I (3,5)  

 

I/O  

I/O(3))  

I/O (1)  

I/O  

LONG VARRAW  

I/O (6)  

 

I (5,6)  

I/O  

 

 

I/O  

 

I/O (6)  

NUMBER  

I/O (4)  

 

I  

 

 

I/O  

 

 

I/O (4)  

RAW  

I/O (6)  

 

I (5,6)  

I/O  

 

 

I/O  

 

I/O (6)  

ROWID  

I  

 

I  

 

 

 

 

I/O  

I  

STRING  

I/O  

I/O (2)  

I/O  

I (3.5)  

 

I/O  

I/O (3)  

I/O (1)  

I/O  

UNSIGNED  

I/O (4)  

 

I  

 

 

I/O  

 

 

I/O (4)  

VARCHAR  

I/O  

I/O (2)  

I/O  

I (3,5)  

 

I/O  

II/O (3)  

 

I/O  

VARCHAR2  

I/O  

I/O (2)  

I/O  

I (3)  

 

I/O  

I/O (3)  

I/O (1)  

I/O  

VARNUM  

I/O (4)  

 

I  

 

 

I/O  

 

 

I/O (4)  

VARRAW  

I/O (6)  

 

I (5,6)  

I/O  

 

 

I/O  

 

I/O (6)  

Notes:
  1. 1. On input, host string must be in Oracle'BBBBBBBB.RRRR.FFFF' format.

  2. On output, column value is returned in same format.

  3. 2. On input, host string must be the default DATE character format.

  4. On output, column value is returned in same format

  5. 3. On input, host string must be in hex format.

  6. On output, column value is returned in same format.

  7. 4. On output, column value must represent a valid number.

  8. 5. On input, length must be less than or equal to 2000.

  9. 6. On input, column value is stored in hex format.

  10. On output, column value must be in hex format.

  11. 7. On input, host string must be a valid OS label in text format.

  12. On output, column value is returned in same format.

  13. 8. On input, host string must be a valid OS label in raw format.

  14. On output, column value is returned in same format.

 
Legend:

I = input only

O = output only

I/O = input or output

 

Explicit Control Over DATE String Format

When you select a DATE column value into a character host variable, Oracle8i must convert the internal binary value to an external character value. So, Oracle8i implicitly calls the SQL function TO_CHAR, which returns a character string in the default date format. The default is set by the Oracle8i initialization parameter NLS_DATE_FORMAT. To get other information such as the time or Julian date, you must explicitly call TO_CHAR with a format mask.

A conversion is also necessary when you insert a character host value into a DATE column. Oracle8i implicitly calls the SQL function TO_DATE, which expects the default date format. To insert dates in other formats, you must explicitly call TO_DATE with a format mask.

For compatibility with other versions of SQL Pro*COBOL now provides the following precompiler option to specify date strings:

DATE_FORMAT={ISO | USA | EUR | JIS | LOCAL | 'fmt' (default LOCAL)}

The DATE_FORMAT option must be used on the command line or in a configuration file. The date strings are shown in the following table:

Table 4-10 Formats for Date Strings
Format Name  Abbreviation  Date Format 

International Standards Organization  

ISO  

yyyy-mm-dd  

USA standard  

USA  

mm/dd/yyyy  

European standard  

EUR  

dd.mm.yyyy  

Japanese Industrial Standard  

JIS  

yyyy-mm-dd  

installation-defined  

LOCAL  

Any installation-defined form.  

'fmt' is a date format model, such as 'Month dd, yyyy'. See the Oracle8i SQL Reference for the list of date format model elements.

Note: All separately compiled units to be linked together must use the same DATE_FORMAT value.

Datatype Equivalencing

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

Why Equivalence Datatypes?

Datatype equivalencing is useful in several ways. For example, suppose you want to use a null-terminated host string in a COBOL program. You can declare a PIC X host variable, then equivalence it to the external datatype STRING, which is always null-terminated.

You can use datatype equivalencing when you want Oracle8i to store but not interpret data. For example, if you want to store an integer host array in a LONG RAW database column, you can equivalence the host array to the external datatype LONG RAW.

In addition, you can use datatype equivalencing to override default datatype conversions. Unless NLS parameters in the initialization file specify otherwise, if you select a DATE column value into a character host variable, Oracle8i returns a 9-byte string formatted as follows:

DD-MON-YY 

However, if you equivalence the character host variable to the DATE external datatype, Oracle8i returns a 7-byte value in the internal format.

Host Variable Equivalencing

By default, Pro*COBOL assigns a specific external datatype to every host variable. You can override the default assignments by equivalencing host variables to external datatypes. This is called host variable equivalencing.

The syntax of the VAR embedded SQL statement is:

      EXEC SQL
          VAR <host_variable> IS <datatype> [CONVBUFSZ [IS] (<size>)]
      END-EXEC

or

      EXEC SQL VAR <host_variable> [CONVBUFSZ [IS] (<size>)] END-EXEC

where <datatype> is:

<SQL datatype> [ ( {<length> | <precision>, <scale> } ) ]

There must be at least one of the two clauses, or both.

where:

host_variable  

Is an input or output host variable (or host table) declared earlier.

The VARCHAR and VARRAW external datatypes have a 2-byte length field followed by an n-byte data field, where n lies in the range 1 .. 65533. So, if type_name is VARCHAR or VARRAW, host_variable must be at least 3 bytes long.

The LONG VARCHAR and LONG VARRAW external datatypes have a 4-byte length field followed by an n-byte data field, where n lies in the range 1 .. 2147483643. So, if type_name is LONG VARCHAR or LONG VARRAW, host_variable must be at least 5 bytes long.  

SQL datatype  

Is the name of a valid external datatype such as RAW or STRING.  

length  

An integer literal specifying a valid length in bytes. The value of length must be large enough to accommodate the external datatype.

When type_name is DECIMAL or DISPLAY, you must specify precision and scale instead of length. When type_name is VARNUM, ROWID, or DATE, you cannot specify length because it is predefined. For other external datatypes, length is optional. It defaults to the length of host_variable.

When specifying length, if type_name is VARCHAR, VARRAW, LONG VARCHAR, or LONG VARRAW, use the maximum length of the data field. Pro*COBOL accounts for the length field. If type_name is LONG VARCHAR or LONG VARRAW and the data field exceeds 65533 bytes, put "-1" in the length field.  

precision and scale  

Are integer literals that represent, respectively, the number of significant digits and the point at which rounding will occur. For example, a scale of 2 means the value is rounded to the nearest hundredth (3.456 becomes 3.46); a scale of -3 means the number is rounded to the nearest thousand (3456 becomes 3000).

You can specify a precision of 1 .. 99 and a scale of -84 .. 99. However, the maximum precision and scale of a database column are 38 and 127, respectively. So, if precision exceeds 38, you cannot insert the value of host_variable into a database column. On the other hand, if the scale of a column value exceeds 99, you cannot select or fetch the value into host_variable.

Specify precision and scale only when type_name is DECIMAL or DISPLAY.  

size  

An integer which is the size, in bytes, of a buffer used to perform conversion of the specified host_variable to another character set.  

Table 4-11 shows which parameters to use with each external datatype.

The CONVBUFSZ clause is explained in "CONVBUFSZ Clause in VAR Statement".

You cannot use EXEC SQL VAR with NCHAR host variables (those containing PIC G or PIC N clauses).

If DECLARE_SECTION=TRUE then you must have a Declare Section and you must place EXEC SQL VAR statements in the Declare Section.

For a syntax diagram of this statement, see "VAR (Oracle Embedded SQL Directive)".

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 Oracle8i 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(4) 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.

You can also declare a group item to be used as a LONG VARCHAR:

 01  MY-LONG-VARCHAR.
     05 UC-LEN PIC S9(9) COMP.
     05 UC-ARR PIC X(6000).
     EXEC SQL VAR MY-LONG-VARCHAR IS LONG VARCHAR(6000).

CONVBUFSZ Clause in VAR Statement

The EXEC SQL VAR statement can have an optional CONVBUFSZ clause. You specify the size, in bytes, of the buffer in the runtime library used to perform conversion of the specified host variable between character sets.

When you have not used the CONVBUFSZ clause, the runtime automatically determines a buffer size based on the ratio of the host variable character size (determined by NLS_LANG) and the character size of the database character set. This can sometimes result in the creation of a buffer of LONG size. Databases are allowed to have only one LONG column. An error is raised if there is more than one LONG value.

To avoid such errors, you use a length shorter than the size of a LONG. If a character set conversion results in a value longer than the length specified by CONVBUFSZ, then Pro*COBOL returns an error.

An Example

Suppose you want to select employee names from the EMP table, then pass them to a C-language routine that expects null-terminated strings. You need not explicitly null-terminate the names. Simply equivalence a host variable to the STRING external datatype, as follows:

      EXEC SQL BEGIN DECLARE SECTION END-EXEC.
      ... 
 01   EMP-NAME  PIC X(11).
     EXEC SQL VAR EMP-NAME IS STRING (11) END-EXEC. 
      EXEC SQL END DECLARE SECTION END-EXEC. 

The width of the ENAME column is 10 characters, so you allocate the new EMP-NAME 11 characters to accommodate the null terminator. (Here, length is optional because it defaults to the length of the host variable.) When you select a value from the ENAME column into EMP-NAME, Oracle8i null-terminates the value for you.

Table 4-11 Parameters for Host Variable Equivalencing
External Datatype  Length  Precision  Scale  Default Length 

CHAR  

optional  

n/a  

n/a  

declared length of variable  

CHARZ  

optional  

n/a  

n/a  

declared length of variable  

DATE  

n/a  

n/a  

n/a  

7 bytes  

DECIMAL  

n/a  

required  

required  

none  

DISPLAY  

n/a  

required  

required  

none  

DISPLAY TRAILING  

n/a  

required  

required  

none  

UNSIGNED DISPLAY  

n/a  

required  

required  

none  

OVERPUNCH TRAILING  

n/a  

required  

required  

none  

OVERPUNCH LEADING  

n/a  

required  

required  

none  

FLOAT  

optional (4 or 8)  

n/a  

n/a  

declared length of variable  

INTEGER  

optional (1, 2, or 4)  

n/a  

n/a  

declared length of variable  

LONG  

optional  

n/a  

n/a  

declared length of variable  

LONG RAW  

optional  

n/a  

n/a  

declared length of variable  

LONG VARCHAR  

required (note 1)  

n/a  

n/a  

none  

LONG VARRAW  

required (note 1)  

n/a  

n/a  

none  

NUMBER  

n/a  

n/a  

n/a  

not available  

STRING  

optional  

n/a  

n/a  

declared length of variable  

RAW  

optional  

n/a  

n/a  

declared length of variable  

ROWID  

n/a  

n/a  

n/a  

18 bytes (see note 2)  

UNSIGNED  

optional (1, 2, or 4)  

n/a  

n/a  

declared length of variable  

VARCHAR  

required  

n/a  

n/a  

none  

VARCHAR2  

optional  

n/a  

n/a  

declared length of variable  

VARNUM  

n/a  

n/a  

n/a  

22 bytes  

VARRAW  

optional  

n/a  

n/a  

none  

  1. If the data field exceeds 65533 bytes, pass -1.

  2. This length is typical but the default is port-specific.

Using the CHARF Datatype Specifier

You can use the datatype specifier CHARF in VAR statements to equivalence COBOL datatypes to the fixed-length ANSI datatype CHAR.s

When PICX=CHARF, specifying the datatype CHAR in a VAR statement equivalences the host-language datatype to the fixed-length ANSI datatype CHAR (Oracle8i external datatype code 96). However, when PICX=VARCHAR2, the host-language datatype is equivalenced to the variable-length datatype VARCHAR2 (code 1).

However, you can always equivalence host-language datatypes to the fixed-length ANSI datatype CHAR. Simply specify the datatype CHARF in the VAR statement. If you use CHARF, the host-language datatype is equivalenced to the fixed-length ANSI datatype CHAR even when PICX=VARCHAR2.

Guidelines

To input VARNUM or DATE values, you must use the Oracle8i internal format. Keep in mind that Oracle8i uses the internal format to output VARNUM and DATE values.

After selecting a column value into a VARNUM host variable, you can check the first byte to get the length of the value. Table 4-1 gives some examples of returned VARNUM values.

Table 4-12 VARNUM Examples
  VARNUM Value  
Decimal Value   Length Byte   Exponent Byte   Mantissa Bytes   Terminator Byte  

0  

1  

128  

n/a  

n/a  

5  

2  

193  

6  

n/a  

-5  

3  

62  

96  

102  

2767  

3  

194  

28, 68  

n/a  

-2767  

4  

61  

74, 34  

102  

100000  

2  

195  

11  

n/a  

1234567  

5  

196  

2, 24, 46, 68  

n/a  

For converting DATE values, see "Explicit Control Over DATE String Format".

If no Oracle8i external datatype suits your needs exactly, use a VARCHAR2-based or RAW-based external datatype.

RAW and LONG RAW Values

When you select a RAW or LONG RAW column value into a character host variable, Oracle8i must convert the internal binary value to an external character value. In this case, Oracle8i returns each binary byte of RAW or LONG RAW data as a pair of characters. Each character represents the hexadecimal equivalent of a nibble (half a byte). For example, Oracle8i returns the binary byte 11111111 as the pair of characters "FF". The SQL function RAWTOHEX performs the same conversion.

A conversion is also necessary when you insert a character host value into a RAW or LONG RAW column. Each pair of characters in the host variable must represent the hexadecimal equivalent of a binary byte. If a character does not represent the hexadecimal value of a nibble, Oracle8i issues an error message.

For more information about datatype conversion, see "Sample Program 4: Datatype Equivalencing".

The default assignments of External and COBOL datatypes are shown in Table 4-13:

Table 4-13 Host Variable Equivalencing
COBOL Datatype  External Datatype  Code 

PIC X...X

PIC X(n)  

CHARF  

96  

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

PIC S9...9 COMP-4

PIC S9(n) COMP-4

PIC S9...9 BINARY

PIC S9(n) BINARY  

INTEGER  

3  

COMP-1

COMP-2  

FLOAT  

4  

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  

DECIMAL  

7  

PIC 9(n) COMP

PIC 9...9 COMP  

UNSIGNED  

68  

PIC S9...9V9...9 LEADING SEPARATE

PIC S9(n)V9(n) LEADING SEPARATE  

DISPLAY  

91  

PIC 9(n)V9(9)

PIC 9...9V9...9  

UNSIGNED DISPLAY  

153  

PIC S9...9V9...9 TRAILING

PIC S9(n)V9(n) TRAILING  

OVERPUNCH TRAILING  

154  

PIC S9...9V9...9 LEADING

PIC S9(n)V9(n) LEADING  

OVERPUNCH LEADING  

172  

PIC S9...9V9...9 TRAILING SEPARATE

PIC S9(n)V9(n) TRAILING SEPARATE  

DISPLAY TRAILING  

152  

Sample Program 4: Datatype Equivalencing

After connecting to Oracle, this program creates a database table named IMAGE in the SCOTT account, then simulates the insertion of bitmap images of employee numbers into the table. Datatype equivalencing lets the program use the Oracle external datatype LONG RAW to represent the images. Later, when the user enters an employee number, the number's "bitmap" is selected from the IMAGE table and pseudo-displayed on the terminal screen.

      *****************************************************************
      * Sample Program 4:  Datatype Equivalencing                     *
      *                                                               *
      * This program simulates the storage and retrieval of bitmap    *
      * images into table IMAGE, which is created in the SCOTT        *
      * account after logging on to ORACLE.  Datatype equivalencing   *
      * allows an ORACLE external type of LONG RAW to be specified    *
      * for the programs representation of the images.                *
      *****************************************************************

       IDENTIFICATION DIVISION.
       PROGRAM-ID. DTY-EQUIV.
       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  EMP-REC-VARS.
           05  EMP-NUMBER    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.
           05  COMM-IND      PIC S9(4) COMP.

           EXEC SQL VAR SALARY IS DISPLAY(8,2) END-EXEC.
           EXEC SQL VAR COMMISSION IS DISPLAY(8,2) END-EXEC.

       01  BUFFER-VAR.
           05  BUFFER        PIC X(8192).
           EXEC SQL VAR BUFFER IS LONG RAW END-EXEC.

       01  INEMPNO           PIC S9(4) COMP.
           EXEC SQL END DECLARE SECTION END-EXEC.
           EXEC SQL INCLUDE SQLCA END-EXEC.

       01  DISPLAY-VARIABLES.
           05  D-EMP-NAME    PIC X(10).
           05  D-SALARY      PIC $Z(4)9.99.
           05  D-COMMISSION  PIC $Z(4)9.99.
           05  D-INEMPNO     PIC 9(4).
       01  REPLY             PIC X(10).
       01  INDX              PIC S9(9) COMP.
       01  PRT-QUOT          PIC S9(9) COMP.
       01  PRT-MOD           PIC S9(9) COMP.

       PROCEDURE DIVISION.

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

           PERFORM LOGON.
           DISPLAY "OK TO DROP THE IMAGE TABLE? (Y/N)  "
               WITH NO ADVANCING.

           ACCEPT REPLY.

           IF (REPLY NOT = "Y") AND (REPLY NOT = "y")
               GO TO SIGN-OFF-EXIT.
           EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC.
           EXEC SQL DROP TABLE IMAGE END-EXEC.
           DISPLAY " ".
           IF (SQLCODE = 0) DISPLAY
               "TABLE IMAGE DROPPED - CREATING NEW TABLE."
           ELSE IF (SQLCODE = -942) DISPLAY
               "TABLE IMAGE DOES NOT EXIST - CREATING NEW TABLE."
           ELSE PERFORM SQL-ERROR.
           EXEC SQL WHENEVER SQLERROR
               DO PERFORM SQL-ERROR END-EXEC.
           EXEC SQL CREATE TABLE IMAGE
               (EMPNO NUMBER(4) NOT NULL, BITMAP LONG RAW)
           END-EXEC.
           EXEC SQL DECLARE EMPCUR CURSOR FOR
               SELECT EMPNO, ENAME FROM EMP
           END-EXEC.
           EXEC SQL OPEN EMPCUR END-EXEC.
           DISPLAY " ".
           DISPLAY
             "INSERTING BITMAPS INTO IMAGE FOR ALL EMPLOYEES ...".
           DISPLAY " ".

       INSERT-LOOP.
           EXEC SQL WHENEVER NOT FOUND GOTO NOT-FOUND END-EXEC.
           EXEC SQL FETCH EMPCUR
               INTO :EMP-NUMBER, :EMP-NAME
           END-EXEC.
           MOVE EMP-NAME-ARR TO D-EMP-NAME.
           DISPLAY "EMPLOYEE ", D-EMP-NAME WITH NO ADVANCING.
           PERFORM GET-IMAGE.
           EXEC SQL INSERT INTO IMAGE
               VALUES (:EMP-NUMBER, :BUFFER)
           END-EXEC.
           DISPLAY " IS DONE!".
           MOVE SPACES TO EMP-NAME-ARR.
           GO TO INSERT-LOOP.

       NOT-FOUND.
           EXEC SQL CLOSE EMPCUR END-EXEC.
           EXEC SQL COMMIT WORK END-EXEC.
           DISPLAY " ".
           DISPLAY
             "DONE INSERTING BITMAPS.  NEXT, LET'S DISPLAY SOME.".

       DISP-LOOP.
           MOVE 0 TO INEMPNO.
           DISPLAY " ".
           DISPLAY "ENTER EMPLOYEE NUMBER (0 TO QUIT):  "
               WITH NO ADVANCING.

           ACCEPT D-INEMPNO.

           MOVE D-INEMPNO TO INEMPNO.
           IF (INEMPNO = 0)
               GO TO SIGN-OFF.
           EXEC SQL WHENEVER NOT FOUND GOTO NO-EMP END-EXEC.
           EXEC SQL SELECT EMP.EMPNO, ENAME, SAL, COMM, BITMAP
               INTO :EMP-NUMBER, :EMP-NAME, :SALARY,
                    :COMMISSION:COMM-IND, :BUFFER
               FROM EMP, IMAGE
               WHERE EMP.EMPNO = :INEMPNO
                 AND EMP.EMPNO = IMAGE.EMPNO
           END-EXEC.
           DISPLAY " ".
           PERFORM SHOW-IMAGE.
           MOVE EMP-NAME-ARR TO D-EMP-NAME.
           MOVE SALARY TO D-SALARY.
           MOVE COMMISSION TO D-COMMISSION.
           DISPLAY "EMPLOYEE ", D-EMP-NAME, " HAS SALARY ", D-SALARY
               WITH NO ADVANCING.
           IF COMM-IND = -1
               DISPLAY " AND NO COMMISSION."
           ELSE
               DISPLAY " AND COMMISSION ", D-COMMISSION, "."
           END-IF.
           MOVE SPACES TO EMP-NAME-ARR.
           GO TO DISP-LOOP.

       NO-EMP.
           DISPLAY "NOT A VALID EMPLOYEE NUMBER - TRY AGAIN.".
           GO TO DISP-LOOP.

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

       GET-IMAGE.
           PERFORM MOVE-IMAGE
               VARYING INDX FROM 1 BY 1 UNTIL INDX > 8192.

       MOVE-IMAGE.
           STRING '*' DELIMITED BY SIZE
               INTO BUFFER
               WITH POINTER INDX.
           DIVIDE 256 INTO INDX
               GIVING PRT-QUOT REMAINDER PRT-MOD.
           IF (PRT-MOD = 0) DISPLAY "." WITH NO ADVANCING.

       SHOW-IMAGE.
           PERFORM VARYING INDX FROM 1 BY 1 UNTIL INDX > 10
               DISPLAY "               *************************"
           END-PERFORM.
           DISPLAY " ".

       SIGN-OFF.
           EXEC SQL DROP TABLE IMAGE END-EXEC.
       SIGN-OFF-EXIT.
           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.




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index