Can't identify or select unversioned data in ArcMap

[ShawnHolyoak]

I have a feature class that is not versioned as it is updated via DML statements that use data from a different software package.  The DML statements, which simply update two columns, process fine, the feature class displays just fine in ArcMap, but when I try to identify, I get no results.  When I try to select, I get an ORA-01455, "The converted form of the specified expression was too large for the specified datatype".  What on earth is going on, and how do I fix it?  Thanks.

[VinceAngelo]

What database?  Is ArcSDE installed?  What version of ArcGIS?  How is the table
defined (both via a SQL interface, and by ArcGIS)?  If you populate a value of
99999 in a NUMBER(5), it will be legal in Oracle, but not as a 16-bit integer.

- V

[ShawnHolyoak]

What database?  Is ArcSDE installed?  What version of ArcGIS?  How is the table
defined (both via a SQL interface, and by ArcGIS)?  If you populate a value of
99999 in a NUMBER(5), it will be legal in Oracle, but not as a 16-bit integer.

- V

ArcSDE 10.05 on Oracle 10.2.0.2  The table was originally created via ArcCatalog, imported from a shapefile, and registered as versioned.  When the requirements changed, we unregistered as versioned the feature dataset this feature class is in.  One of the new columns included in the DML statements is showing up as an integer in Oracle, not a number, and it has a value over 8,000,000,000.  I'm thinking that may be the problem, but I'm not sure how to tell.

Edit:  I tried adding an additional column to the table via ArcToolbox, and then calculating the new column with the value of the original column.  I received the identical error, which tells me that column is the problem.  It may be that we added the column originally via Oracle dml statement instead of through ArcCatalog or Toolbox, and this is the result.

[VinceAngelo · Accepted solution]

While all the other ArcSDE databases support discrete integer and floating-point types,
Oracle doesn't.  They have NUMBER.  Trying to place a value in excess of 2^31-1 will work
in SQL, but will cause overflow when it's bound to a 32-bit integer.  The ORA-01455 error
is how you tell you've got an encoding problem.

You have two choices:
1) Knock an order of magnitude off that 8B value to put it in range, or
2) Change the column to NUMBER(11,1) and treat it as a SE_FLOAT64_TYPE.

- V

[ShawnHolyoak]

While all the other ArcSDE databases support discrete integer and floating-point types,
Oracle doesn't.  They have NUMBER.  Trying to place a value in excess of 2^31-1 will work
in SQL, but will cause overflow when it's bound to a 32-bit integer.  The ORA-01455 error
is how you tell you've got an encoding problem.

You have two choices:
1) Knock an order of magnitude off that 8B value to put it in range, or
2) Change the column to NUMBER(11,1) and treat it as a SE_FLOAT64_TYPE.

- V

That's what I expected was happening.  Thanks for the confirmation - I'll see how the vendor wants to handle it.  BTW, how do I do option number 2?  I did sdetable -o alter_column and changed it to a float64, but it still shows as 10 digits with zero decimals.  Do I just do it through Oracle sql?  I've not had good luck with changing things in Oracle and having ArcGIS pick up those changes.  Thanks.

[VinceAngelo]

Usually that's not something that can be easily altered, especially if you just defined
the column as NUMBER.  You can't reduce precision but you can increase it, so
altering from NUMBER(10) to NUMBER(10,0) or (11,1) might be possible.  The
trick is getting ArcSDE (and ArcGIS) to recognize the update on a registered table.

I've never been fond of 'sdetable -o alter', though it sounds like it's more capable
now then when I last had to use it.  The easiest way to handle major schema
changes is to back up the table, create a clone with CREATE TABLE ... AS SELECT...,
drop it with ArcGIS, then recreate the empty table template (with updates) and
INSERT back into it.  Then you can re-register with the geodatabase and clean
up the interim copies.

- V

[ShawnHolyoak]

Usually that's not something that can be easily altered, especially if you just defined
the column as NUMBER.  You can't reduce precision but you can increase it, so
altering from NUMBER(10) to NUMBER(10,0) or (11,1) might be possible.  The
trick is getting ArcSDE (and ArcGIS) to recognize the update on a registered table.

I've never been fond of 'sdetable -o alter', though it sounds like it's more capable
now then when I last had to use it.  The easiest way to handle major schema
changes is to back up the table, create a clone with CREATE TABLE ... AS SELECT...,
drop it with ArcGIS, then recreate the empty table template (with updates) and
INSERT back into it.  Then you can re-register with the geodatabase and clean
up the interim copies.

- V

Thanks for the help.  I wanted to do the first option, but the column has to be empty to change the precision, and if I have to do that, it's probably easier just to do the whole table, especially with the trouble I've had getting ArcSDE and GIS to recognize the changes.