Using Cursor to Update Of Table in SQL Developer using Spatial Data

Question

I have already transformed my table's geometries to a new table which only has the two fields (Geometry and SDO_RowID)

I need to take all of the data that is in the original table and bring it over to the new table. I created the columns in the new table and now I'm trying to bring the data over using a For Update Of cursor.

I'm just not sure how to loop through all of the data in the correct way/to make it work. Thanks.

DECLARE
    CURSOR OTTAWACOLLECTS IS 
    SELECT SHAPE_LENG,
           CLASS,
           SUBCLASSIF,
           ROAD_NAME,
           ROAD_TYPE,
           ROAD_DIREC,
           MUNICIPALI,ADDRESS_LE,
           ADDRESS__1, 
           ADDESSS_RI,
           ADDRESS_RI, ROAD_NAME_
    FROM OTTAWACOLLECTORS
    WHERE OTTAWACOLLECTORS_NAD.SDO_ROWID = ROWID
    FOR UPDATE OF OTTAWACOLLECTORS_NAD;

BEGIN
    FOR ROWID IN OTTAWACOLLECTS
    LOOP
        UPDATE OTTAWACOLLECTORS_NAD
        SET SHAPE_LENG = OTTAWACOLLECTORS.SHAPE_LENG,
            CLASS = OTTAWACOLLECTORS.CLASS,
            SUBCLASSIF = OTTAWACOLLECTORS.SUBCLASSIF,
            ROAD_NAME = OTTAWACOLLECTORS.ROAD_NAME,
            ROAD_TYPE = OTTAWACOLLECTORS.ROAD_TYPE,
            ROAD_DIREC = OTTAWACOLLECTORS.ROAD_DIREC,
            MUNICIPALI = OTTAWACOLLECTORS.MUNICIPALI,
            ADDRESS_LE = OTTAWACOLLECTORS.ADDRESS_LE,
            ADDRESS__1 = OTTAWACOLLECTORS.ADDRESS__1,
            ADDRESSS_RI = OTTAWACOLLECTORS.ADDESSS_RI,
            ADDRESS_RI = OTTAWACOLLECTORS.ADDRESS_RI,
            ROAD_NAME = OTTAWACOLLECTORS.ROAD_NAME_
        WHERE CURRENT OF OTTAWACOLLECTS;
    END LOOP;
END;

Answer 1

It looks like you got the new table using the sdo_cs.transform_layer() function. IMO you are just making your life unnecessarily complex with that approach. There are two simpler ways:

1) Just transform the geometries in the original table. Or if you need geometries in both coordinate systems (the original one and the new one), then just add a second geometry column in your origin table. You can even link them via a trigger so that when the "master" geometry gets updated, then the transformed one gets automatically updated.

alter table ottawacollectors add geometry_nad do_geometry;
update ottawacollectors set geometry_nad = sdo_cs.transform (geometry, <your new SRID>);
commit;

then add metadata and a spatial index on the additional column.

The benefit is that you do not need to deal with two separate tables, both with the same content that you need to keep synchronized.

2) Or if you really want a full copy of the original table with the transformed geometries, then just create that new table and transform at the same time, like this:

create table ottawacollectors_nad as
select ... (all columns) ..., sdo_cs.transform (geometry, <your new SRID>) geometry
from ottawacollectors;

Or even simpler:

create table ottawacollectors_nad as select * from ottawacollectors;

followed by:

update ottawacollectors_nad set geometry = sdo_cs.transform (geometry, <your new SRID>);
commit;

both followed by the usual setting up of metadata and the creation of a spatial index.