skip to main content
Schema Management : Schema Objects : Columns Catalog Table: OA_COLUMNS : Specifying Data Types of Columns
 

Specifying Data Types of Columns

The data type of each column must be specified in the DATA_TYPE and TYPE_NAME columns of the OA_COLUMNS table. The TYPE_NAME is included to allow the client applications to query the schema and obtain a readable value for the data type. The DATA_TYPE field contains an integer representing the type and is used by the table owner for processing. The data type is the type exposed to the client and has nothing to do with how the data is actually stored in the database.
In the following table, the SQL Type Name contains the valid values for the TYPE_NAME field and Data Type contains the valid values for the DATA_TYPE field. Your IP may expose everything as a character string since this is the final output of reporting and GUI applications.
 
Table 118: Data Type Name to Number Mapping  
SQL Type Name
Data Type
Description
BIGINT
-5
Signed 64-bit integer
BINARY
-2
Binary data of fixed length n (1 <= n <= 255)
BIT
-7
Single bit binary data (0 or 1)
CHAR
1
Character string of fixed string length n (1<= n<=254).
DATE
9
Date containing year, month and day
DOUBLE
8
Signed, approximate, numeric value with a mantissa precision 15 (zero or absolute value 10-308 to 10308)
FLOAT
6
Signed, approximate, numeric value with a mantissa precision 15 (zero or absolute value 10-308 to 10308)
INTEGER
4
Exact numeric value with precision 10 and scale 0. (signed: -231 <=n <=231-1, unsigned:0<=n<=232-1).
LONGVARBINARY
-4
Variable length binary data of any size < 2 GB.
LONGVARCHAR
-1
Variable length character string up to 2 GB in length.
NUMERIC
2
Signed, exact, numeric value with a precision p and scale s (1<=p<=40, 0<=s<=p).
REAL
7
Signed, approximate, numeric value with a mantissa precision 7 (zero or absolute value 10-38 to 1038)
SMALLINT
5
Exact numeric value with precision 5 and scale 0. (signed: -32,768 <=n <=32,767, unsigned: 0<=n<=65,535).
TIME
10
Time containing hour, minute and seconds
TIMESTAMP
11
Date/time data.
TINYINT
-6
Exact numeric value with precision 3 and scale 0. (signed: -128 <=n<=127, unsigned: 0<=n<=255).
VARBINARY
-3
Variable length binary data with maximum size N as defined in the schema definition. Optimized for N <=4 KB.
VARCHAR
12
Variable length character string with maximum size N as defined in the schema definition. Optimized for N <= 4 KB.
WCHAR
-8
Unicode character string of fixed length n (1<= n<=254).
WLONGVARCHAR
-10
Variable length Unicode string of any size < 2 GB.
WVARCHAR
-9
Variable length Unicode string with maximum size N as defined in the schema definition. Optimized for N <= 4 KB.