Changing all DIMINFO sdo_tolerance values for all metadata records in one go.

Someone contacted me about having to change all the tolerances in their user_sdo_geom_metadata due to a need to change each sdo_tolerance value from 0.005 (1cm) to 0.0005 (1mm) meters.

 SET serveroutput ON SIZE unlimited
    cursor meta IS
     SELECT TABLE_NAME, column_name, diminfo
       FROM user_sdo_geom_metadata
        FOR UPDATE OF diminfo;
   FOR rec IN meta loop
     IF (rec.diminfo IS NOT NULL AND rec.diminfo.COUNT >= 2) THEN
        dbms_output.put_line(rec.TABLE_NAME || '.' || rec.column_name ||
                             ' diminfo tolerances are ' ||
                             ' X=' || rec.diminfo(1).sdo_tolerance ||
                             ' Y=' || rec.diminfo(2).sdo_tolerance);
        rec.diminfo(1).sdo_tolerance := rec.diminfo(1).sdo_tolerance; -- 0.05;
        rec.diminfo(2).sdo_tolerance := rec.diminfo(2).sdo_tolerance; -- 0.05;
        UPDATE user_sdo_geom_metadata
           SET diminfo = rec.diminfo
         WHERE CURRENT OF meta;
     END IF;
   END loop;

I hope this helps someone out there…

Leave a Reply

Your email address will not be published. Required fields are marked *