Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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?

share|improve this question
    
I noticed your question on an oracle forum. You might try on the spatial forum. I do know the minds there are quite capable of answering this type of question. forums.oracle.com/forums/forum.jspa?forumID=76&start=15 –  Brad Nesom Nov 28 '11 at 18:34

1 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;
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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