Anyone a PL SQL Expert that can help me figure this out? Problem in Oracle Spatial

From: ScottD <svaz_guy_at_yahoo.com>
Date: 16 May 2005 13:42:32 -0700
Message-ID: <1116276152.727823.145950@g43g2000cwa.googlegroups.com>

I have a script that needs to read the longitude and latitude values out of my database, the result of this query needs to be dynamic as it is used to build mapped locations in Oracle Spatial. I just can not seem to get the values of X1, Y1, X2, Y2, to be passed tothe array. Can anyone help?

Here is what it looks like:
The data I need is returned looking like the following: X1 Y1 X2 Y2
-76.217,39.452,-76.742,39.108,
-76.217,39.452,-76.742,39.108,
-96.546,32.657,-98.407,34.659,
-93.200,45.900,-97.384,47.962......

The PLSQL I am running to try and do this is as follows: DECLARE
v_dml LONG;
v_array MDSYS.SDO_GEOMETRY;

x1 NUMBER(8,6);
y1 NUMBER(9,6);
x2 NUMBER(8,6);
y2 NUMBER(9,6);

CURSOR get_coord
IS
SELECT a.longitude,

a.latitude,
b.longitude,
b.latitude

FROM (SELECT d_loc_id,
longitude,
latitude,
d_dev_id
FROM locations,
devices
WHERE d_dt_id = 0

AND d_loc_id = location_id
AND d_ops_status <= 2)a,
(SELECT d_dest_id,

longitude,
latitude,
d_dev_id
FROM locations,
devices
WHERE d_dt_id = 0
AND d_dest_id = location_id
AND d_ops_status <= 2)b
WHERE a.d_dev_id = b.d_dev_id
AND a.d_loc_id <> b.d_dest_id;
BEGIN
OPEN get_coord;
LOOP
FETCH get_coord
INTO x1,
y1,
x2,
y2;
EXIT WHEN get_coord%NOTFOUND;
IF v_array IS NULL THEN
v_array := x1||','||y2||','||x2||','||y2; ELSE
v_array := v_array||','||x1||','||y1||','||x2||','||y2; END IF;
END LOOP;
CLOSE get_coord;
v_dml := 'SELECT MDSYS.SDO_GEOMETRY(2002,8307,null,'|| 'MDSYS.SDO_ELEM_INFO_ARRAY(1,2,1),'||
'MDSYS.SDO_ORDINATE_ARRAY('||v_array||')'; EXECUTE IMMEDIATE(v_dml);
END; results in:

ERROR at line 44:

ORA-06550: line 44, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 44, column 2:

PL/SQL: Statement ignored

ORA-06550: line 46, column 20:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 46, column 9:

PL/SQL: Statement ignored

ORA-06550: line 50, column 12:
PLS-00306: wrong number or types of arguments in call to '||'
ORA-06550: line 50, column 3:

PL/SQL: Statement ignored Received on Mon May 16 2005 - 15:42:32 CDT