Forum Stats

  • 3,825,886 Users
  • 2,260,571 Discussions
  • 7,896,720 Comments

Discussions

ORA-00904 invalid identifier when dropping generated column

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?

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,974 Blue Diamond
    edited Mar 9, 2022 8:06PM Answer ✓

    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

Answers

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,974 Blue Diamond
    edited Mar 9, 2022 8:06PM Answer ✓

    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
    Jonathan Lewis Member Posts: 9,974 Blue Diamond

    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