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
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 #451645 is a reply to message #451643] Thu, 15 April 2010 22:55 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't know SDO_GOMETRY but please post an example of an (static) insert statement to insert such a value in a column.
If you know how to do it for a static insert, then you should be able to get the right syntax from a string.

Regards
Michel
Re: Update SDO_GEOMETRY [message #451646 is a reply to message #451643] Thu, 15 April 2010 23:26 Go to previous messageGo to next message
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 messageGo to next message
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 messageGo to next message
Michel Cadot
Messages: 68624
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 messageGo to next message
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 #451691 is a reply to message #451689] Fri, 16 April 2010 02:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't do as I showed.
You have to drop down each component still the leaves.
Each part of || MUST be string.
For a varray (of string or number), you have to loop on all elements of the varray.

Regards
Michel
Re: Update SDO_GEOMETRY [message #451707 is a reply to message #451691] Fri, 16 April 2010 05:07 Go to previous messageGo to next message
MSAM123
Messages: 29
Registered: June 2007
Junior Member
Hi Michel,

Thank you very much for the solution.
Now it is working fine. Smile

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 #451708 is a reply to message #451707] Fri, 16 April 2010 05:16 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thank you for the feedback and code.

Regards
Michel
Re: Update SDO_GEOMETRY [message #452539 is a reply to message #451708] Thu, 22 April 2010 09:02 Go to previous message
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
Previous Topic: how to solve ORA-0939
Next Topic: ORA-29532: Java call terminated by uncaught Java exception
Goto Forum:
  


Current Time: Thu Mar 28 06:49:06 CDT 2024