Progress
DataServer
for ORACLE Guide


Data Types

ORACLE data types differ from Progress data types. However, each ORACLE internal data type supported by the DataServer has at least one Progress equivalent.

The DataServer translates the ORACLE data types into Progress equivalents. When an ORACLE data type has more than one Progress equivalent, the DataServer supplies a default data type. The schema image contains the Progress data definitions for the ORACLE columns, which you can modify by using the Data Dictionary. For example, the DataServer assigns the NUMBER data type the Progress equivalent, DECIMAL. You can then change the data type from DECIMAL to INTEGER or LOGICAL. The "Modifying a Schema Image" section in "The DataServer Tutorial," explains how to change Progress data types in the schema image.

NOTE: You cannot change the data type of a stored procedure parameter. Although you can use the Data Dictionary to view the stored procedure properties in the schema holder, you cannot modify them.

ORACLE allows users to define their own data types, known as external data types. ORACLE converts these external types to an equivalent internal type. For example, a float data type maps to NUMBER. The DataServer also considers it to be a NUMBER and maps it to DECIMAL in the schema image.

Table 2–5 lists the ORACLE internal data types supported by the DataServer and their Progress equivalents. The table also shows the default equivalent supplied by the DataServer for those ORACLE data types with more than one Progress equivalent.

The only ORACLE internal data type that the DataServer does not support is ROWID. The ORACLE ROWID, however, has its programming equivalent in the Progress ROWID. See the "ROWID Function" section for more information.

Table 2–5: ORACLE and Progress Data Types 
ORACLE Data Type
Progress Equivalent
CHAR1
CHARACTER
VARCHAR22,3
CHARACTER
NUMBER
DECIMAL4,5
INTEGER
LOGICAL6
DATE7,8
DATE8
INTEGER8
LONG9
CHARACTER
RAW (limited support)10
RAW
LONG RAW10
CHARACTER

The following notes provide additional information on the ORACLE data types and their Progress equivalents:

  1. The maximum length of an ORACLE7 CHAR field is 255 bytes. The maximum length of an ORACLE8 CHAR field is 2000 bytes.
  2. The maximum length of an ORACLE7 VARCHAR2 field is 2000 bytes. The maximum length of an ORACLE8 VARCHAR2 field is 4000 bytes.
  3. The VARCHAR2 data type does not pad data with trailing spaces. However, ORACLE CHAR does pad with trailing spaces. For example, in a CHAR column 20 characters wide, the entry MA includes the two characters and 18 spaces. Your application will find the entry only if a WHERE clause searches for the string that includes MA and the 18 spaces. If the column is a VARCHAR2 column, your application will find the entry if it searches only for the two characters. The VARCHAR2 data type is more consistent with the Progress CHARACTER data type.
  4. ORACLE has only one numeric data type, which the DataServer translates to a Progress DECIMAL, INTEGER, or LOGICAL data type depending on the scale and precision of the NUMBER column. However, Progress handles the Progress INTEGER data type more efficiently than it does a DECIMAL data type. You can use the Progress Data Dictionary to change the data type from DECIMAL to INTEGER in the schema image. See the "Modifying a Schema Image" section in "The DataServer Tutorial," for instructions.
  5. Consider the local version of the ORACLE database when accessing NUMBER data. The internal radix (decimal point symbol) varies among versions. For example, some European versions expect the radix to be a comma (,) rather than a period (.). The DataServer issues an ALTER SESSION SET SEPARATOR statement, which might result in stored procedures that you call from Progress seeing a different radix separator.
  6. ORACLE does not have a LOGICAL data type. You can change the data type for a field from DECIMAL to LOGICAL in the schema holder. Progress then reads the numeric values stored in the ORACLE column, as Table 2–6 shows.
  7. Table 2–6: LOGICAL Data Type and ORACLE Equivalents 
    Progress
    ORACLE
    True
    Any non-zero value
    False
    0

    The DataServer stores the contents of a Progress LOGICAL data type in an ORACLE NUMBER column as:

    • True = 1
    • False = 0
    • If you retain values other than 1 or 0 in the ORACLE column, do not write a value to that column as a LOGICAL data type.

  8. The ORACLE DATE data type contains both date and time information. By default, in the schema image, an ORACLE date column is represented by two Progress fields: a DATE field and an INTEGER field. The DataServer follows this convention when naming the fields: column column-1. For example, an ORACLE date column named Date_Due converts to two fields named Date_Due and Date_Due-1 in the schema image. Date_Due is a DATE field and Date_Due-1 is an INTEGER field.
  9. Progress converts the time component of the ORACLE date to an INTEGER value. To convert the INTEGER value into time, use the STRING and TIME functions, as described in the Progress Language Reference.

    You can change the data-type mapping for DATE to CHARACTER in the schema image using the Data Dictionary. If you change the mapping, you must remove the column representing the time as an INTEGER. Use this feature only if you must use the time portion of an ORACLE DATE in WHERE clauses.

    NOTE: Do not include the time portion of a date field in an index.

  10. The range of ORACLE DATE are the years 4712 B.C. to 4712 A.D. The range of DATE that the DataServer supports are the years 999 B.C. to 9999. The DataServer converts all years greater than 4712 to 4712. For example, if your Progress application updates a DATE column with the year 4750, the DataServer converts it to 4712 before writing it to the ORACLE database.
  11. ORACLE allows only one LONG column per table.
  12. Progress supports RAW data types for non-Progress databases. For information about programming using the RAW data type, see the Progress Programming Handbook. For information about the specific statements and functions, see the Progress Language Reference.

The DataServer does not support the ORACLE BFILE, CLOB, or LOB data types nor any Progress SQL-92 data types.


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095