Finding Intersection Points between Line and Polygon

Today, someone emailed me and asked:

I’m using sql developer and oracle spatial to develop my prototype database (I’m really a beginner in SQL), one of my task now is to query parcels (polygon) which crossed by utility pipeline (line) [… and …] to query the intersection point between polygon and line…

Can you give me advice and tip how I can accomplish this task?

Below is one method for achieving this (note that you need an Enterprise Spatial license to do this).

 With pipelines as (
   select rownum as pipeid, MDSYS.SDO_GEOMETRY(2002, 82469, NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1), MDSYS.SDO_ORDINATE_ARRAY(455759.220, 6455577.890, 455618.508, 6455354.691, 455577.264, 6455432.325, 455545.725, 6455364.395, 455482.647, 6455553.629)) as geom from dual
 )
 select rownum as intersection_id, sdo_geometry(2001,p.geom.sdo_srid,sdo_point_type(c.x,c.y,null),null,null) as geom
   from lm_parcel p,
        table(select sdo_util.getVertices(sdo_geom.sdo_intersection(p.geom,l.geom,0.005)) as easement
                from pipelines l,
                     lm_parcel p
               where sdo_anyinteract(p.geom,l.geom) = 'TRUE'
                 and p.fid = 568470) c
  where p.fid = 568470
    and sdo_geom.relate(p.geom,'DETERMINE',sdo_geometry(2001,p.geom.sdo_srid,sdo_point_type(c.x,c.y,null),null,null),0.005) = 'TOUCH';
  
 INTERSECTION_ID GEOM
 --------------- -----------------------------------------------------------------------------------------------------
 1               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455515.315221591,6455455.62433523,NULL),NULL,NULL)
 2               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455539.783310342,6455382.22006897,NULL),NULL,NULL)
 3               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455553.065512693,6455380.20529922,NULL),NULL,NULL)
 4               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455609.490204436,6455371.66528816,NULL),NULL,NULL)
 5               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455627.496129769,6455368.94807528,NULL),NULL,NULL)
 6               MDSYS.SDO_GEOMETRY(2001,82469,MDSYS.SDO_POINT_TYPE(455675.025317434,6455444.33942184,NULL),NULL,NULL)
  
  6 rows selected

This is what it looks like graphically:

I hope this is of help to someone.