Home » Server Options » Spatial » Update SDO_GEOMETRY (Oracle 10g 10.2.0.3.0, Windows XP Professional)
Update SDO_GEOMETRY [message #451643] |
Thu, 15 April 2010 22:50 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
MSAM123
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
Hi All,
The requirement is to update a table's SDO_GEOMETRY column, where in the table is a input parameter.
I'm using the below code to do the same, but when I concatenate the SDO_GEOMETRY object using '||', I get a compilation error as mentioned below.
Could you please let me know the solution for this.
CREATE OR REPLACE PROCEDURE Update_geom(tname IN VARCHAR2, v_id IN NUMBER ,v_gtype IN NUMBER,
v_srid IN NUMBER, v_elem IN SDO_ELEM_INFO_ARRAY,
v_ord IN SDO_ORDINATE_ARRAY)
AS
v_geom SDO_GEOMETRY;
v_sqlstr VARCHAR2(500);
BEGIN
v_geom := SDO_GEOMETRY(v_gtype, v_srid, NULL, v_elem,v_ord);
v_sqlstr := 'update '||tname||' a set a.geom = '||v_geom||'where id = '||v_id;
EXECUTE IMMEDIATE v_sqlstr;
END Update_geom;
/
LINE/COL ERROR
-------- -------------------------------------------------------
9/1 PL/SQL: Statement ignored
9/13 PLS-00306: wrong number or types of arguments in call to '||'
Thanks,
Msam
|
|
|
|
Re: Update SDO_GEOMETRY [message #451646 is a reply to message #451643] |
Thu, 15 April 2010 23:26 ![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) |
MSAM123
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
Hi Michel,
Please see the below example.
SQL> create table lines (id number, geom sdo_geometry);
Table created.
SQL> INSERT INTO LINES (id,geom) VALUES (
2 1,
3 MDSYS.SDO_GEOMETRY
4 (
5 2002,
6 null,
7 null,
8 MDSYS.SDO_ELEM_INFO_ARRAY (1,2,1),
9 MDSYS.SDO_ORDINATE_ARRAY (10,10, 20,25, 30,10, 40,10)
10 )
11 );
1 row created.
SQL> select * from lines;
ID
----
GEOM (SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------
1
SDO_GEOMETRY(2002, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1, 2, 1),
SDO_ORDINATE_ARRAY(10, 10, 20, 25, 30, 10, 40, 10))
Thanks,
Msam
|
|
|
Re: Update SDO_GEOMETRY [message #451647 is a reply to message #451646] |
Thu, 15 April 2010 23:33 ![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) |
MSAM123
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
Another example
SQL> DECLARE
2 v_elem SDO_ELEM_INFO_ARRAY := SDO_ELEM_INFO_ARRAY();
3 v_ord SDO_ORDINATE_ARRAY := SDO_ORDINATE_ARRAY ();
4 v_gtype NUMBER := 2002;
5 v_srid NUMBER := NULL;
6 v_geom SDO_GEOMETRY ;
7
8 BEGIN
9
10 v_elem.extend(3);
11 v_elem(1):= 1;
12 v_elem(2):= 2;
13 v_elem(3):= 1;
14
15 v_ord.extend(6);
16 v_ord(1) := 76767.6767 ;
17 v_ord(2) := 65677.0101 ;
18 v_ord(3) := 43434.3434 ;
19 v_ord(4) := 34344.6875 ;
20 v_ord(5) := 23234.6542 ;
21 v_ord(6) := 76520.4242 ;
22
23 v_geom := SDO_GEOMETRY(v_gtype, v_srid, NULL, v_elem,v_ord);
24 INSERT INTO LINES (id, geom) VALUES (2,v_geom);
25 END ;
26
27
28 /
PL/SQL procedure successfully completed.
SQL> select * from lines;
ID
----------
GEOM(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
----------------------------------------------------------------
1
SDO_GEOMETRY(2002, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1, 2, 1),
SDO_ORDINATE_ARRAY(10, 10, 20, 25, 30, 10, 40, 10))
2
SDO_GEOMETRY(2002, NULL, NULL,
SDO_ELEM_INFO_ARRAY(1, 2, 1),
SDO_ORDINATE_ARRAY(76767.6767, 65677.0101, 43434.3434, 34344.6875, 23234.6542, 76520.4242))
2 rows selected.
|
|
|
Re: Update SDO_GEOMETRY [message #451657 is a reply to message #451646] |
Fri, 16 April 2010 00:46 ![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/102589.gif) |
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
You have to replace '||v_geom||' by:
SDO_GEOMETRY('||v_geom.sgo_type||','||v_geom.sgo_srid||',
SDO_POINT_TYPE('||v_geom.sdo_point.x'||','v_geom.sdo_point.y'||','v_geom.sdo_point.z'||'),
SDO_ELEM_INFO_ARRAY('...
And so on with all the elements of v_geom to build aSDO_GEOMETRY as SQL*Plus displays it.
Regards
Michel
[Updated on: Fri, 16 April 2010 00:46] Report message to a moderator
|
|
|
Re: Update SDO_GEOMETRY [message #451689 is a reply to message #451657] |
Fri, 16 April 2010 02:18 ![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) |
MSAM123
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
Hi Michel,
As suggested by you, I tried all possible options but no success.
I think the '||' operator can not be used to concatenate if a VARRAY is involved.
This is what I tried.
SQL> DECLARE
2 TYPE num_array IS VARRAY(10) OF NUMBER;
3 v_num NUMBER := 10;
4 v_char VARCHAR2(10) := 'STRING';
5 v_conact VARCHAR2(100);
6 v_array_var num_array := num_array();
7 BEGIN
8
9 v_array_var.extend(1);
10 v_array_var(1) := 20;
11 v_conact := v_num||','||v_array_var;
12 dbms_output.put_line(v_conact);
13 END ;
14 /
v_conact := v_num||','||v_array_var;
*
ERROR at line 11:
ORA-06550: line 11, column 14:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 11, column 2:
PL/SQL: Statement ignored
Even I tried using CONACT function instead of '||', I get the below message.
PLS-00306: wrong number or types of arguments in call to 'CONCAT'
Is there any other way to acheive the result.
Any help in this regard will be highly appreciated.
Thank you,
Msam
|
|
|
|
Re: Update SDO_GEOMETRY [message #451707 is a reply to message #451691] |
Fri, 16 April 2010 05:07 ![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) |
MSAM123
Messages: 29 Registered: June 2007
|
Junior Member |
|
|
Hi Michel,
Thank you very much for the solution.
Now it is working fine.
Below is the sample code
DECLARE
v_elem SDO_ELEM_INFO_ARRAY := SDO_ELEM_INFO_ARRAY();
v_ord SDO_ORDINATE_ARRAY := SDO_ORDINATE_ARRAY ();
v_gtype NUMBER := 2002;
v_srid NUMBER := 1632;
v_geom SDO_GEOMETRY ;
v_tname VARCHAR2(30) := 'LINES';
v_sqlstr VARCHAR2(1000);
v_id NUMBER := 1;
v_elem_str VARCHAR2(200);
v_ord_str VARCHAR2(4000);
BEGIN
v_elem.extend(3);
v_elem(1):= 1;
v_elem(2):= 2;
v_elem(3):= 3;
v_ord.extend(6);
v_ord(1) := 1176767.6767 ;
v_ord(2) := 2265677.0101 ;
v_ord(3) := 3343434.3434 ;
v_ord(4) := 4434344.6875 ;
v_ord(5) := 5523234.6542 ;
v_ord(6) := 6676520.4242 ;
FOR p IN v_elem.first..v_elem.last
LOOP
v_elem_str := v_elem_str||','||v_elem(p);
END LOOP;
-- This is to remove the extra comma at the beginning of the string
v_elem_str := SUBSTR(v_elem_str,2,LENGTH(v_elem_str) - 1);
FOR p IN v_ord.first..v_ord.last
LOOP
v_ord_str := v_ord_str||','||v_ord(p);
END LOOP ;
-- This is to remove the extra comma at the beginning of the string
v_ord_str := SUBSTR(v_ord_str,2,LENGTH(v_ord_str) - 1);
v_sqlstr := 'UPDATE '||v_tname||' a SET a.geom = SDO_GEOMETRY('
||v_gtype||','
||v_srid||','
||'NULL'||','
||'MDSYS.SDO_ELEM_INFO_ARRAY('||v_elem_str||')'||','
||'MDSYS.SDO_ORDINATE_ARRAY('||v_ord_str||')'
||')'
||' WHERE id = '||v_id;
EXECUTE IMMEDIATE v_sqlstr;
END ;
/
Thanks & Regards,
Msam
[Updated on: Fri, 16 April 2010 05:18] Report message to a moderator
|
|
|
|
Re: Update SDO_GEOMETRY [message #452539 is a reply to message #451708] |
Thu, 22 April 2010 09:02 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
Albert Godfrind
Messages: 2 Registered: September 2002
|
Junior Member |
|
|
The simplest and also most efficient solution is just to use bind variables, like this:
CREATE OR REPLACE PROCEDURE Update_geom(tname IN VARCHAR2, v_id IN NUMBER ,v_gtype IN NUMBER,
v_srid IN NUMBER, v_elem IN SDO_ELEM_INFO_ARRAY,
v_ord IN SDO_ORDINATE_ARRAY)
AS
v_geom SDO_GEOMETRY;
v_sqlstr VARCHAR2(500);
BEGIN
v_geom := SDO_GEOMETRY(v_gtype, v_srid, NULL, v_elem,v_ord);
v_sqlstr := 'update '||tname||' a set a.geom = :1 where id = :2';
EXECUTE IMMEDIATE v_sqlstr using v_geom, v_id;
END Update_geom;
/
It has the added benefit that you will be reusing the same statement for all updates on a given table, so saving on the hard query parsing costs.
Albert
|
|
|
Goto Forum:
Current Time: Sat Sep 07 18:33:18 CDT 2024
|