Oracle data types supported in ArcGIS
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 access database tables from ArcGIS, you can work with specific data types. When accessing the database table through the Database Connections node in the Catalog tree or through a query layer in ArcMap, ArcGIS filters out any unsupported data types. If you access your database table directly, unsupported data types won't be displayed in the ArcGIS interface and you cannot edit them through ArcGIS. Similarly, when you copy tables containing unsupported data types with ArcGIS, it will only copy the supported data types; when you paste the table to another database or a geodatabase, the unsupported data type columns will not be present.
When you create a feature class or table in ArcGIS or add a column to an existing table or feature class using ArcGIS, there are 12 possible ArcGIS data types that can be assigned to a field. Database data types that don't correspond to these types cannot be used directly in ArcGIS client applications.
The first column lists the ArcGIS data types. The second column lists the Oracle data type that will be created in the database. The third column shows what other Oracle data types (if any) map to the ArcGIS data type when viewed in ArcGIS.
|
ArcGIS data types |
Oracle data types created | Other Oracle data types that can be viewed |
Notes |
|---|---|---|---|
|
BLOB |
BLOB | ||
|
DATE |
DATE | ||
|
DOUBLE |
NUMBER(38,8) | NUMBER(p,s) |
The precision and scale specified in ArcGIS can affect the resultant data type created in the database. See ArcGIS field data types for more information. |
FLOAT | NUMBER(38,8) | NUMBER(p,s) | The precision and scale specified in ArcGIS can affect the resultant data type created in the database. See ArcGIS field data types for more information. |
|
GEOMETRY |
ST_GEOMETRY, NUMBER(38), or SDO_GEOMETRY | The Oracle data type that gets created depends on the geometry storage specified when the feature class is created. Compressed binary or well-known binary (geodatabases only) = NUMBER(38); Oracle Spatial = SDO_GEOMETRY; spatial type = ST_GEOMETRY. ST_Geometry is a superclass. When you create feature classes in ArcGIS for Desktop, 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. To use ST_Geometry in a database, you must install it. See Add the ST_Geometry type to an Oracle database for information. |
|
GLOBAL ID | CHAR or NCHAR (UUID LEN) | Only supported in geodatabases. 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. | |
|
GUID |
CHAR or NCHAR (UUID LEN) |
The unique identifier field will be created as NCHAR in a geodatabase if the configuration keyword with which you specified the table's creation had the parameter UNICODE_STRING set to TRUE. |
|
|
LONG INTEGER |
NUMBER(38) | NUMBER(n) |
The value n can be in the range of 5 to 10. If created with ArcGIS for Desktop or ArcObjects and precision is set to 0, a NUMBER(38) is created in the database. Otherwise, the precision specified is used. |
|
OBJECT ID |
NUMBER(38) when created in an enterprise geodatabase NUMBER(38) with sequence and trigger when created in a database |
The ArcGIS type ObjectID is the registered row ID column for the table (or feature class.) Only one may exist per table. |
|
|
RASTER |
BLOB, LONG RAW, SDO_GEORASTER, or ST_RASTER |
Rasters are only supported in geodatabases. Which Oracle data type is created depends on the DBTUNE configuration keyword used when creating the raster catalog, raster dataset, or mosaic dataset. |
|
|
SHORT INTEGER |
NUMBER(5) | NUMBER(n) |
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 for Desktop, n = 5. This allows you to store short integers that fall within the allowable range. |
|
TEXT |
VARCHAR2, CLOB, NVARCHAR2, or NCLOB | A VARCHAR2 field is created if the database is not set to use Unicode encoding. If you set the text field size larger than 4,000 and the database is set to use Unicode encoding, the Oracle data type will be CLOB. The field will be created as NVARCHAR2 if the database is set to use Unicode encoding. (This is the default setting for geodatabases in Oracle.) If you set the text field size larger than 2,000 and the database is set to use Unicode encoding, the Oracle data type will be NCLOB. |