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

Not and expert, but

ScottD wrote:
> 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;

define a record based on the cursor rowtype here. i.e. lat_long_rcd get_coord%rowtype

> BEGIN
> OPEN get_coord;
> LOOP
> FETCH get_coord

fetch into the record here, instead of the individual variables

> INTO x1,
> y1,
> x2,
> y2;
> EXIT WHEN get_coord%NOTFOUND;
> IF v_array IS NULL THEN

change all refs to x1, etc, to the fields in the record

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

use some white space and indenting to make your code easier to read. Received on Mon May 16 2005 - 17:07:54 CDT