Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table sidewalks (
  id number primary key,
  geom sdo_geometry
);
create table inspections (
  id number primary key,
  inspection_point sdo_geometry,
  sidewalk_id number,
  sidewalk_distance number,
  sidewalk_measure number,
  projected_point sdo_geometry
);
insert into sidewalks 
values (
 1,
 sdo_geometry ('LINESTRING(0 5, 10 10, 30 0, 50 10, 60 10)')
);
1 rows affected

insert into user_sdo_geom_metadata
values (
  'SIDEWALKS',
  'GEOM',
  sdo_dim_array (
    sdo_dim_element('X',0,1000,0.05),
    sdo_dim_element('Y',0,1000,0.05)
  ),
  null
);
1 rows affected

create index sidewalks_sx on sidewalks(geom) indextype is mdsys.spatial_index;
create or replace trigger sidewalk_inspections
before insert or update of inspection_point on inspections
for each row

declare
  sidewalk_id number;
  sidewalk_distance number;
  sidewalk_geom sdo_geometry;
  sidewalk_lrs sdo_geometry;
  projected_point sdo_geometry;

begin

  -- Find nearest sidewalk
  select id, geom, sdo_nn_distance(1)
  into sidewalk_id, sidewalk_geom, sidewalk_distance
  from sidewalks
  where sdo_nn(geom, :new.inspection_point, 'sdo_num_res=1',1) = 'TRUE';

  -- Convert sidewalk geometry to LRS
  sidewalk_lrs := sdo_lrs.convert_to_lrs_geom(sidewalk_geom);

  -- Project inspection point onto the sidewalk line
  projected_point := sdo_lrs.project_pt(sidewalk_lrs,:new.inspection_point);

  -- Update inspection point
  :new.sidewalk_id := sidewalk_id;
  :new.sidewalk_distance := sidewalk_distance;
  :new.sidewalk_measure := sdo_lrs.get_measure(projected_point);
  :new.projected_point := sdo_lrs.convert_to_std_geom(projected_point);
end;
/
insert into inspections(id,inspection_point) values (101, sdo_geometry('POINT(20 1)'));
ORA-29902: error in executing ODCIIndexStart() routine
ORA-22060: argument [2] is an invalid or uninitialized number
ORA-06512: at "MDSYS.SDO_INDEX_METHOD_10I", line 333
ORA-06512: at "FIDDLE_SQXMFXWWLNFQTQEDFMCN.SIDEWALK_INSPECTIONS", line 11
ORA-04088: error during execution of trigger 'FIDDLE_SQXMFXWWLNFQTQEDFMCN.SIDEWALK_INSPECTIONS'

select * from inspections
select a.id, a.inspection_point.Get_WKT() from inspections a;