Text data type and Length size problem

[BibiOung1]

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!

[JakeSkinner]

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

[VinceAngelo]

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

- V

[JakeSkinner]

Ooops, good catch V.  Thanks!

[Raj-Chavada]

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 

[Raj-Chavada]

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

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. 

[Bud]

Get maximum text value length of multiple text fields

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