4

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?

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.