topic Get maximum text value length of multiple text fields in ArcGIS Pro Questions

Get maximum text value length of multiple text fields

Bud — Mon, 28 Oct 2024 19:02:56 GMT

ArcGIS Pro 2.9.5; Oracle 18c 10.7.1 EGDB:

I've imported a .CSV to an enterprise geodatabase as a standalone table using the Table to Geodatabase geoprocessing tool.

Unfortunately, the tool has made the text fields way too long: 1073741822 characters, regardless of the max value length in a given field.

I've deleted all rows in the table to allow me to change the field lengths using the Fields View. (I'll re-load the data afterwards.) But the question is: What lengths to use? If I could determine what the max value length in all of the fields, then I'd set the max length to, say, 255 for all fields. But I don't know what the max text value length is.

How can I determine the max text value length of multiple fields using ArcGIS Pro?

Import Table — Set text field length to input field's max text value length

Re: Get maximum text value length of multiple text fields

Bud — Mon, 28 Oct 2024 17:58:54 GMT

One option is to use SQL in a SQL client like SQL Developer:

select
    max(length(WONUM)),
    max(length(RECTYPE)),
    max(length(DESCRIPTION)),
    max(length(STATUS)),
    max(length(WO_CLASSIFICATION)),
    max(length(OWNERGROUP)),
    max(length(ASSET_DESCRIPTION)),
    max(length(ASSET_CLASSIFICATION)),
    max(length(LOCATION)),
    max(length(LOCATION_DESCRIPTION)),
    max(length(REPORTDATE)),
    max(length(STATUSDATE)),
    max(length(ACTSTART)),
    max(length(ACTFINISH)),
    max(length(DIVISION)),
    max(length(ASSETNUM)),
    max(length(RECUNIQUEID)),
    max(length(SA_ADDRESSLINE2)),
    max(length(SA_STADDRSTREET)),
    max(length(SA_STADDRNUMBER)),
    max(length(SA_DESCRIPTION)),
    max(length(SA_STREETADDRESS)),
    max(length(SA_FORMATTEDADDRESS)),
    max(length(SA_CODE)),
    max(length(XYSOURCE)),
    max(length(SUBZONE))
from
    infrastr.a_test_tbl

It would be ideal if I could pivot using Oracle SQL and then ORDER BY to find the max. I'm aware of the PIVOT clause in Oracle SQL, but I'm not sure how to use it for this.

Re: Get maximum text value length of multiple text fields

Bud — Mon, 28 Oct 2024 18:33:43 GMT

Unfortunately, changing the field lengths in the empty table using the Fields View didn't work:

"ERROR 001624: Failed to alter field length."

Re: Get maximum text value length of multiple text fields

Bud — Mon, 28 Oct 2024 20:04:06 GMT

I think the problem is the underlying Oracle field type is NCLOB.

That's unusual for Oracle enterprise geodatabase tables. The underlying text datatype is usually NVARCHAR2.

With that said, I think I found a fix:

In ArcGIS Pro > Fields View, change the field data type in the empty table from TEXT to SHORT INT. Save. Then change it back to TEXT. Save. The underlying datatype is now NVARCHAR2(255 CHAR).