Text data type and Length size problem

5410
5
09-01-2011 09:09 AM
BibiOung1
New Contributor II
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!
5 Replies
JakeSkinner
Esri Esteemed Contributor
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
Esri Esteemed Contributor
'sdetable -o export' won't work, but 'sdedbtune -o export' will. Ditto with 'sdedbtune -o import'. 😉

- V
0 Kudos
JakeSkinner
Esri Esteemed Contributor
Ooops, good catch V.  Thanks!
0 Kudos
Raj-Chavada
New Contributor III

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 

0 Kudos
Raj-Chavada
New Contributor III

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.