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 Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c7340d07f1ccc73d3419ab70fcef6040?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/143cf1fff7c304015782aea41ae84527?s=64&d=mm&r=g) |
Hassankse
Messages: 2 Registered: November 2011 Location: Karachi, Pakistan
|
Junior Member |
![hassan.siddique81%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/328128e98b5752f102c3f39db8a6da66?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/c7340d07f1ccc73d3419ab70fcef6040?s=64&d=mm&r=g) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/328128e98b5752f102c3f39db8a6da66?s=64&d=mm&r=g) |
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 #533285 is a reply to message #533281] |
Tue, 29 November 2011 03:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/328128e98b5752f102c3f39db8a6da66?s=64&d=mm&r=g) |
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 #533376 is a reply to message #533363] |
Tue, 29 November 2011 12:28 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/43710.gif) |
Barbara Boehmer
Messages: 9097 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.
|
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:33:47 CDT 2024
|