How To: Enable Select by Attribute on an Oracle OLE DB table
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.
This procedure is performed in SQLPlus while connected as the owner of the table.
- Add the new LONG INTEGER column to the table. No constraints. They are added later.
ALTER TABLE MyData ADD (theIDCol NUMBER(9));
- Create a sequence generator:
CREATE SEQUENCE MyGen START WITH 1;
This requires the CREATE SEQUENCE system privilege.
- Populate the new column:
UPDATE MyData SET theIDCol = MyGen.NEXTVAL;
- Add NOT NULL Constraint.
ALTER TABLE MyData MODIFY (theIDCol NOT NULL);
- Create index:
CREATE UNIQUE INDEX MyDataIDX ON MyData (theIDCol);
- 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:
ALTER TABLE MyData ADD CONSTRAINT theIDCol_PK PRIMARY KEY(theIDCOL));
- XY events do not always appear as a selectable layer when connecting to an Access database using an OLE DB connection