SCOTT@orcl_11gR2> CREATE TABLE piper_equipment
  2    (structure_category_name  VARCHAR2 (10),
  3  	bt_object_subid 	 NUMBER,
  4  	bt_owning_node_id	 NUMBER,
  5  	bt_object_id		 NUMBER,
  6  	shape			 MDSYS.SDO_GEOMETRY)
  7  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO piper_equipment VALUES
  2    ('scn1', 1, 1, 1,
  3  	SDO_GEOMETRY
  4  	  (2003, NULL, NULL,
  5  	   SDO_ELEM_INFO_ARRAY (1, 1003, 3),
  6  	   SDO_ORDINATE_ARRAY (1, 1, 5, 7)))
  7  /

1 row created.

SCOTT@orcl_11gR2> INSERT INTO user_sdo_geom_metadata
  2    (table_name, column_name, diminfo, srid)
  3  VALUES
  4    ('piper_equipment', 'shape',
  5  	SDO_DIM_ARRAY
  6  	  (SDO_DIM_ELEMENT ('X', 0, 20, 0.005),
  7  	   SDO_DIM_ELEMENT ('Y', 0, 20, 0.005)),
  8  	NULL)
  9  /

1 row created.

SCOTT@orcl_11gR2> CREATE INDEX pe_idx ON piper_equipment (shape)
  2  INDEXTYPE IS MDSYS.SPATIAL_INDEX
  3  /

Index created.

SCOTT@orcl_11gR2> CREATE TABLE l_bar_noncopper_add
  2  	 (OBJECTID		 NUMBER(38,0),
  3  	  SHAPE 		 SDO_GEOMETRY,
  4  	  UDPRN 		 VARCHAR(12 BYTE),
  5  	  NADALK		 VARCHAR(12 BYTE),
  6  	  EXCHANGE_1141_CODE	 VARCHAR(12 BYTE))
  7  /

Table created.

SCOTT@orcl_11gR2> INSERT INTO l_bar_noncopper_add VALUES
  2    (1,
  3  	SDO_GEOMETRY
  4  	  (2003, NULL, NULL,
  5  	   SDO_ELEM_INFO_ARRAY (1, 1003, 3),
  6  	   SDO_ORDINATE_ARRAY (1, 1, 5, 7)),
  7  	'udprn1', 'nadalk1', 'exchcode1')
  8  /

1 row created.

SCOTT@orcl_11gR2> DECLARE
  2    stmt			 VARCHAR2(2000);
  3    VWNONCOPPERADD		 VARCHAR2(30) := 'l_bar_noncopper_add';
  4    exchCode 		 VARCHAR(12 BYTE) := 'exchcode1';
  5    unservedCursor		 SYS_REFCURSOR;
  6    TYPE data_record IS RECORD
  7  	 (OBJECTID		 NUMBER(38,0),
  8  	  SHAPE 		 L_BAR_NONCOPPER_ADD.SHAPE%TYPE,
  9  	  UDPRN 		 VARCHAR(12 BYTE),
 10  	  NADALK		 VARCHAR(12 BYTE),
 11  	  EXCHANGE_1141_CODE	 VARCHAR(12 BYTE));
 12    unservedValue		 DATA_RECORD;
 13    v_sDPsct 		 piper_equipment.structure_category_name%TYPE;
 14    v_saddDPdist		 NUMBER;
 15    v_dpTypeNearSrvAdd	 piper_equipment.bt_object_subid%TYPE;
 16    v_sbtOwningNodeID	 piper_equipment.bt_owning_node_id%TYPE;
 17    v_saddDPid		 piper_equipment.bt_object_id%TYPE := 1;
 18    stmt_piperequipment3	 VARCHAR2(2000);
 19    vwpiperdata		 VARCHAR2(30) := 'PIPER_EQUIPMENT';
 20  BEGIN
 21    stmt :=
 22  	 'SELECT * FROM ' || VWNONCOPPERADD ||
 23  	' WHERE exchange_1141_code = '''||exchCode||'''';
 24    OPEN unservedCursor FOR stmt;
 25    LOOP
 26  	 FETCH unservedCursor INTO unservedValue;
 27  	 EXIT WHEN unservedCursor%NOTFOUND;
 28  	 stmt_piperequipment3 :=
 29  	   'SELECT pe.structure_category_name,
 30  		   SDO_NN_DISTANCE(2),
 31  		   pe.bt_object_subid,
 32  		   pe.bt_owning_node_id
 33  	    FROM   ' || VWPIPERDATA || ' pe
 34  	    WHERE  SDO_NN
 35  		     (pe.shape,
 36  		      (:b_shape),
 37  		      ''sdo_batch_size=10'',
 38  		      2) = ''TRUE''
 39  	    AND    ROWNUM < 2
 40  	    AND    pe.bt_object_id = :b_saddDPid';
 41  	 DBMS_OUTPUT.PUT_LINE (stmt_piperequipment3);
 42  	 EXECUTE IMMEDIATE stmt_piperequipment3
 43  	    INTO   v_sDPsct,
 44  		   v_saddDPdist,
 45  		   v_dpTypeNearSrvAdd,
 46  		   v_sbtOwningNodeID
 47  	    USING  unservedValue.shape, v_saddDPid;
 48  	 DBMS_OUTPUT.PUT_LINE (v_sDPsct);
 49  	 DBMS_OUTPUT.PUT_LINE (v_saddDPdist);
 50  	 DBMS_OUTPUT.PUT_LINE (v_dpTypeNearSrvAdd);
 51  	 DBMS_OUTPUT.PUT_LINE (v_sbtOwningNodeID);
 52    END LOOP;
 53  END;
 54  /
SELECT pe.structure_category_name,
              SDO_NN_DISTANCE(2),

pe.bt_object_subid,
              pe.bt_owning_node_id
       FROM
PIPER_EQUIPMENT pe
       WHERE  SDO_NN
                (pe.shape,

(:b_shape),
                 'sdo_batch_size=10',
                 2) = 'TRUE'

AND    ROWNUM < 2
       AND    pe.bt_object_id = :b_saddDPid
scn1
0
1
1

PL/SQL procedure successfully completed.