A comparison of ArcGIS, ArcSDE, and Oracle data types
When you create a table or add a column to a table in the database, columns are created as a specific data type. Data types are classifications that identify possible values for and operations that can be done on the data, as well as the way the data in that column is stored in the database.
When you import data of one type into a column of another data type, you need to understand what the equivalent data types are between ArcSDE and your database management system (DBMS) because it can impact data content. Also, when creating new datasets in ArcGIS, it is helpful to know the equivalent data types between ArcGIS and your DBMS.
When you create a feature class or table in ArcGIS, there are 11 possible data types you can assign to a field. That means there are some data types that can only be created outside ArcGIS Desktop.
|
ArcSDE data type |
Oracle data type |
ArcGIS data type |
Notes |
|---|---|---|---|
|
SE_STRING_TYPE |
VARCHAR2 |
TEXT |
The field will be created as VARCHAR2 if the configuration keyword with which you specified the table's creation had the parameter UNICODE_STRING set to FALSE. If you set the text field size larger than 4,000 and the configuration keyword with which you specified the table's creation had the parameter UNICODE_STRING set to FALSE when you create the field in ArcGIS Desktop, the Oracle data type will be CLOB. |
|
SE_NSTRING_TYPE |
NVARCHAR2 |
TEXT |
The field will be created as NVARCHAR2 if the configuration keyword with which you specified the table's creation had the parameter UNICODE_STRING set to TRUE. If you set the text field size larger than 2,000 and the configuration keyword with which you specified the table's creation had the parameter UNICODE_STRING set to TRUE when you create the field in ArcGIS Desktop, the Oracle data type will be NCLOB. |
|
SE_INT16_TYPE (SE_SMALLINT_TYPE) |
NUMBER(n) |
SHORT INTEGER |
The value n can be in the range of 1 to 5. However, short integer columns can only store values that fall in the range -32,768 to 32,767. Even if the precision on the number is 5, you cannot store a number greater than 32,767 or less than -32,768 in a short integer column. When created with ArcGIS Desktop, n=5. This allows you to store short integers that fall within the allowable range. |
|
SE_INT32_TYPE (SE_INTEGER_TYPE) |
NUMBER(n) |
LONG INTEGER OBJECTID |
The value n can be in the range of 5 to 10. However, if a long integer is created with the sdetable -o create operation or ArcGIS Desktop and no precision is specified, NUMBER(38) is created in the database. |
|
SE_INT64_TYPE |
NUMBER(n) |
na |
The value n can be in the range of 10 to 38. The server configuration parameter INT64TYPES must be TRUE to create columns with this data type. |
|
SE_FLOAT32_TYPE (SE_FLOAT_TYPE) |
NUMBER(n,m) |
na |
The value n can be in the range of 1 to 7; m is 127 or less. |
|
SE_FLOAT64_TYPE (SE_DOUBLE_TYPE) |
NUMBER(n,m) |
na |
The value n can be in the range of 7 to 38; m is 127 or less. |
|
SE_DATE_TYPE |
DATE |
DATE |
|
|
SE_UUID_TYPE |
CHAR or NCHAR (UUID LEN) |
GUID or Global ID |
The unique identifier field will be created as NCHAR if the configuration keyword with which you specified the table's creation had the parameter UNICODE_STRING set to TRUE. |
|
SE_BLOB_TYPE |
BLOB |
BLOB |
|
|
SE_SHAPE_TYPE |
ST_GEOMETRY*, NUMBER(38), or SDO_GEOMETRY |
GEOMETRY |
Oracle data type depends on the geometry storage specified for the layer. Compressed binary or well-known binary = NUMBER(38); Oracle Spatial = SDO_GEOMETRY; Spatial Type = ST_GEOMETRY. |
|
SE_XML_TYPE |
SDE_XML or Oracle XML DB |
na |
XML columns are not exposed in ArcGIS Desktop. |
|
SE_RASTER_TYPE |
BLOB, LONG RAW, SDO_GEORASTER, or ST_RASTER |
RASTER |
Oracle data type depends on the raster storage specified in the DBTUNE table. |
|
DECIMAL(31,8) |
FLOAT |
||
|
DECIMAL(31,8) |
DOUBLE |
||
SE_CLOB_TYPE | CLOB | TEXT | If you set the text field size larger than 4,000 and the configuration keyword with which you specified the table's creation had the parameter UNICODE_STRING set to FALSE when you create the field in ArcGIS Desktop, the Oracle data type will be CLOB. |
|
SE_NCLOB_TYPE |
NCLOB |
TEXT |
If you set the text field size larger than 2,000 and the configuration keyword with which you specified the table's creation had the parameter UNICODE_STRING set to TRUE when you create the field in ArcGIS Desktop, the Oracle data type will be NCLOB. |
*ST_Geometry is a superclass. When you create feature classes in ArcGIS Deksktop, ST_Geometry is always used. If you create a spatial column using SQL, the actual data subtype created (such as ST_Multilinestring or ST_Point) depends on what subtype you specify.