Enable Select by Attribute on an Oracle OLE DB table

Summary

An Oracle OLE DB table that has a NUMBER (9) PRIMARY KEY or a NUMBER(9)UNIQUE NOT NULL column can support selections in ArcGIS.

If the OLE DB table does not have a suitable field and users wish to enable Select by Attribute or relate the table to other data sources, add a new field as outlined below.

Procedure

1. Add the new LONG INTEGER column to the table. No constraints. They are added later.
   
   

   Code:
   ALTER TABLE MyData ADD (theIDCol NUMBER(9));

   
   
2. Create a sequence generator:
   
   

   Code:
   CREATE SEQUENCE MyGen START WITH 1; 

   
   
   

   Note:
   This requires the CREATE SEQUENCE system privilege.

   
   
3. Populate the new column:
   
   

   Code:
   UPDATE MyData SET theIDCol = MyGen.NEXTVAL; 

   
   
4. Add NOT NULL Constraint.
   
   

   Code:
   ALTER TABLE MyData MODIFY (theIDCol NOT NULL);

   
   
5. Create index:
   
   

   Code:
   CREATE UNIQUE INDEX MyDataIDX ON MyData (theIDCol); 

   
   
6. Alternatively, rather than applying 'NOT NULL' and 'UNIQUE' constraints on the column as shown in Steps 4 and 5, apply a PRIMARY KEY constraint on the column:
   
   

   Code:
   ALTER TABLE MyData ADD CONSTRAINT theIDCol_PK PRIMARY KEY(theIDCOL));