Matching Oracle9i Data Types

Table of equivalents

Oracle9i uses a mix of conventional and unique native data types. The equivalent data types for OLAP DML dimensions and variables are listed in the following table.

Oracle9i	OLAP DML Dimensions	OLAP DML Variables
CHAR	TEXT [WIDTH n], ID	TEXT [WIDTH n], ID, BOOLEAN
DATE	DAY, WEEK, MONTH, QUARTER, YEAR, TEXT	DATE, TEXT
FLOAT	N/A	DECIMAL
LONG	N/A	TEXT
LONG RAW	N/A	N/A
NUMBER	N/A	INTEGER, SHORTINTEGER, DECIMAL, SHORTDECIMAL
RAW	N/A	N/A
ROWID	N/A	N/A
VARCHAR2	TEXT	TEXT

Date data

When writing OLAP DML text to a DATE column, the text must be in the default date format. You can use slashes (/) or hyphens (-) as separators, as well as spaces. If the data is in a different format, then you can use the Oracle9i TO_DATE function in your SQL INSERT command. In the following example, TODAY is a text variable containing the current date in the format of "March 16, 1995".

sql insert into shipments(Timestamp) -
     values(TO_DATE(:today, 'Month DD, YYYY')

Refer to your Oracle9i documentation for information about using the TO_DATE function.

Text data

You can retrieve the entire contents of VARCHAR2 and LONG columns into OLAP DML TEXT variables. The maximum size in Oracle9i is 2K bytes for VARCHAR2 columns and 2 gigabytes for LONG columns. Newline characters are inserted in an output host variable whenever it encounters carriage returns (ASCII code 13) or line feeds (ASCII code 10). A newline character is inserted whenever its maximum line length of 498 bytes is reached.

LONG data

To insert more than 2k bytes of text data into a LONG column, use the WIDE keyword before the name of the input host variable. With this keyword, the maximum size is 65k bytes. Longer values are truncated.

The following is the syntax of the WIDE keyword.

:WIDE input-host-variable

Data is passed with the WIDE keyword to Oracle9i as LONG data. (Otherwise, text data is passed as VARCHAR2.) Oracle9i imposes some restrictions on the LONG data type. An error will not be signaled when you violate these restrictions. However, you might get unexpected results.

Refer to the Oracle9i manuals for information about restrictions on the LONG data type.

Input host variables

The input host variable must have a TEXT data type. The following example shows the contents of an OLAP DML variable, TEXTVAR, being inserted into a LONG column, COL1 of TABLE1.

sql insert into table1(col1) values(:wide textvar)

Formula for calculating the number of characters

You can calculate the number of characters that will be sent to Oracle9i from an input host variable by using the following formula.

NUMCHARS(variable) + 2 * (NUMLINES(variable) - 1) 

For example, the following command shows the number of characters that will be sent using BIGVAR as the input host variable.

show numchars(bigvar) + 2 * (numlines(bigvar) -1)

This formula counts the extra carriage return and line feed characters that have been inserted between lines when passing the text to Oracle9i.

Related information

For more information about OLAP DML data types, search OLAP DML Reference for the "DEFINE command." For descriptions of Oracle9i data types, see the SQL Language Reference Manual or the PL/SQL User's Guide and Reference.