AcuSQL supports most Oracle data types, but it does not currently support the following: NCHAR, NCLOB, BLOB, BFILE, RAW, and LONG RAW.
The following table describes data type compatibility for Oracle and AcuSQL.
| SQL Type
| COBOL Type
| Description
|
| CHAR(n)
| 01 name PIC X(n).
| Fixed-length character string
|
| DATE
| 01 name PIC X(n).
| n-byte character string that can contain date in a specified format**
|
| NUMBER(p,n)
| 01 name PIC S9(m)V9(n) COMP-3.
| Packed decimal
(where m = p - n) and p denotes the precision (in digits) |
| DOUBLE PRECISION
| 01 name USAGE IS DOUBLE.
| Double precision floating bit number (126 bit precision)
|
| FLOAT(p)
| 01 name USAGE IS FLOAT.
| p<64 (p denotes precision in bits).
|
| INTEGER
| 01 name PIC S9(9) COMP-5.
| 32-bit signed integer
|
| LONG(n)
| 01 name.
03 name-length PIC S9(4) COMP-5. 03 name-name PIC X(n). | A variable length character string maintained for backward compatibility. Not
useful with AcuSQL, because AcuSQL does not support CLOB and NCLOB. Use
VARCHAR2 instead.
|
| REAL
| 01 name USAGE IS FLOAT.
| Floating point number with 63 bits precision
|
| VARCHAR(n)
| 01 name.
03 name-length PIC S9(4) COMP-5. 03 name-name PIC X(n). | Variable-length character string*
|
| VARCHAR2(n)
| 01 name.
03 name-length PIC S9(4) COMP-5. 03 name-name PIC X(n). | Variable-length character string |
**The DATE type in Oracle is a timestamp containing both time and date information. In order to use it this way, you must alter the current SQL session set in your COBOL code using, for example:
EXEC SQL ALTER SESSION SET NLS_DATE_FORMAT='MM/DD/YYYY HH24:MI:SS' END-EXEC
Thereafter, you can insert date and time in a typical fashion:
EXEC SQL
INSERT INTO TDATE ('12/21/1999 12:13:14')
END-EXEC
Where TDATE is a SQL table containing a single column of type DATE.
When a date record is retrieved from the Oracle database, it is returned in a form that corresponds to the form of the server-side NLS_DATE_FORMAT variable. In order to set the preferred date format in the Oracle server, you must edit the NLS_DATE_FORMAT line in the initialization file for the Oracle server. The procedure for editing this file is platform specific. Refer to your Oracle administration documentation for instructions.
Oracle Data Type Conversion
Oracle lets you define columns in tables using ANSI/ISO, DB2, and SQL/DS data types, and Oracle internally converts such data types to their corresponding Oracle data types. The following table shows how ANSI data types map to their corresponding Oracle counterparts.
| ANSI SQL Data Type
| Oracle Data Type
|
| CHARACTER (n), CHAR (n)
| CHAR (n)
|
| NUMERIC (p,s), DECIMAL (p,s), DEC (p,s)
| NUMBER (p,s)
|
| INTEGER, INT, SMALLINT
| NUMBER (38)
|
| FLOAT (p)
| FLOAT (p)
|
| REAL
| FLOAT (63)
|
| DOUBLE PRECISION
| DOUBLE PRECISION
|
| CHARACTER VARYING(n), CHAR VARYING(n)
| VARCHAR2 (n)
|
| DB2 or SQL/DS Data Type
| Oracle Data Type
|
| CHARACTER (n)
| CHAR (n)
|
| VARCHAR (n)
| VARCHAR2 (n)
|
| LONG VARCHAR
| LONG
|
| DECIMAL (p,s)
| NUMBER (p,s)
|
| INTEGER, SMALLINT
| NUMBER (38)
|
| FLOAT (p)
| FLOAT (p)
|
| DATE
| DATE
|