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

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))

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.

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('...

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

PLS-00306: wrong number or types of arguments in call to 'CONCAT'

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