SET SERVEROUTPUT ON
SET LINESIZE 500
DECLARE
v_elementid cmpobjecttype_r.elementid%TYPE;
v_logical_name cmpobjecttype_r.logicalname%TYPE;
CURSOR c1 IS SELECT elementid, logicalname FROM cmpobjecttype_r WHERE logicalname IN ('SDO_GEOMETRY', 'SDO_POINT_TYPE', 'SDO_DIM_ELEMENT', 'SDOAGGRTYPE');
BEGIN
OPEN c1;
LOOP
  FETCH c1 INTO v_elementid, v_logical_name;
  EXIT WHEN c1%NOTFOUND;
  
  DBMS_OUTPUT.PUT_LINE('Element ID: ' || v_elementid); 
  DBMS_OUTPUT.PUT_LINE('Logical Name: ' || v_logical_name); 

  if v_logical_name = 'SDO_GEOMETRY' then
   execute immediate(' UPDATE cmprecordfield_r SET position = 1 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''SDO_GTYPE''');
   execute immediate(' UPDATE cmprecordfield_r SET position = 2 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''SDO_SRID''');
   execute immediate(' UPDATE cmprecordfield_r SET position = 3 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''SDO_POINT''');
   execute immediate(' UPDATE cmprecordfield_r SET position = 4 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''SDO_ELEM_INFO''');
   execute immediate(' UPDATE cmprecordfield_r SET position = 5 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''SDO_ORDINATES''');
   DBMS_OUTPUT.PUT_LINE( 'Complete updating SDO_GEOMETRY!' );
  elsif v_logical_name = 'SDO_POINT_TYPE' then
   execute immediate(' UPDATE cmprecordfield_r SET position = 1 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''X''');
   execute immediate(' UPDATE cmprecordfield_r SET position = 2 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''Y''');
   execute immediate(' UPDATE cmprecordfield_r SET position = 3 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''Z''');
   DBMS_OUTPUT.PUT_LINE( 'Complete updating SDO_POINT_TYPE!' );
  elsif v_logical_name = 'SDO_DIM_ELEMENT' then
   execute immediate(' UPDATE cmprecordfield_r SET position = 1 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''SDO_DIMNAME''');
   execute immediate(' UPDATE cmprecordfield_r SET position = 2 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''SDO_LB''');
   execute immediate(' UPDATE cmprecordfield_r SET position = 3 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''SDO_UB''');
   execute immediate(' UPDATE cmprecordfield_r SET position = 4 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''SDO_TOLERANCE''');
   DBMS_OUTPUT.PUT_LINE( 'Complete updating SDO_DIM_ELEMENT!' );
  else
   execute immediate(' UPDATE cmprecordfield_r SET position = 1 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''GEOMETRY''');
   execute immediate(' UPDATE cmprecordfield_r SET position = 2 WHERE firstclassobject =''' || v_elementid || ''' AND logicalname = ''TOLERANCE'''); 
   DBMS_OUTPUT.PUT_LINE( 'Complete updating SDOAGGRTYPE!' );
  end if; 
  
END LOOP;
CLOSE c1;
DBMS_OUTPUT.PUT_LINE( 'Complete updating position information for all MDSYS types.' );
END;
/