HOW TO

Enable Select by Attribute on an Oracle OLE DB table

Last Published: April 25, 2020

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

Article ID:000007168

Software:
  • ArcMap 8 x
  • ArcMap 9 x
  • Legacy Products

Get help from ArcGIS experts

Contact technical support

Download the Esri Support App

Go to download options

Related Information

Discover more on this topic