This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,967 Users
  • 2,269,775 Discussions
  • 7,916,823 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: 10,142 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: 10,142 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: 10,142 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