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 or a query layer, ArcGIS filters out any unsupported data types. ArcGIS will not display unsupported data types and you cannot edit them 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 Oracle data type that ArcGIS creates. The third column shows what other Oracle data types (if any) map to the ArcGIS data type when you view a table that you created outside ArcGIS. The last column provides additional information when needed.

ArcGIS data types	Oracle data types created	Other Oracle data types that can be viewed	Notes
BLOB	BLOB
DATE	TIMESTAMP	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. To use ST_Geometry in a database (not geodatabase), you must install it.
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 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 the following circumstances: You use Oracle 11g. You register an existing Oracle 12c 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 12c database or use the Add Incrementing ID Field geoprocessing tool to add an ID field to a table in an Oracle 12c database.		The ArcGIS type ObjectID is the registered row ID column for the table (or feature class). Only one can exist per table.
RASTER	RASTERBLOB, BLOB, or ST_RASTER		Rasters are only supported in geodatabases. Which data type is 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(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 Desktop, n = 5. This allows you to store short integers that fall within the allowable range.
TEXT	VARCHAR2, CLOB, NVARCHAR2, or NCLOB

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

As indicated in the table, ArcGIS creates and can work with three geometry data types in Oracle: Esri ST_Geometry, Oracle SDO_Geometry, and compressed binary. Compressed binary geometry storage can only be used in geodatabases.

ST_Geometry

The following is a general description of the ST_Geometry spatial data type. For information specific to the Oracle implementation, see ST_Geometry in Oracle.

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, Inc. (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.

Although you can define a column as type ST_Geometry, you do not insert ST_Geometry values into the column since it cannot be instantiated. Instead, you insert the subclass values.

ST_Geometry itself is an abstract, noninstantiated superclass. However, its subclasses can be instantiated. An instantiated data type is one that can be defined as a table column and have values of its type inserted into it.

The following chart demonstrates the hierarchy of the ST_Geometry data type and its subclasses.

ST_Geometry's subclasses are divided into two categories: the base geometry subclasses and the homogeneous collection subclasses. The base geometries include ST_Point, ST_LineString, and ST_Polygon, while the homogeneous collections include ST_MultiPoint, ST_MultiLineString, and ST_MultiPolygon. As the names imply, the homogeneous collections are collections of base geometries. In addition to sharing base geometry properties, homogeneous collections have some of their own properties.

Each subclass stores the type of geometry implied by its name; for instance, ST_MultiPoint stores multipoints. A list of the subclasses and their descriptions are in the following table:

Subtype	Description
ST_Point	A zero-dimensional geometry that occupies a single location in coordinate space. Has a single x,y coordinate value, is always simple, and has a NULL boundary.
ST_LineString	A one-dimensional object stored as a sequence of points defining a linear interpolated path. ST_LineStrings have length. The ST_LineString is simple if it does not intersect its interior. The endpoints (the boundary) of a closed ST_LineString occupy the same point in space. An ST_LineString is a ring if it is both closed and simple. The endpoints normally form the boundary of an ST_LineString unless the ST_LineString is closed, in which case the boundary is NULL. The interior of an ST_LineString is the connected path that lies between the endpoints, unless it is closed, in which case the interior is continuous.
ST_Polygon	A two-dimensional surface stored as a sequence of points defining its exterior bounding ring and zero or more interior rings. ST_Polygon has area and is always simple. The exterior and any interior rings define the boundary of an ST_Polygon, and the space enclosed between the rings defines the ST_Polygon's interior. The rings of an ST_Polygon can intersect at a tangent point but never cross.
ST_MultiPoint	A collection of ST_Points. Has a dimension of 0. An ST_MultiPoint is simple if none of its elements occupy the same coordinate space. The boundary of an ST_MultiPoint is NULL.
ST_MultiLineString	A collection of ST_LineStrings. ST_MultiLineStrings have length. ST_MultiLineStrings are simple if they only intersect at the endpoints of the ST_LineString elements. ST_MultiLineStrings are nonsimple if the interiors of the ST_LineString elements intersect. The boundary of an ST_MultiLineString is the nonintersected endpoints of the ST_LineString elements. The ST_MultiLineString is closed if all of its ST_LineString elements are closed. The boundary of an ST_MultiLineString is NULL if all the endpoints of all the elements are intersected.
ST_MultiPolygon	A collection of polygons. ST_MultiPolygons have area. The boundary of an ST_MultiPolygon is the cumulative length of its elements' exterior and interior rings. The interior of an ST_MultiPolygon is defined as the cumulative interiors of its element ST_Polygons. The boundary of an ST_MultiPolygon's elements can only intersect at a tangent point.

ST_Geometry subtypes

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 within enterprise geodatabases or Oracle databases.

SDO_Geometry stores information about a geometry including its geometry type, spatial reference ID, interpolation type (straight versus curved), and coordinate values. The SDO_Geometry type in geodatabases supports single and multipart point, line, and area geometry. Geometries can be described as having linear interpolation between coordinates as defined by the OpenGIS Simple Feature Specification. Geometries can also be constructed from circular curves or a combination of both interpolation methods. Application programs are responsible for properly inserting, updating, and fetching the contents of the SDO_Geometry type using an Oracle object-relational SQL interface. Applications are also responsible for ensuring that the content of each geometry adheres to the rules defined in the Oracle Spatial documentation. Oracle provides geometry validation routines that can be executed after inserting geometries. Additionally, beginning with Oracle 11.1.0.7, geometry is validated on index inserts.

Note:

Oracle geometry validation routines do not implement precisely the same set of rules as the ArcGIS geometry validation. However, ArcGIS is designed to write SDO_Geometry that satisfies the Oracle validation rules.

Information about every SDO_Geometry column should be recorded in the Oracle Spatial metadata schema, though Oracle Spatial does not do this automatically. (The Oracle Spatial metadata schema is exposed for each schema as the view USER_SDO_GEOM_METADATA.) The software that creates SDO_Geometry columns must insert the metadata for those columns. ArcGIS does this for any SDO_Geometry feature classes it creates. The metadata contains the spatial column name, the name of the table it resides in and its owner, the Oracle Spatial Reference Identifier (SRID), number of dimensions, the range of each dimension, and its coordinate tolerance.

Spatial indexes provide fast access to features based on the location of their geometry. For SDO_Geometry, R-tree spatial indexes are generally the most efficient and easiest to create, and Oracle recommends their use in most situations. Oracle Spatial provides the Spatial Index Advisor utility to assist in determining the best type of spatial index for a given table. In addition, consult your Oracle Spatial User's Guide and Reference for detailed information on supported spatial index types, how to create them, and the trade-offs of different spatial index methods.

Oracle Spatial extends SQL with spatial search functions for primary and secondary filtering. Including the SDO_FILTER function in an SQL query performs a primary spatial search utilizing the spatial index. Spatial predicates, such as SDO_RELATE and SDO_CONTAINS, return secondary relationships between pairs of SDO_Geometry objects. Oracle Spatial has spatial transformation functions that change the form of an SDO_Geometry value. For example, the SDO_BUFFER function computes the coordinates of a new SDO_Geometry object as a buffer polygon at a given distance surrounding the original geometry. Other spatial transformation functions include SDO_DIFFERENCE and SDO_INTERSECTION.

Oracle Spatial provides access to a number of predefined coordinate reference systems using an SRID value. The SRID value, stored in the SDO_Geometry object, specifies the coordinate reference for the geometry stored in that object. If it is not NULL, the SRID in the SDO_Geometry object is a foreign key into a table containing details about each SRID. This table is MDSYS.CS_SRS. The SDO_TRANSFORM function uses the spatial reference ID to establish coordinate reference transformations. ArcGIS also uses this information to create spatial references.

Compressed binary

The Esri compressed binary storage type uses a binary storage mechanism for storing feature geometry. A compressed binary feature class is made up of three tables: the business table, feature table, and spatial index table.

After verifying the geometry, the client application compresses and sends it to the geodatabase, where it is stored in compressed binary format in a feature table, or F table. Compressing the geometry on the client unloads the task from the database server and reduces the transmission time to send the geometry. It also offers efficient storage and retrieval of spatial data by reducing the space required to store data by as much as 40 percent.

The business table contains attributes and a spatial column. The spatial column is a key to the feature and spatial index tables.

The relationship between the business table and feature table is managed through the spatial column and the feature ID (FID) column. This key, which is maintained by ArcGIS, is unique.

Raster data types

You can use BLOB or ST_Raster for the raster columns of raster datasets and mosaic dataset.

Raster datasets and mosaic datasets created with a configuration keyword that contains the RASTERCOLUMN parameter set to RASTERBLOB or BLOB both create BLOB columns. When the RASTERBLOB setting is used, the BLOB column is created directly on the business table; when BLOB is used, the BLOB column is created on a side table. See the BLOB section of this topic for information on BLOBs in Oracle.

The next subsection describes the ST_Raster data type.

ST_Raster

ST_Raster is a user-defined data type you can install in enterprise geodatabases to provide SQL access to raster data.

To use ST_Raster, you must configure it in the geodatabase.

For in-depth information on how the ST_Raster object type is defined, see The ST_Raster data type.

BLOBs

BLOB is a database management system (DBMS) industry acronym for binary large object. BLOB columns were implemented several years ago by Oracle Corporation to replace LONG RAW technology for storing binary data.

The architecture of the BLOB data type is divided into three basic components: the BLOB column, LOB segment, and LOB index. The BLOB column stores the LOB locator (36 bytes) and binary data in-row if it is less than 3,965 bytes and in-row storage has not been disabled for the column.

Note:

Tests by Esri have shown that allowing storage in-row provides the best performance, so you are advised not to disable in-row storage.

If the binary data exceeds 3,964 bytes, the in-row storage space of the BLOB column is not allocated, and the LOB locator references the binary data stored in the LOB segment.

Therefore, a value stored in a BLOB column with in-row storage enabled is always at least 36 bytes (the space allocated to the LOB locator) and may be as large as 4,000 bytes (the combined space allocated to the LOB locator and the maximum space that can be allocated to binary data stored in-row).

The LOB segment is divided into chunks. Chunks must be a multiple of the Oracle data block size. For example, if the data block size is 8K, the LOB segment can be created with a minimum chunk size of 8K. If the length of the data stored within the LOB segment is 5,000 bytes, it is stored in the LOB segment since it exceeds 3,964 bytes and the chunk size is 8K or 8,192 bytes. In this case, 3,192 bytes of the LOB segment chunk remains unused. Transferring data from LONG RAW to BLOB can result in more space being required—perhaps as much as a 30 percent increase due to the unused space in the LOB segment. This is unavoidable if your data exceeds the 3,964-byte, in-row storage threshold of the BLOB column.

The 8K chunk size experiences the best I/O performance while wasting the least amount of space. The 16K chunk size wastes more space than an 8K chunk size. Therefore, to avoid the loss of space, you are advised to either re-create the database that currently has a 16K data block size with an 8K data block size or, if that is not possible, create LOB segments in tablespaces that have been created with an 8K block size. To do this, you need to allocate an 8K buffer cache in the Oracle System Global Area (SGA).

Chunk sizes of 4K and 2K have been found to waste less space, but the increase in I/O cost does not warrant using them.

The LOB index is only used if the number of chunks addressed by the LOB locator exceeds 12; otherwise, the first 12 chunks are addressed by the LOB locator.

The following three figures illustrate the three possible storage cases of binary data stored in a BLOB column. In the first case, 3,000 bytes of binary data are stored in-row, since 3,000 bytes is less than the 3,965-byte, in-row storage threshold. If in-row storage is not disabled for the BLOB column, the LOB segment and LOB index are not used. Typically, this results in a faster fetch of the BLOB data due to the reduced number of I/O operations since Oracle does not need to access the LOB segment or LOB index.

BLOB data smaller than 3,965 bytes in size stored in-row

The next figure illustrates the second case, in which the binary data is larger than 3,964 bytes (in this case, the data is 81,920 bytes) and cannot fit in-row. Therefore, the LOB locator references the binary data that is stored in the LOB segment. Since the binary data does not occupy more than 12 chunks in the LOB segment, the LOB locator stores its addresses. In this case, the LOB index is not used.

BLOB data greater than 3,964 bytes in size stored out-of-row. A LOB locator in the table points to the LOB segment where the data is stored.

In the final illustration, the binary data is so large (106,496 bytes) that the LOB index is required. In this case, the binary data exceeds the in-row storage plus requires more than 12 chunks within the LOB segment to store it. For data this large, the LOB locator references the LOB index to obtain the location of the chunks within the LOB segment. This case is extremely rare for vector data and can be avoided for raster data.

BLOB data stored out-of-row, requiring a LOB index

For information on setting BLOB storage, see Oracle configuration parameters.