Release 8.0
A58232-01
 Library |
 Product |
 Contents |
 Index |
| Pro*COBOL Precompiler Programmer's Guide Release 8.0 A58232-01 |
|
Advanced Pro*COBOL techniques are presented. Topics are:
Oracle8 recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle8 stores data in database columns. Oracle8 also uses internal datatypes to represent database pseudo-columns. An external datatype specifies how data is stored in a host variable.
For values stored in database columns, Oracle8 uses the following internal datatypes:
These internal datatypes can be quite different from COBOL datatypes. For example, COBOL has no equivalent to the NUMBER datatype, which was specially designed for portability and high precision.
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.
Use this datatype to store NLS (National Language Support) strings. See "National Language Support" on page 4-30. 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" on page E-3 for a discussion of semantics checking. See "DECLARE TABLE (Oracle Embedded SQL Directive)" on page F-20 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.
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, Oracle8 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.
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.
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 Oracle8 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.
With Trusted Oracle, you use the MLSLABEL datatype to store variable-length, binary operating system labels. Trusted Oracle uses labels to control access to data. For more information, see the Trusted Oracle documentation.
You can use the MLSLABEL datatype to define a database column. However, with standard Oracle8, such columns can store only nulls. With Trusted Oracle, you can insert any valid operating system label into a column of type MLSLABEL. If the label is in text format, Trusted Oracle converts it to a binary value automatically. The text string can be up to 255 bytes long. However, the internal length of an MLSLABEL value is between 2 and 5 bytes.
With Trusted Oracle, you can also select values from a MLSLABEL column into a character variable. Trusted Oracle converts the internal binary value to a VARCHAR2 value automatically.
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, Oracle8 does extra integrity checks before storing the data. If a value exceeds the precision, Oracle8 issues an error message; if a value exceeds the scale, Oracle8 rounds the value.
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 Oracle8.
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 Oracle8 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.
Internally, every table in an Oracle8 database has a pseudo-column named ROWID, which stores binary values called rowids. ROWIDs uniquely identify rows and provide the fastest way to access particular rows.
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.
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" on page 4-30. 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" on page E-3 for a discussion of semantics checking. See "DECLARE TABLE (Oracle Embedded SQL Directive)" on page F-20 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 recognizes the pseudo-columns in Table 4-2, which return specific data items:
| Pseudo-column | Internal Datatype |
|---|---|
|
CURRVAL |
NUMBER |
|
LEVEL |
NUMBER |
|
NEXTVAL |
NUMBER |
|
ROWID |
ROWID |
|
ROWLABEL |
MLSLABEL |
|
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 pseudo-column values from a dummy table.
In addition, SQL recognizes the parameterless functions in Table 4-3, which also return specific data items:
| 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 pseudo-columns and functions follow. For details, see the Oracle8 Server 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.
ROWID returns a row address in hexadecimal.
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.
SQL also recognizes the special column ROWLABEL, which Trusted Oracle creates for every database table. Like other columns, ROWLABEL can be referenced in SQL statements. However, with standard Oracle, ROWLABEL returns a null. With Trusted Oracle, ROWLABEL returns the operating system label for a row.
A common use of ROWLABEL is to filter query results. For example, the following statement counts only those rows with a security level higher than "unclassified":
EXEC SQL SELECT COUNT(*) INTO :HEAD-COUNT FROM EMP WHERE ROWLABEL > 'UNCLASSIFIED' END-EXEC.
For more information about the ROWLABEL column, see the Trusted Oracle documentation.
As the table below shows, the external datatypes include all the internal datatypes plus several datatypes found in other supported host languages. For example, the STRING external datatype refers to a C null-terminated string. You use the datatype names in datatype equivalencing, and you use the datatype codes in dynamic SQL Method 4.
| Name | Code | Description |
|---|---|---|
|
CHAR |
1 96 |
<= 65535-byte, variable-length character string (1) <= 65535-byte, fixed-length character string (1) |
|
CHARF |
96 |
<= 65535-byte, fixed-length character string |
|
CHARZ |
97 |
<= 65535-byte, fixed-length, null-terminated string (2) |
|
DATE |
12 |
7-byte, fixed-length date/time value |
|
DECIMAL |
7 |
COBOL packed decimal |
|
DISPLAY |
91 |
COBOL numeric character string |
|
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 |
|
MLSLABEL |
106 |
2..5-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:
CHAR behavior depends on the settings of the option PICX. See "PICX" on page 7-32.
By default, Oracle8 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 page 7-32.
On Input. Oracle8 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, Oracle8 generates an error. If the input value is all-blank, Oracle8 treats it like a character value.
On Output. Oracle8 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, Oracle8 fills the host variable with blanks.
If the output value is longer than the declared length of the host variable, Oracle8 truncates the value before assigning it to the host variable. If an indicator variable is available, Oracle8 sets it to the original length of the output value.
Use the CHARZ datatype to store fixed-length, null-terminated character strings. On most platforms, the maximum length of a CHARZ value is 65,535 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. Oracle8 appends a null terminator to the output value, which is also blank-padded if necessary.
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.
| Byte |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
|---|---|---|---|---|---|---|---|
| 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).
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, Oracle8 fills the host variable with asterisks.
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).
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.
Oracle8 can represent numbers with greater precision than floating point implementations because the internal format of Oracle8 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.
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 system-dependent. You must specify a length for input and output host variables. On output, if the column value is a floating point number, Oracle8 truncates the fractional part.
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).
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 Oracle8 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.
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.
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.
Use the MLSLABEL datatype to store variable-length, binary operating system labels. Trusted Oracle uses labels to control access to data. For more information, see your Trusted Oracle documentation, You can use the MLSLABEL datatype to define a column. However, with standard Oracle, such columns can store nulls only. With Trusted Oracle, you can insert any valid operating system label into a column of type MLSLABEL.
On Input. Trusted Oracle translates the input value into a binary label, which must be a valid operating system label. If the label is invalid, Trusted Oracle issues an error message. If the label is valid, Trusted Oracle stores it in the target database column.
On Output. Trusted Oracle converts the binary label to a character string, which can be of type CHAR, CHARZ, STRING, VARCHAR, or VARCHAR2.
Use the NUMBER datatype to store fixed or floating point Oracle8 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 Oracle8. 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. Oracle8 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.
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 Oracle8 assumes nothing about the meaning of RAW data and does no character set conversions when you transmit RAW data from one system to another.
Use the ROWID datatype to store binary rowids in 18-byte fixed-length fields. The field size is system-specific. So, check your system-specific Oracle8 manuals.
The ROWID in Oracle 8 has a format of 'OOOOOOFFFBBBBBBSSS' which is an
18 character string where:
OOOOOO = is a base 64 encoding of the 32-bit data object number.
(Data object number was introduced in 8.0 to track versions of the same segment because certain operations can change the version. It is used to discover stale ROWIDs and stale undo records)
FFF = is a base 64 encoding of the relative file number
BBBBBB = is a base 64 encoding of the block number
SSS = is a base 64 encoding of the slot (row) number
This format is called the extended ROWID character format.
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, Oracle8 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" on page 10-14.
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.
The STRING datatype is like the VARCHAR2 datatype, except that a STRING value is always null-terminated.
On Input. Oracle8 uses the specified length to limit the scan for a null terminator. If a null terminator is not found, Oracle8 generates an error. If you do not specify a length, Oracle8 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, Oracle8 inserts a null unless the column is defined as NOT NULL. An all-blank or null-terminated value is stored intact.
On Output. Oracle8 appends a null byte to the last character returned. If the string length exceeds the specified length, Oracle8 truncates the output value and appends a null byte.
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, Oracle8 truncates the fractional part.
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.
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. Oracle8 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, Oracle8 generates an error. If the input value is all-blank, Oracle8 treats it like a null.
Oracle8 can convert a character value to a NUMBER column value if the character value represents a valid number. Otherwise, Oracle8 generates an error.
On Output. Oracle8 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, Oracle8 fills the host variable with blanks.
If the output value is longer than the declared length of the host variable, Oracle8 truncates the value before assigning it to the host variable. If an indicator variable is available, Oracle8 sets it to the original length of the output value.
Oracle8 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, Oracle8 returns the value "1.2E08" to the host variable.
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 Oracle8. 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.
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.
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 Oracle8. Oracle8 uses the codes to convert between internal and external datatypes.
Before assigning a SELECTed column value to an output host variable, Oracle8 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, Oracle8 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 Oracle8 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 Oracle8 Application Developer's Guide.
The following table shows the supported conversions between internal and external datatypes.
When you select a DATE column value into a character host variable, Oracle8 must convert the internal binary value to an external character value. So, Oracle8 implicitly calls the SQL function TO_CHAR, which returns a character string in the default date format. The default is set by the Oracle8 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. Oracle8 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:
`fmt' is a date format model, such as 'Month dd, yyyy'. See the Oracle8 SQL Reference Manual 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 lets you control the way Oracle8 interprets input data and the way Oracle8 formats output data. You can equivalence supported COBOL datatypes to Oracle8 external datatypes on a variable-by-variable basis.
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 Oracle8 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.
Also, you can use datatype equivalencing to override default datatype conversions. Unless NLS parameters in the Oracle8 initialization file specify otherwise, if you select a DATE column value into a character host variable, Oracle8 returns a 9-byte string formatted as follows:
DD-MON-YY
However, if you equivalence the character host variable to the DATE external datatype, Oracle8 returns a 7-byte value in the internal format.
By default, Pro*COBOL assigns a specific external datatype to every host variable. You can override the default assignments by equivalencing host variables to Oracle8 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 | is 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-8 shows which parameters to use with each external datatype.
The CONVBUFSZ clause is explained in "CONVBUFSZ Clause in VAR Statement" on page 4-23.
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)" on page F-53.
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 Oracle8 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).
The EXEC SQL VAR statement can have an optional CONVBUFSZ clause. You specify the size, in bytes, of the buffer in the Oracle8 runtime library used to perform conversion of the specified host variable between character sets.
When you have not used the CONVBUFSZ clause, the Oracle8 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.
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, Oracle8 null-terminates the value for you.
| 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 |
|
MLSLABEL |
required |
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 |
You can use the datatype specifier CHARF in VAR statements to equivalence COBOL datatypes to the fixed-length ANSI datatype CHAR.
When PICX=CHARF, specifying the datatype CHAR in a VAR statement equivalences the host-language datatype to the fixed-length ANSI datatype CHAR (Oracle8 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.
To input VARNUM or DATE values, you must use the Oracle8 internal format. Keep in mind that Oracle8 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-9 gives some examples of returned VARNUM values.
For converting DATE values, see "Explicit Control Over DATE String Format" on page 4-19.
If no Oracle8 external datatype suits your needs exactly, use a VARCHAR2-based or RAW-based external datatype.
When you select a RAW or LONG RAW column value into a character host variable, Oracle8 must convert the internal binary value to an external character value. In this case, Oracle8 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, Oracle8 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, Oracle8 issues the following error message:
ORA-01465: invalid hex number
For more information about datatype conversion, see "Sample Program 4: Datatype Equivalencing" on page 5-19.
See "Sample Program 4: Datatype Equivalencing" on page 5-19
The default assignments of External and COBOL datatypes are shown in Table 4-10
Pro*COBOL treats a PL/SQL block like a single embedded SQL statement. So, you can place a PL/SQL block anywhere in a host program that you can place a SQL statement.
To embed a PL/SQL block in your host program, declare the variables to be shared with PL/SQL and bracket the PL/SQL block with the EXEC SQL EXECUTE and END-EXEC keywords.
Inside a PL/SQL block, host variables are global to the entire block and can be used anywhere a PL/SQL variable is allowed. Like host variables in a SQL statement, host variables in a PL/SQL block must be prefixed with a colon. The colon sets host variables apart from PL/SQL variables and database objects.
When entering a PL/SQL block, Oracle8 automatically checks the length fields of VARCHAR host variables, so you must set the length fields before the block is entered. For input variables, set the length field to the length of the value stored in the string field. For output variables, set the length field to the maximum length allowed by the string field.
When NLS_LOCAL=YES, multi-byte NCHAR features are not supported within a PL/SQL block. These features include N-quoted character literals (see Chapter 4, "Advanced Pro*COBOL Programs") and fixed-length character variables.
In a PL/SQL block, you cannot refer to an indicator variable by itself; it must be appended to its associated host variable. Also, if you refer to a host variable with its indicator variable, you must always refer to it that way in the same block.
When entering a block, if an indicator variable has a value of -1, PL/SQL automatically assigns a null to the host variable. When exiting the block, if a host variable is null, PL/SQL automatically assigns a value of -1 to the indicator variable.
PL/SQL does not raise an exception when a truncated string value is assigned to a host variable. However, if you use an indicator variable, PL/SQL sets it to the original length of the string.
You must specify SQLCHECK=SEMANTICS when precompiling a program with an embedded PL/SQL block. You must also use the USERID option. For more information, see Chapter 7, "Running the Pro*COBOL Precompiler".
Although the widely-used 7- 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 Oracle8 deal with such dissimilar languages?
Oracle8 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 Oracle8 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 Oracle8 initialization file. Table 4-11 shows what each NLS parameter specifies.
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 Oracle8 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 Oracle8 Application Developer's Guide.
Pro*COBOL extends support for multi-byte NLS character sets through
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.
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 7, "Running the Pro*COBOL Precompiler".
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" on page 3-39.
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.
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" on page 5-3.
Pro*COBOL let s you embed OCI calls in your program. Just take the following steps:
That way, Pro*COBOL and the OCI "know" that they are working together. However, there is no sharing of Oracle8 cursors.
You need not worry about declaring the OCI Host Data Area (HDA) because the Oracle8 run-time library manages connections and maintains the HDA for you.
You set up the LDA by issuing the OCI call
CALL "SQLLDA" USING LDA.
where LDA identifies the LDA data structure. See the Oracle Call Interface Programmer's Guide. If the CONNECT statement fails, the LDA-RC field in the lda is set to 1012 to indicate the error.
A call to SQLLDA sets up an LDA for the connection used by the most recently executed SQL statement. To set up the different LDAs needed for additional connections, just call SQLLDA with a different lda after each CONNECT. In the following example, you connect to two non-default databases concurrently:
EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 USERNAME PIC X(10) . 01 PASSWORD PIC X(10) . 01 DB-STRING1 PIC X(20) . 01 DB-STRING2 PIC X(20) . EXEC SQL END DECLARE SECTION END-EXEC. ... * -- Field sizes in LDA are system-dependent. 01 LDA1. 02 LDA1-V2RC PIC S9(4) COMP. 02 FILLER PIC X(10). 02 LDA1-RC PIC S9(4) COMP. 02 FILLER PIC X(50). 01 LDA2. 02 LDA2-V2RC PIC S9(4) COMP. 02 FILLER PIC X(10). 02 LDA2-RC PIC S9(4) COMP. 02 FILLER PIC X(50). ... MOVE 'SCOTT' TO USERNAME. MOVE 'TIGER' TO PASSWORD. MOVE 'D:NEWYORK-NONDEF1' TO DB-STRING1. MOVE 'D:CHICAGO-NONDEF2' TO DB-STRING2. ... * -- give each database connection a unique name EXEC SQL DECLARE db_name1 DATABASE END-EXEC. EXEC SQL DECLARE db_name2 DATABASE END-EXEC. ... * -- connect to first non-default database EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD AT db_name1 USING :DB-STRING1 END-EXEC. * -- set up first LDA for OCI use CALL 'SQLLDA' USING LDA1. * -- connect to second non-default database EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWORD AT db_name2 USING :DB-STRING2 END-EXEC. * -- set up second LDA for OCI use CALL 'SQLLDA' USING LDA2.
Remember, do not declare db_name1 and db_name2 because they are not host variables. You use them only to name the default databases at the two non-default nodes so that later SQL statements can refer to the databases by name.
X/Open applications run in a distributed transaction processing (DTP) environment. In an abstract model, an X/Open application calls on resource managers (RMs) to provide a variety of services. For example, a database resource manager provides access to data in a database. Resource managers interact with a transaction manager (TM), which controls all transactions for the application.
Figure 4-1 shows one way that components of the DTP model can interact to provide efficient access to data in an Oracle8 database. The DTP model specifies the XA interface between resource managers and the transaction manager. Oracle supplies an XA-compliant library, which you must link to your X/Open application. Also, you must specify the native interface between your application program and the resource managers.
The DTP model that specifies how a transaction manager and resource managers interact with an application program is described in the X/Open guide Distributed Transaction Processing Reference Model and related publications, which you can obtain by writing to
X/Open Company Ltd.
1010 El Camino Real, Suite 380
Menlo Park, CA 94025
For instructions on using the XA interface, see your Transaction Processing (TP) Monitor user's guide.
You can use Pro*COBOL to develop applications that comply with the X/Open standards. However, you must meet the following requirements.
The X/Open application does not establish and maintain connections to a database. Instead, the transaction manager and the XA interface, which is supplied by Oracle, handle database connections and disconnections transparently. So, normally an X/Open-compliant application does not execute CONNECT statements.
The X/Open application must not execute statements such as COMMIT, ROLLBACK, SAVEPOINT, and SET TRANSACTION that affect the state of global transactions. For example, the application must not execute the COMMIT statement because the transaction manager handles commits. Also, the application must not execute SQL data definition statements such as CREATE, ALTER, and RENAME because they issue an implicit commit.
The application can execute an internal ROLLBACK statement if it detects an error that prevents further SQL operations. However, this might change in later versions of the XA interface.
If you want your X/Open application to issue OCI calls, you must use the run-time library routine SQLLD2, which sets up an LDA for a specified connection established through the XA interface. For a description of the SQLLD2 call, see the Oracle Call Interface Programmer's Guide. Note that OCOM, OCON, OCOF, ORLON, OLON, OLOG, and OLOGOF cannot be issued by an X/Open application.
To get XA functionality, you must link the XA library to your X/Open application object modules. For instructions, see your system-specific Oracle8 manuals.
