Oracle CLOB field into SDE and File GDB issues

[JacquelinePursell]

I am trying to create an automatic feature class backup/update.  All of our databases including the SDE are in Oracle and my DBA has created me a few views for me in those non-sde databases.  I am making a copy of the SDE feature class to be updated into a file geodatabase on the network then truncating the SDE feature class, and appending it with a newly created XY event layer from the Oracle table view.  I have successfully finished making this with 2 other feature classes, but this particular table for some reason has a field with a CLOB data type.... 

I was able to recreate the SDE feature class from scratch and forcing a 1000 character limit to fix the error appending the SDE but
I get an error when saving the SDE feature class to the file geodatabase after doing so.  It seems the file geodatabase automatically forces max limit as 255 characters and won't even truncate the field for me, only stops and throws an error.

[ATTACH=CONFIG]32988[/ATTACH]

What is the best approach for dealing with CLOB field types in ArcGIS?

[JacquelinePursell · Accepted solution]

I will answer my own question.

After doing a lot of research, I stumbled upon the explanation of the SDE system tables:
http://resources.arcgis.com/en/help/main/10.2/index.html#/Geodatabase_system_tables/002n0000008m0000...
On that first chart is the COLUMN_REGISTRY table. SDE_TYPE column in that table is the column I needed! The numbers in there correspond to the type of field that SDE recognizes the type as.

I made a dummy table in SDE with 1 field:
CLOB, Text with 4000 characters

I queried the COLUMN REGISTRY as such:
SELECT C.COLUMN_NAME, C.SDE_TYPE
FROM COLUMN_REGISTRY C
WHERE C.COLUMN_NAME = â??CLOBâ?? ;

I got:
COLUMN_NAME SDE_TYPE
_____________ _________
CLOB.................15

Type 15 = SE_NCLOB_TYPEâ??Unicode character large object. (for 1000 characters, it is type 13) So all I had to do was just check that if I tell ArcGIS to use a character length of 4000, it automatically reads it as CLOB types. If not, I would need to edit the DBTUNE table and mess with my database settings.

But there was still an issue because I was grabbing this from a view inside another Oracle database. The view has to be grouped by permit number but according to Oracle, you must name every single field in the GROUP BY statement and CLOBs are not supported for grouping (because they could contain non-text items that are unsortable). So I had to rewrite the view to convert it to a NVARCHAR(4000) using the CAST command. If anyone puts anything more than 4000 characters in it, it will be truncated but thatâ??s the only way I can do it.

NOTE: if you put 4000 in the text length, it should read it as a CLOB and when you go into the properties after you create your table, it will show a gigantic amount of characters. Also that if you cast the field in Oracle as a text, the max you can have any text field is 4000 characters.

In my opinion there was no need for a CLOB type to begin with for this particular type of data (or anything thats only going to contain only text since its got up to a billion characters and up to 128 TB of data...yikes!) but I am not the admin or designer of their database, only for the SDE and I have to work with what I am given.... oh well.

oh and about my automatic update issue, in my model, when I am copying the SDE feature class over to the GDB, I right click on that field I know is a CLOB and hit properties, then increase the text length to 4000. Also in the SDE, I had to recreate the feature class from scratch and "import" the fields, then edit the CLOB field to have 4000 characters before finishing. This eliminated all my errors when copying and appending.

[georgemetonidze]

im doing the same
I am trying to create an automatic feature class backup/update. All of our databases including the SDE are in Oracle
in my case all my databases are in SQL so i create xy event layer on points ,but how to create it on lines ?
and is it posible to create a line from table where vertex cordinates are stored .
thanks
george.

[JacquelinePursell]

George-
Yes, there is a tool called XY To Line located under \Data Management Tools.tbx\Features\XY To Line

According to the description:
Creates a new feature class containing geodetic line features constructed based on the values in a start x-coordinate field, start y-coordinate field, end x-coordinate field, and end y-coordinate field of a table.

[JacquelinePursell]

Also there is a point to line tool under the same toolbox as well.  This one you can have many vertices per line, unlinke the XY to line where it has only a start and end.  You would have to convert your XY to point, then to line.