As a part of my last article on tracking down and correcting ORA-13011 errors when using SDO_GEOM.VALIDATE_LAYER_WITH_CONTEXT() , I showed how to update the diminfo structure associated with a table’s geometry column in its USER_SDO_GEOM_METADATA entry.

Here was the code in that article.

UPDATE user_sdo_geom_metadata 
   SET diminfo = ( SELECT MDSYS.SDO_DIM_ARRAY( 
                                MDSYS.SDO_DIM_ELEMENT('X', minx, maxx, 0.05), 
                                MDSYS.SDO_DIM_ELEMENT('Y', miny, maxy, 0.05)) as diminfo 
                     FROM ( SELECT TRUNC( MIN( v.x ) - 1,0) as minx,
                                   ROUND( MAX( v.x ) + 1,0) as maxx,
                                   TRUNC( MIN( v.y ) - 1,0) as miny,
                                   ROUND( MAX( v.y ) + 1,0) as maxy
                              FROM (SELECT SDO_AGGR_MBR(a.geometry) as mbr
                                      FROM rectangles a) b,
                                           TABLE(mdsys.sdo_util.getvertices(b.mbr)) v
                           )
                 )
 WHERE table_name = 'RECTANGLES'
   AND column_name = 'GEOMETRY';

Now, that code is pretty neat, but it has in it a few things that need changing every time you want to use it for another table’s geometry column, or you want to change the MBR shrinkage/expansion factor (ie – 1/+ 1), or you can’t remember the sdo_tolerance values (in the above, 0.05).

What we need to do is encapsulate this in a PL/SQL Procedure that you can call anytime you need to. Here is one such implementation that is, I believe, generic enough to do all that is required.

   /** function    UpdateSdoMetadata
   *   description Updates 2D spatial extent of DIMINFO associated with table/column in all_sdo_geom_metadata
   *   param       p_table_name  The object containing the spatal data.
   *   param       p_column_name The sdo_geometry column to be analyzed.
   *   param       p_mbr_factor  Expansion/Shrinkage amount for MBR of current data.
   *   param       p_commit      Whether to commit the update.
   */
Create Or Replace
   Procedure UpdateSdoMetadata( p_table_name  in varchar2,
                                p_column_name in varchar2,
                                p_mbr_factor  in number,
                                p_commit      in boolean := false )
   As
     v_mbr_factor number := case when p_mbr_factor is null then 0 else p_mbr_factor end;
     v_diminfo    mdsys.sdo_dim_array;
   Begin
     -- Check if something to process
     If ( p_table_name is null or p_column_name is null ) Then
       Return;
     End If;
 
     -- Get existing record (checks if one even exists)
     --
     SELECT diminfo
       INTO v_diminfo
       FROM user_sdo_geom_metadata
      WHERE table_name  = UPPER(p_table_name)
        AND column_name = UPPER(p_column_name);
 
      -- Update the diminfo with the MBR of the existing data
      EXECUTE IMMEDIATE 'SELECT MDSYS.SDO_DIM_ARRAY( 
                                MDSYS.SDO_DIM_ELEMENT(''X'', minx, maxx, :1), 
                                MDSYS.SDO_DIM_ELEMENT(''Y'', miny, maxy, :2)) as diminfo 
                     FROM ( SELECT TRUNC( MIN( v.x ) - :3,0) as minx,
                                   ROUND( MAX( v.x ) + :4,0) as maxx,
                                   TRUNC( MIN( v.y ) - :5,0) as miny,
                                   ROUND( MAX( v.y ) + :6,0) as maxy
                              FROM (SELECT SDO_AGGR_MBR(a.' || p_column_name || ') as mbr
                                      FROM ' || p_table_name || ' a) b,
                                           TABLE(mdsys.sdo_util.getvertices(b.mbr)) v
                           )'
                 INTO v_diminfo
                USING v_diminfo(1).sdo_tolerance, 
                      v_diminfo(2).sdo_tolerance,
                      v_mbr_factor,v_mbr_factor,v_mbr_factor,v_mbr_factor;
                       
     -- Now update the existing record
     --
     UPDATE user_sdo_geom_metadata 
        SET diminfo     = v_diminfo
      WHERE table_name  = UPPER(p_table_name)
        AND column_name = UPPER(p_column_name);
 
    -- Commit if requested
    If ( p_commit ) Then
      commit;
    End If;
 
    Return;
 
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
         raise_application_error(-20000, 'No SDO_METADATA record exists for ' || p_table_name || '.' || p_column_name || '. Run MetadataAnalayzer');
   End UpdateSdoMetadata;

I’ve placed this in the TOOLS package in my free PL/SQL packages.

I hope the code is useful to someone.