Error in passing geometry column to SQL query

Question

When I execute the following query with a static table name it's working fine:

SELECT pe.structure_category_name,SDO_NN_DISTANCE(2),pe.bt_object_subid,
pe.bt_owning_node_id INTO v_sDPsct,v_saddDPdist,v_dpTypeNearSrvAdd,
v_sbtOwningNodeID FROM piper_equipment pe WHERE
SDO_NN(pe.shape,(unservedValue.shape),'sdo_batch_size=10',2) = 'TRUE' AND ROWNUM < 2 AND pe.bt_object_id = v_saddDPid

Here unservedValue is the data_record fetching through cursor. I have to pass table name as variable , so I have changed above query into the following:

stmt_piperequipment3 := 'SELECT pe.structure_category_name,SDO_NN_DISTANCE(2), 
pe.bt_object_subid,
pe.bt_owning_node_id INTO v_sDPsct,v_saddDPdist,v_dpTypeNearSrvAdd, v_sbtOwningNodeID 
FROM '||VWPIPERDATA||' pe WHERE
SDO_NN(pe.shape,(unservedValue.shape),''sdo_batch_size=10'',2) = ''TRUE'' AND
ROWNUM < 2 AND pe.bt_object_id = v_saddDPid';
execute immediate stmt_piperequipment3;

While executing above query it's giving following error

ORA-00904: "UNSERVEDVALUE"."SHAPE":invalid identifier.

My question is how to pass a shape into the string variable?

Answer 1

My previous answer was not correct, sorry but I was misled by the term 'string variable'. I thought that unservedValue.shape was actually a column. My bad.

One of the documentation link is actually key to the solution anyway. Have you tried something like this:

stmt_piperequipment3 := 'SELECT pe.structure_category_name,SDO_NN_DISTANCE(2), 
pe.bt_object_subid,
pe.bt_owning_node_id INTO v_sDPsct,v_saddDPdist,v_dpTypeNearSrvAdd, v_sbtOwningNodeID 
FROM '||VWPIPERDATA||' pe WHERE
SDO_NN(pe.shape, :1 ,''sdo_batch_size=10'',2) = ''TRUE'' AND
ROWNUM < 2 AND pe.bt_object_id = v_saddDPid';
execute immediate stmt_piperequipment3 using unservedValue.shape;