Convert a Oracle table to a feature class?

[Bud]

What are the steps needed to convert a Oracle table to a FC in an EGDB?

For example:

1. Take an existing Oracle table.
2. Add a geometry column if needed.
3. If it's an SDO_GEOMETRY column, then register with USER_SDO_GEOM_METADATA?
4. Add a spatial index using the Add Spatial Index GP tool?
5. Register with the geodatabase using Catalog in ArcMap?
6. Other?

I've tried various combinations of the above steps, but I often have problems like the resulting FC's attribute table is empty when I view it in ArcMap. Or the spatial reference is broken. Or the geometry type (point/line/polygon) isn't properly defined, etc.
I usually give up at that point and try something else, like create an empty FC via ArcMap, create a spatial view on the Oracle table, and then use the Load tool in Catalog to load the data into the empty FC. But that's pretty clunky...I'm hoping there's a better way.

Thanks.

[JakeSkinner · Accepted solution]

Hi @Bud,

Here is an example of creating a table, inserting points, inserting into the user_sdo_geom_metadata table, creating the index, and then registering with your geodatabase:

create table SDO_Points (OID number(38) not null unique, TestID number, shape SDO_GEOMETRY);

Insert into SDO_Points values (1, 1, SDO_GEOMETRY(4001, 8260, null, SDO_ELEM_INFO_ARRAY(1, 1, 1),  SDO_ORDINATE_ARRAY(10.0, 10.0, 10, 1100)));
Insert into SDO_Points values (2, 2, SDO_GEOMETRY(4001, 8260, null, SDO_ELEM_INFO_ARRAY(1, 1, 1),  SDO_ORDINATE_ARRAY(10.0, 20.0, 20, 1200)));
Insert into SDO_Points values (3, 3, SDO_GEOMETRY(4001, 8260, null, SDO_ELEM_INFO_ARRAY(1, 1, 1),  SDO_ORDINATE_ARRAY(10.0, 30.0, 30, 1300)));
Insert into SDO_Points values (4, 4, SDO_GEOMETRY(4001, 8260, null, SDO_ELEM_INFO_ARRAY(1, 1, 1),  SDO_ORDINATE_ARRAY(10.0, 40.0, 40, 1400)));

insert into user_sdo_geom_metadata values ('SDO_Points', 'SHAPE', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('Lat', -90, 90, 0.05), SDO_DIM_ELEMENT('Long', -180, 180, 0.05), SDO_DIM_ELEMENT('Elevation', 0, 5000, 0.05), SDO_DIM_ELEMENT('M', 0, 5000, 0.05)), 8260);

create index SDO_8260_4D_Point_indx on SDO_Points(shape) indextype is mdsys.spatial_index;

Last step is to execute the Register with Geodatabase tool in ArcGIS Pro/ArcMap.

[Bud]

Thanks very much. That really helps.

Related: Tips for creating an entry in user_sdo_geom_metadata (NAD 1983 UTM)

[Bud]

@JakeSkinner 

FYI - I noticed just now that SQL Developer has a tool for entering Spatial Metadata for a given table:

That could be handy.