English

How To: 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

This procedure is performed in SQLPlus while connected as the owner of the table.

  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));

Related Information