4.15 Updating GeoRaster Objects in a Loop

When multiple GeoRaster objects are to be updated, a cursor is generally used. However, if the GeoRaster operation in the loop is parallel enabled or the COMMIT statement is executed after each UPDATE statement, the SQL query for the cursor cannot have the FOR UPDATE clause because the commit within the loop will invalidate the cursor with that clause.

Example 4-5 Updating GeoRaster Objects in a Loop With Parallel Processing Enabled

In the following example, pyramids were generated on all the GeoRaster objects in the table. An explicit commit is executed after each update, and parallel processing is enabled (parallel=4) for the SDO_GEOR.generatePyramid procedure. The query of the GeoRaster object with FOR UPDATE clause is executed within the loop, not in the cursor query statement.

DECLARE
    gr1 sdo_georaster;
BEGIN
    FOR rec in (SELECT georid FROM georaster_table ORDER BY georid) LOOP
      SELECT georaster INTO gr1 FROM georaster_table WHERE georid=rec.georid FOR UPDATE;
      sdo_geor.generatePyramid(gr1, 'rlevel=5, resampling=bilinear', null, 'parallel=4');
      UPDATE georaster_table SET georaster=gr1 WHERE georid=rec.georid;
      COMMIT;
    END LOOP;
 END;
/

Example 4-6 Updating GeoRaster Objects in a Loop Without Parallel Processing Enabled

If the GeoRaster operation in the loop is not parallel enabled (as is the case in this example), instead of executing a COMMIT after each update, the COMMIT statement can be executed outside the cursor loop, to avoid invalidating the cursor with FOR UPDATE clause. Also note that the example adds NODATA to all the GeoRaster objects in the table.

BEGIN
FOR rec in (SELECT georid, georaster FROM georaster_table FOR UPDATE)
    LOOP
      sdo_geor.addNODATA(rec.georaster, 0, 0);
      UPDATE georaster_table SET georaster=rec.georaster 
        WHERE georid=rec.georid;
END LOOP;
COMMIT;
 END;
/