Changing all DIMINFO sdo_tolerance values for all metadata records in one go.
Keywords user_sdo_geom_metadata sdo_tolerance change allSummary This article shows how a simple piece of anonymous pl/sql can be used to change all sdo_tolerance values in user_sdo_geom_metadata diminfo structures in one task.
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
DECLARE
cursor meta IS
SELECT TABLE_NAME , column_name, diminfo
FROM user_sdo_geom_metadata
FOR UPDATE OF diminfo;
BEGIN
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;
commit;
END ;
/
I hope this helps someone out there…
Article Navigation:
Previous
SpatialDB Advisor
Home
Comment