SELECT g.obj_id
FROM   wzw_pand g
WHERE  sdo_relate (g.centroid, geef_buffer(120133, 488623), 'mask=INSIDE querytype=WINDOW') = 'TRUE'
AND   (g.ind_nultrede = 'J');

------------------------------------------------------

CREATE OR REPLACE FUNCTION GEEF_BUFFER
(      p_xcoord number
,      p_ycoord number) return mdsys.sdo_geometry
is
  --
  cursor c1
  is
  SELECT p.centroid.sdo_point.x AS xcoord,
         p.centroid.sdo_point.y AS ycoord,
         sdo_nn_distance(1) AS nn_afstand
  FROM   wzw_pand p
  WHERE  ind_afroep = 'J' -- geen index op deze kolom!
  AND    sdo_nn (p.centroid,
                 mdsys.sdo_geometry(2001,
                                    90112,
                                    mdsys.sdo_point_type(p_xcoord, p_ycoord, NULL),
                                    NULL,
                                    NULL),
                                    'sdo_batch_size=0 unit=meter', 1) = 'TRUE'
and rownum = 1
order by nn_afstand;
  --
  l_afstand number;
  l_bufferwaarde number;
  l_geometrie mdsys.sdo_geometry;
  --
begin
  --
  l_afstand := null;
  l_bufferwaarde := null;
  l_geometrie := null;
  --
  for r1 in c1 loop
   l_afstand := r1.nn_afstand;

    if l_afstand <= 200 then
     l_bufferwaarde := 200;

     else if l_afstand > 200 and l_afstand <= 300 then
      l_bufferwaarde := 300;

      else l_bufferwaarde := 0.001;

     end if;
    end if;

   l_geometrie := mdsys.sdo_geom.sdo_buffer(mdsys.sdo_geometry(2001,
                                                               90112,
                                                               mdsys.sdo_point_type(r1.xcoord, r1.ycoord, NULL),
                                                               NULL,
                                                               NULL), l_bufferwaarde, 0.5);

  end loop;
  --
  return (l_geometrie);
end geef_buffer;
/

MYDBA@orcl > create table test (a) as select rownum from all_objects where rownum <= 10;

Table created.

MYDBA@orcl > select * from test order by a;

         A
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10

10 rows selected.

MYDBA@orcl > select * from test order by a desc;

         A
----------
        10
         9
         8
         7
         6
         5
         4
         3
         2
         1

10 rows selected.

MYDBA@orcl > select * from test where rownum = 1 order by a desc;

         A
----------
         1

1 row selected.

MYDBA@orcl > select * from (select * from test order by a desc) where rownum = 1;

         A
----------
        10

1 row selected.