Oracle data types supported in ArcGIS

When you create a table or add a column to a table in a database, you define a specific data type for the column. Data types determine the following:

  • What values you can store in the column
  • What operations you can use on the data in that column
  • How the data in that column is stored in the database

ArcGIS works with specific data types. When you access a database table through a Database Connection, query layer, or web service, ArcGIS filters out any unsupported data types. ArcGIS does not display unsupported data types, and you cannot edit unsupported data types through ArcGIS. Similarly, when you use ArcGIS to copy and paste tables containing unsupported data types from one database to another, ArcGIS only pastes columns that use a supported data type.

The first column in the following table lists the ArcGIS data types. The second column lists the PostgreSQL data type that ArcGIS creates. The third column shows what other PostgreSQL data types, if any, map to the ArcGIS data type when you view a table that you created outside ArcGIS (not registered with the geodatabase). The last column provides additional information when needed.

ArcGIS data typeOracle data types createdOther Oracle data type that can be viewedNotes

Big integer

number(p)

number(p)

The precision (p) value can be in the range of 11 to 18.

If you do not specify a precision when you create a big integer field in ArcGIS software or ArcObjects SDK, an Oracle number(38) field is created.

Blob

BLOB

Date

timestamp

Date only

Not applicable

Not supported

Double

number(38,8)

number(p,s)

The precision (p) and scale (s) specified in ArcGIS can affect the resultant data type. See ArcGIS field data types for more information.

Float

number(38,8)

number(p,s)

The precision (p) and scale (s) specified in ArcGIS can affect the resultant data type. See ArcGIS field data types for more information.

Geometry

ST_Geometry or SDO_Geometry

The Oracle data type that gets created depends on the geometry storage specified when the feature class is created. Oracle Spatial = SDO_Geometry or spatial type = ST_Geometry.

To use ST_Geometry in a database (not geodatabase), you must install it.

Note:

The following geometry storage types have been deprecated in ArcGIS AllSource:

  • SDEBINARY
  • SDELOB
  • WKB_GEOMETRY

If present in the geodatabase, these appear as number data types in the database.

You cannot create feature classes that use these storage types. Currently, you can view feature classes that use these geometry storage types, but this functionality will be removed in a future release. Use the Migrate Storage geoprocessing tool to migrate feature classes to supported data types to ensure you can continue to access the data.

Global ID

char or nchar (UUID len)

Supported in geodatabases only.

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(10)

number(p)

The precision (p) value can be in the range of 6 to 10.

If created with ArcGIS software or ArcObjects SDK and precision is not set, a number(38) is created in the database; otherwise, the precision specified is used.

The precision can affect the resultant ArcGIS data type. See ArcGIS field data types for more information.

Object ID

number(38) when created in an enterprise geodatabase

number(38) with sequence when you register an existing database table with the geodatabase.

number(38) generated always as identity when you use ArcGIS to create a feature class or table in an Oracle database or use the Add Incrementing ID Field geoprocessing tool to add an ID field to a table in an Oracle database.

The ArcGIS Object ID data type is the registered row ID column for the table (or feature class). Only one can exist per table.

Raster

BLOB or number(38)

The ArcGIS raster data type is supported in geodatabases only.

The data type used for the raster field depends on the configuration keyword you specify when you create a mosaic dataset or raster dataset.

Short integer

number(5)

number(p)

The precision (p) value can be in the range of 1 to 5. However, short integer columns can only store values that fall in the range of -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.

If created with ArcGIS software or ArcObjects SDK and precision is not set, a number(5) is created in the database; otherwise, the precision specified is used.

The precision can affect the resultant ArcGIS data type. See ArcGIS field data types for more information.

Text

varchar2, CLOB, nvarchar2, or NCLOB

Time only

Not applicable

Not supported

Timestamp offset

timestamp with time zone offset

Oracle data types supported in ArcGIS

Text data types

When you choose to include a text field in the table you create using ArcGIS, the varchar2 data type is used 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 not set to use Unicode encoding, the Oracle data type will be CLOB.

A text 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.

Geometry data types

ArcGIS creates and can work with two geometry data types in Oracle: Esri ST_Geometry and Oracle SDO_Geometry.

ST_Geometry

The ST_Geometry data type implements the SQL 3 specification of user-defined data types (UDTs), allowing you to create columns capable of storing spatial data such as the location of a landmark, a street, or a parcel of land. It provides International Organization for Standards (ISO) and Open Geospatial Consortium (OGC) compliant structured query language (SQL) access to the geodatabase and database. This storage extends the capabilities of the database by providing storage for objects (points, lines, and polygons) that represent geographic features. It was designed to make efficient use of database resources, to be compatible with database features such as replication and partitioning, and to provide rapid access to spatial data.

For more information, see ST_Geometry in Oracle.

SDO_Geometry

SDO_Geometry is implemented using an Oracle extensible object-relational-type system. The SDO_Geometry type is offered by Oracle using two primary options:

  • Oracle Spatial is an optional feature of the Oracle Database Enterprise Edition. In addition to providing the SDO_Geometry type, Oracle Spatial provides a number of additional geospatial capabilities.
  • Oracle Locator provides a subset of Oracle Spatial capabilities. It is included as a standard feature of Oracle Database Standard and Enterprise editions. Among other capabilities, it provides the Oracle Spatial geometry type (referred to as SDO_Geometry) and a SQL API to this content.

ArcGIS supports SDO_Geometry as an optional method to store spatial data. Specifically, Oracle Spatial or Locator geometry can be used to store and manage the feature and raster contents of datasets in enterprise geodatabases or Oracle databases.

For information on SDO_Geometry, consult Oracle documentation.

Raster data types

Use a configuration keyword that contains the RASTER_STORAGE parameter set to RASTERBLOB when you create raster datasets and mosaic datasets in geodatabases in Oracle.

Legacy:

Although you can use a configuration keyword that contains the RASTER_STORAGE parameter set to BLOB, it is less efficient and is supported primarily for legacy systems.