By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table my_table (shape sdo_geometry);
begin
insert into my_table (shape) values (sdo_geometry('linestring(100 200, 300 400, 500 600)'));
insert into my_table (shape) values (sdo_geometry('linestring(700 800, 900 1000)'));
end;
/
1 rows affected
select
(sdo_util.get_coordinate(shape,1)).sdo_point.x as startpoint_x
from
my_table
| STARTPOINT_X |
|---|
| 100 |
| 700 |
create index idx1 on my_table ((mdsys.sdo_util.get_coordinate(shape,1)).sdo_point.x);
ORA-02327: cannot create index on expression with datatype ADT
select * from USER_ERRORS
create or replace function startpoint_x(shape sdo_geometry) return number
deterministic is
begin
return sdo_util.get_coordinate(shape,1).sdo_point.x;
end;
/
create index idx1 on my_table
(sys_context('USERENV', 'SESSION_USER')||'.'||startpoint_x(shape));