Error: ORA-01438 When Importing Data into an SDE Geodatabase

Error Message

** Internal Publish Only! This article may contain information that is not intended for external circulation. **

Loading data into ArcSDE that contains a field defined as SHORT INT may result in an ORA-01438 error.

Cause

From the Oracle documentation:

Code:
ORA-01438 value larger than specified precision allows for this column
Cause: When inserting or updating records, a numeric value was entered that exceeded the precision defined for the column.
Action: Enter a value that complies with the numeric column's precision, or use the MODIFY option with the ALTER TABLE command to expand the precision.

To understand this problem, one first needs to understand the SMALL INTEGER data type.

"In the C programming language, int objects have the natural size suggested by the host machine architecture"
-- Source: "The C programming language - Kernighan, Ritchie".

To become Operating System independent, ArcSDE defines a SMALL INT as a signed 2 byte integer:

Code:
#define SE_SMALLINT_TYPE           1   /* 2-byte Integer */

This is the same as the Java Specification that defines a Short Integer as an integer in the range of -32768 to 32767, inclusive.

The problem is that Oracle does not have a 'short Integer' data type. It has a NUMBER() data type. Looking at the positive portion of a Short Integer, which by definition has a range of 0 to 32767, which Oracle data type should be used? Using a NUMBER (4) the range is 0 to 9999, therefore not able to store the full range of the short int. Using a NUMBER(5) the range is 0 to 99999, which is far greater than the range of the short int.

ArcSDE has therefore defined the Short Integer in Oracle as NUMBER(4).

If the original dataset contains a valid short integer value, but its value is greater than 9999, the error message will occur, due to the above discussion.

Solution or Workaround

The Short Int data type needs to be changed to an Integer data type. This will conserve the correct value, prevent the error message, and take little if no extra data storage.

- The first option is to change the data type in the original data source. Depending on what the data sources is will depend on what method selected to make this change.

- The second option is to create the Feature Class, making sure the new field is of Integer type, and load the data as shown below.

For example:
1. Open ArcCatalog.
2. Create a new Feature Class.
3. Make sure the Geometry type is the same as the original data source.
4. Set the correct values for the Coordinate system (falseX, falseY, precision, projection and grid size).
5. Import the fields from the data source.
6. Change the field type from 'short int' to 'int'.
7. An empty Feature Class has been created, with the correct spatial extent and spatial type, with the correct fields.
8. Use the 'Load data' option to load the data.

Article ID:000006022

Software:

Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

ORA-01438 when importing data into an SDE Geodatabase

Error Message

Cause

Solution or Workaround

Related Information

Discover more on this topic