Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table poly (
  id int
  , poly SDO_GEOMETRY
)
create table point (
  id int
  , x number
  , y number
)
insert into poly
/*Growing squares centered in (0,0)*/
select 
  level
  , sdo_geometry(
    2003
    , null, null
    , SDO_ELEM_INFO_ARRAY(1, 1003, 3)
    , SDO_ORDINATE_ARRAY(-level, -level, level, level)
  )
from dual
connect by level < 6
5 rows affected

insert into point
/*List of points (0, y)*/
select
  level
  , 0
  , level
from dual
connect by level < 8
7 rows affected

select
  p.*
  , pl.id as contained_in
  , pl.poly.get_wkt() as contained_in_wkt
from point p
  left join poly pl
    /*You may construct point on-the-fly from its coordinates*/
    on SDO_CONTAINS(pl.poly, sdo_geometry(
      2001
      , null
      , sdo_point_type(p.x, p.y, null)
      , null
      , null
    )) = 'TRUE'
where 1 = 1
order by
  p.id
  , pl.id


ID
X
Y
CONTAINED_IN
CONTAINED_IN_WKT


1
0
1
2
POLYGON ((-2.0 -2.0, 2.0 -2.0, 2.0 2.0, -2.0 2.0, -2.0 -2.0))

1
0
1
3
POLYGON ((-3.0 -3.0, 3.0 -3.0, 3.0 3.0, -3.0 3.0, -3.0 -3.0))

1
0
1
4
POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0))

1
0
1
5
POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0))

2
0
2
3
POLYGON ((-3.0 -3.0, 3.0 -3.0, 3.0 3.0, -3.0 3.0, -3.0 -3.0))

2
0
2
4
POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0))

2
0
2
5
POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0))

3
0
3
4
POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0))

3
0
3
5
POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0))

4
0
4
5
POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0))

5
0
5
null
null

6
0
6
null
null

7
0
7
null
null


/*Object type to access instances as a single field*/
create type ts_sdo as object (
  shape sdo_geometry
)
/*Table of SDO_GEOMETRY*/
create type tt_sdo as varray (1048576) of ts_sdo
select
  p.shape.get_wkt() as point_wkt
  , pl.id as contained_in
  , pl.poly.get_wkt() as contained_in_wkt
from table(
  /*Then we construct collection from list of points*/
  tt_sdo(
    /*Each member is constructed from SDO_GEOMETRY*/
    ts_sdo(sdo_geometry('POINT(0 1)'))
    , ts_sdo(sdo_geometry(
      2001
      , null
      , sdo_point_type(0, 10, 0)
      , null
      , null
    ))
  )) p
  left join poly pl
    on SDO_CONTAINS(pl.poly, p.shape) = 'TRUE'
where 1 = 1
order by
  pl.id


POINT_WKT
CONTAINED_IN
CONTAINED_IN_WKT


POINT (0.0 1.0)
2
POLYGON ((-2.0 -2.0, 2.0 -2.0, 2.0 2.0, -2.0 2.0, -2.0 -2.0))

POINT (0.0 1.0)
3
POLYGON ((-3.0 -3.0, 3.0 -3.0, 3.0 3.0, -3.0 3.0, -3.0 -3.0))

POINT (0.0 1.0)
4
POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0))

POINT (0.0 1.0)
5
POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0))

POINT (0.0 10.0)
null
null

ID	Y	CONTAINED_IN	CONTAINED_IN_WKT
1	1	2	POLYGON ((-2.0 -2.0, 2.0 -2.0, 2.0 2.0, -2.0 2.0, -2.0 -2.0))
1	1	3	POLYGON ((-3.0 -3.0, 3.0 -3.0, 3.0 3.0, -3.0 3.0, -3.0 -3.0))
1	1	4	POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0))
1	1	5	POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0))
2	2	3	POLYGON ((-3.0 -3.0, 3.0 -3.0, 3.0 3.0, -3.0 3.0, -3.0 -3.0))
2	2	4	POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0))
2	2	5	POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0))
3	3	4	POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0))
3	3	5	POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0))
4	4	5	POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0))
5	5	null	null
6	6	null	null
7	7	null	null

POINT_WKT	CONTAINED_IN	CONTAINED_IN_WKT
POINT (0.0 1.0)	2	POLYGON ((-2.0 -2.0, 2.0 -2.0, 2.0 2.0, -2.0 2.0, -2.0 -2.0))
POINT (0.0 1.0)	3	POLYGON ((-3.0 -3.0, 3.0 -3.0, 3.0 3.0, -3.0 3.0, -3.0 -3.0))
POINT (0.0 1.0)	4	POLYGON ((-4.0 -4.0, 4.0 -4.0, 4.0 4.0, -4.0 4.0, -4.0 -4.0))
POINT (0.0 1.0)	5	POLYGON ((-5.0 -5.0, 5.0 -5.0, 5.0 5.0, -5.0 5.0, -5.0 -5.0))
POINT (0.0 10.0)	null	null