ORA-00904 invalid identifier when dropping generated column

User_77G7L

Hello,

I have a table that was created as

CREATE TABLE xxx (

"SHAPE" "SDO_GEOMETRY",

"X_VALUE" NUMBER GENERATED ALWAYS AS ((("MDSYS"."SDO_GEOM"."SDO_POINTONSURFACE"("SHAPE",0.005))."SDO_POINT")."X") VIRTUAL ,

"Y_VALUE" NUMBER GENERATED ALWAYS AS ((("MDSYS"."SDO_GEOM"."SDO_POINTONSURFACE"("SHAPE",0.005))."SDO_POINT")."Y") VIRTUAL)

Both X_VALUE and Y_VALUE columns are created from the SHAPE (SDO_GEOMETRY) column, but when I want to drop them with:

ALTER TABLE xxx DROP (X_VALUE, Y_VALUE)

I get the error "ORA-00904: "MDSYS"."SDO_GEOM"."SDO_POINTONSURFACE": invalid identifier". Am I doing something wrong?

Jonathan Lewis

Thanks for supplying a model to test.

I've run it on 19.11.0.0 with a couple of variations. Obviously having the SHAPE column produces a number of virtual columns, but that doesn't seem to be the critical issue.

It looks like an Oracle bug; I've created a version of the table with some other "real" columns, before, after, and in between your columns, and if I include even one column like the X_VALUE or Y_VALUE I get the same error even when I drop a real, simple column.

Regards

Jonathan Lewis

Jonathan Lewis

Thanks for supplying a model to test.

I've run it on 19.11.0.0 with a couple of variations. Obviously having the SHAPE column produces a number of virtual columns, but that doesn't seem to be the critical issue.

It looks like an Oracle bug; I've created a version of the table with some other "real" columns, before, after, and in between your columns, and if I include even one column like the X_VALUE or Y_VALUE I get the same error even when I drop a real, simple column.

Regards

Jonathan Lewis

Jonathan Lewis

I've had another look at this - the only workaround I've found is to create functions that hide the complexity, viz:

create or replace function my_x(inshape mdsys.sdo_geometry)
return number
deterministic
as
begin
        return   mdsys.sdo_geom.sdo_pointonsurface(inshape,0.005).sdo_point.x;
end;
/

...
        x_value         number generated always as (my_x(shape)) virtual,
...

with a similar function and definition for y_value.

Regards

Jonathan Lewis