4 Datatypes and Host Variables

CHAR

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

CHARF

By default, the CHARF datatype represents 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. Oracle9i 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, Oracle9i generates an error. If the input value is all-blank, then a string of spaces is stored.

On Output. Oracle9i 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, then the original value of the variable is not overwritten.

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

CHARZ

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

DATE

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

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). Pro*COBOL also supports five additional datetime datetypes, as described in "Datetime and Interval Datatype Descriptors" .

DECIMAL

The DECIMAL datatype represents 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, the value is truncated to the declared length.

DISPLAY

The DISPLAY datatype represents 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

The FLOAT datatype represents numbers that have a fractional part or that exceed the capacity of the INTEGER datatype. FLOAT relates to the COBOL datatypes COMP-1 (4-byte floating point) and COMP-2 (8-byte floating point).

Oracle9i can represent numbers with greater precision than floating point implementations because the internal format of Oracle9i 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

The INTEGER datatype represents numbers that have no fractional part. An integer is a signed, 2-byte, 4-byte, or 8-byte binary number. (8-byte on 64-bit platforms.) 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 has a fractional part, the digits after the decimal point are truncated.

LONG

The LONG datatype represents 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

The LONG RAW datatype represents 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 Oracle9i 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

The LONG VARCHAR datatype represents 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

The LONG VARRAW datatype represents 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

The NUMBER datatype represents the internal Oracle NUMBER format which cannot be represented by a COBOL datatype.

OVER-PUNCH

OVER-PUNCH is the default signed numeric for the COBOL language. Digits are held in ASCII or EBCDIC format in radix 10, with one digit for each byte of computer storage. The sign is held in the high order nibble of one of the bytes. It is called OVER-PUNCH because the sign is "punched-over" the digit in either the first or last byte. The default sign position will be over the trailing byte. PIC S9(n)V9(m) TRAILING or PIC S9(n)V9(m) LEADING is used to specify the over-punch.

RAW

The RAW datatype represents 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 Oracle9i 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

The ROWID datatype is the database row identifier in COBOL. To support both logical and physical ROWIDs (as well as ROWIDs of non-Oracle tables) the Universal ROWID (UROWID) was defined. Use the SQL-ROWID pseudotype for this datatype (see "Universal ROWIDs").

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, Oracle9i 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, and hen 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 through 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 through 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 terminated by a LOW-VALUE character.This datatype is usually not used in Pro*COBOL.

UNSIGNED

The UNSIGNED datatype represents unsigned integers.This datatype is usually not used in Pro*COBOL.

VARCHAR

The VARCHAR datatype represents 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

The VARCHAR2 datatype represents 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 multibyte characters, its maximum length is less than n characters.

On Input. Oracle9i reads the number of bytes specified for the input host variable, strips any trailing blanks, and then stores the input value in the target database column.

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

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

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

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

Oracle9i 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, Oracle9i returns the value 1.2E08 to the host variable.

VARNUM

The VARNUM datatype is similar in format to NUMBER and is usually not used in Pro*COBOL.

VARRAW

The VARRAW datatype represents 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.

SQL Pseudocolumns and Functions

SQL recognizes the pseudocolumns listed in Table 4-4, which return specific data items.

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 listed in Table 4-5, which also return specific data items.

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

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 can 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, the sequence named partno assigns 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 roll back 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.

Example Declarations

The following example declares several host variables for later use:

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

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 preceding 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 Oracle9i reserved word. See Appendix B, "Reserved Words, Keywords, and Namespaces" for a list of reserved words and keywords.

On Input

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

On Output

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

Use in Where Clauses

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. 

Avoid Error Messages

If you SELECT or FETCH a NULL into a host variable that has no indicator, Oracle9i 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, Oracle9i 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 Multibyte NCHAR Variables

Indicator variables for multibyte 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 multibyte 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.

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.

Host input variables for multibyte Globalization Support 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.

Make sure that the input value is not trailed by extraneous characters. 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.

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.

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

PIC X and NCHAR Column

By default, PIC X variables are converted to the server-side Database Character Set so that these variables can be used with CHAR columns. Therefore, when you use the PIC X variables with NCHAR columns, there can be some data loss. To avoid this, use the following command to set the character set form used by PIC X variables to NCHAR:

charset_picx=nchar_charset

PIC N and CHAR column

By default, PIC N variables are converted to the server-side National Character Set so that these variables can be used with NCHAR columns. Therefore, when you use the PIC N variables with NCHAR columns, there may be some performance impact. To avoid this, use the following command to set the character set form used by PIC N variables to CHAR:

charset_picn=db_charset

For more information on CHARSET_PICX and CHARSET_PICN, see section Using Pro*COBOL Precompiler Options.

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, Oracle9i null-terminates the value for you.

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

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