topic Re: Text data type and Length size problem in Data Management Questions

Text data type and Length size problem

BibiOung1 — Thu, 01 Sep 2011 16:09:37 GMT

When importing a table into SDE/Oracle 11g instance using AG 10 or 9.3.1 "Table to Table" tool, a field defined as Text, 4000 is converted to Text, 1073741822. Using SQL Plus to describe the table, it defines it as Type, NCLOB. The table was originally loaded in SDE using SQL. There is a similar issue when importing the table into a File Geodatabase.

Has anyone encounter this problem.

Thanks
B!

Re: Text data type and Length size problem

JakeSkinner — Thu, 01 Sep 2011 19:19:32 GMT

By default, ArcSDE for Oracle stores text fields as nvarchar2. The maximum size for nvarchar2 is 2000. When a field is larger than 2000, SDE will convert the field to NCLOB. To avoid this issue you will need to update your dbtune table so that text fields will be converted to varchar2 rather than nvarchar2.

To do this, export your dbtune table to a text file. Ex:

sdetable -o export -f c:\temp\dbtune.txt -i sde:oracle11g -u sde -p sde@orcl

Add a parameter UNICODE_STRING with a string value of FALSE under the DEFAULTS keyword. Save the text file and re-import into SDE:

sdetable -o import -f c:\temp\dbtune.txt -i sde:oracle11g -u sde -p sde@orcl

Now you can import tables into SDE with text fields of size 4000 and it will be varchar2 rather than NCLOB. Here is some further information on this:

http://help.arcgis.com/en/arcgisdesktop/10.0/help/index.html#//002n00000067000000

Re: Text data type and Length size problem

VinceAngelo — Thu, 01 Sep 2011 22:25:54 GMT

'sdetable -o export' won't work, but 'sdedbtune -o export' will. Ditto with 'sdedbtune -o import'. 😉

- V

Re: Text data type and Length size problem

JakeSkinner — Fri, 02 Sep 2011 10:51:07 GMT

Ooops, good catch V. Thanks!

Re: Text data type and Length size problem

Raj-Chavada — Mon, 29 Mar 2021 22:32:17 GMT

I am experiencing similar issue when creating attribute field with field length - 5000 but with SQL Server 2016 not oracle. For some reason, the SQL Server sets the type - navchar(max) displaying field length of 1073741822.

Can this fixed by applying the same fix?

@JakeSkinner @VinceAngelo

Re: Text data type and Length size problem

Raj-Chavada — Mon, 29 Mar 2021 23:04:19 GMT

Nevermind. I got my answer here - https://pro.arcgis.com/en/pro-app/latest/help/data/geodatabases/manage-sql-server/data-types-sqlserver.htm

It seems like I can do this by exporting and import database configuration keyword table. The UNICODE_STRING needs set to false "FALSE". This way I can create the text field with field length up to 8000 characters.

Re: Text data type and Length size problem

Bud — Mon, 28 Oct 2024 18:24:15 GMT

Get maximum text value length of multiple text fields

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