Home » Server Options » Spatial » Error in passing geometry column to SQL query
Error in passing geometry column to SQL query [message #533113] Mon, 28 November 2011 05:53 Go to next message
neetugulati
Messages: 6
Registered: November 2011
Location: pune
Junior Member
When I execute following query with static table name its 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 Its giving following error

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

My question is how to pass shape into the string variable.
Re: Error in passing geometry column to SQL query [message #533126 is a reply to message #533113] Mon, 28 November 2011 06:49 Go to previous messageGo to next message
Hassankse
Messages: 2
Registered: November 2011
Location: Karachi, Pakistan
Junior Member

Dear Netuu,

'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';

you have witen like this but it is worng, ''sdo_batch_size=10'', you cann't use single code(') twise,

'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),'||chr(39)||'sdo_batch_size=10'||chr(39)||',2) = '||chr(39)||'TRUE'||chr(39)||' AND ROWNUM < 2 AND pe.bt_object_id = v_saddDPid';

kindly write chr(39), it is will return a single code.

Re: Error in passing geometry column to SQL query [message #533158 is a reply to message #533113] Mon, 28 November 2011 09:17 Go to previous messageGo to next message
jrnayak
Messages: 35
Registered: November 2011
Location: London
Member
Hi Neetu,

The double quotation are fine.

remove into from select and put it in execute immediate .

stmt_piperequipment3 := 'SELECT pe.structure_category_name,SDO_NN_DISTANCE(2),pe.bt_object_subid,
pe.bt_owning_node_id 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 INTO v_sDPsct,v_saddDPdist,v_dpTypeNearSrvAdd, v_sbtOwningNodeID ;

Re: Error in passing geometry column to SQL query [message #533228 is a reply to message #533158] Mon, 28 November 2011 23:54 Go to previous messageGo to next message
neetugulati
Messages: 6
Registered: November 2011
Location: pune
Junior Member
Thanks all for your reply.I am getting problem in passing geometry column i.e., unservedValue.shape.

I am writing here few lines from my procedure to understand from where I am getting "unservedValue"

TYPE data_record IS RECORD(OBJECTID NUMBER(38,0),SHAPE FTTP_ADDRESS.L_BAR_NONCOPPER_ADD.SHAPE%TYPE,UDPRN VARCHAR(12 BYTE),NADALK VARCHAR(12 BYTE),EXCHANGE_1141_CODE VARCHAR(12 BYTE));
unservedCursor SYS_REFCURSOR;
unservedValue DATA_RECORD;
stmt VARCHAR2(2000);

BEGIN
stmt := 'SELECT * FROM ' || VWNONCOPPERADD|| ' WHERE exchange_1141_code = '''||exchCode||'''';

FETCH unservedCursor INTO unservedValue;

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;
In running mode of execution of this procedure,its giving error on this line "unservedValue.shape"
How to pass shape/geometry column to the string.
Hope I am able to explain my quetion.
Re: Error in passing geometry column to SQL query [message #533277 is a reply to message #533228] Tue, 29 November 2011 03:24 Go to previous messageGo to next message
jrnayak
Messages: 35
Registered: November 2011
Location: London
Member
the code should be like below. I have removed the "INTO v_sDPsct,v_saddDPdist,v_dpTypeNearSrvAdd, v_sbtOwningNodeID" and put in the execute immediate statement.

TYPE data_record IS RECORD(OBJECTID NUMBER(38,0),SHAPE FTTP_ADDRESS.L_BAR_NONCOPPER_ADD.SHAPE%TYPE,UDPRN VARCHAR(12 BYTE),NADALK VARCHAR(12 BYTE),EXCHANGE_1141_CODE VARCHAR(12 BYTE));
unservedCursor SYS_REFCURSOR;
unservedValue DATA_RECORD;
stmt VARCHAR2(2000);

BEGIN
stmt := 'SELECT * FROM ' || VWNONCOPPERADD|| ' WHERE exchange_1141_code = '''||exchCode||'''';

FETCH unservedCursor INTO unservedValue;

stmt_piperequipment3 := 'SELECT pe.structure_category_name,SDO_NN_DISTANCE(2),pe.bt_object_subid,
pe.bt_owning_node_id 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 INTO v_sDPsct,v_saddDPdist,v_dpTypeNearSrvAdd, v_sbtOwningNodeID ;
Re: Error in passing geometry column to SQL query [message #533281 is a reply to message #533277] Tue, 29 November 2011 03:40 Go to previous messageGo to next message
neetugulati
Messages: 6
Registered: November 2011
Location: pune
Junior Member
Thanks, I did the same as instructed by you. Now, I am getting following error.

ORA-00900: invalid SQL statement.

I think problem is in passing 'unservedValue.shape' in query statement.
Please suggest.
Re: Error in passing geometry column to SQL query [message #533285 is a reply to message #533281] Tue, 29 November 2011 03:52 Go to previous messageGo to next message
jrnayak
Messages: 35
Registered: November 2011
Location: London
Member
Take dbms output of that sql statement and see what it returns.run the query and then you will know exactly where is the problem.

FETCH unservedCursor INTO unservedValue;

stmt_piperequipment3 := 'SELECT pe.structure_category_name,SDO_NN_DISTANCE(2),pe.bt_object_subid,
pe.bt_owning_node_id 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';

dbms_output.put_line(stmt_piperequipment3);

execute immediate stmt_piperequipment3 INTO v_sDPsct,v_saddDPdist,v_dpTypeNearSrvAdd, v_sbtOwningNodeID ;
Re: Error in passing geometry column to SQL query [message #533319 is a reply to message #533285] Tue, 29 November 2011 07:32 Go to previous messageGo to next message
neetugulati
Messages: 6
Registered: November 2011
Location: pune
Junior Member
Hi,

in output, I am getting following result.

SELECT pe.structure_category_name,SDO_NN_DISTANCE(2),pe.bt_object_subid, pe.bt_owning_node_id FROM L_BAR_PIPER_DATA pe WHERE SDO_NN(pe.shape,(unservedValue.shape),'sdo_batch_size=10',2) = 'TRUE' AND ROWNUM < 2 AND pe.bt_object_id = '11'
Re: Error in passing geometry column to SQL query [message #533325 is a reply to message #533319] Tue, 29 November 2011 08:08 Go to previous messageGo to next message
jrnayak
Messages: 35
Registered: November 2011
Location: London
Member
You should pass (unservedValue.shape) as a variable. The statement should be like this

stmt_piperequipment3 := 'SELECT pe.structure_category_name,SDO_NN_DISTANCE(2),pe.bt_object_subid,
pe.bt_owning_node_id 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';

Re: Error in passing geometry column to SQL query [message #533363 is a reply to message #533325] Tue, 29 November 2011 10:49 Go to previous messageGo to next message
neetugulati
Messages: 6
Registered: November 2011
Location: pune
Junior Member
Thanks for your reply.
Whenever I passed "unservedValue.shape" as a variable.it is giving error because it is a type of SDO_GEOMETRY and will not be able to concanate into the string stmt_piperequipment3.

how can we pass parameter of type shape/geometry into the string variable.
Re: Error in passing geometry column to SQL query [message #533376 is a reply to message #533363] Tue, 29 November 2011 12:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
It would have helped if you had provided a complete reproducible test case, as I have done below. As previously mentioned by others, you need to separate the INTO clause. I have also used bind variables and a USING clause. It is this use of bind variable and parameter in the USING clause that allows you to pass the unservedValue.shape as a variable.

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.

Re: Error in passing geometry column to SQL query [message #536706 is a reply to message #533376] Thu, 22 December 2011 04:46 Go to previous message
neetugulati
Messages: 6
Registered: November 2011
Location: pune
Junior Member
Hi,
Thanks for your reply.
your solution worked.
Sorry for delay in reply.
Previous Topic: Merging line geomrtry in a particular order
Next Topic: UNION operator with SDO_GEOMETRY column
Goto Forum:
  


Current Time: Thu Mar 28 03:14:20 CDT 2024