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; /
Parent topic: GeoRaster Data Query and Manipulation