Adding an SDO_GEOMETRY spatial column to a business table with the command 'sdelayer –o add' results in the following error:
"ORA-01758: table must be empty to add mandatory (NOT NULL) column"
The business table must not contain rows prior to the operation. If the table already contains rows, the operation fails. This is a known issue.
Solution or Workaround
There are two options for the addition of an SDO_GEOMETRY column in this case.
- If the table has rows and is not registered as versioned, use SQL to alter the table, adding the SDO_GEOMETRY column. USers must also insert the metadata for the new column in USER_SDO_GEOM_METADATA and build a spatial index using CREATE INDEX. Then, register the table as a layer using sdelayer –o register.
- Alternatively, use "sdelayer -o add" to add the column to the table before inserting any rows.