1 Reply Latest reply: Jun 3, 2010 10:21 AM by Ben Speckhard-Oracle RSS

    Versioning Spatial Tables ;Trigger fails

    763172
      I am having issues regarding versioning of spatial tables.When I enable versioning of this table by executing :
      execute dbms_wm.enableversioning('OWM_TEST');
      It shows this messageL:-
      ORA-24344: success with compilation error

      After that when I insert a record

      insert into owm_test(geom) values(MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(76,26,null),null,null));

      I get SQL Error: ORA-04098: trigger 'TEST.OVM_INSERT_19' is invalid and failed re-validation

      If I insert the record before enabling versioning Trigger does not cause any error.
      Please tell me what could be wrong in my script .

      I am running the following script:-

      create table owm_test ( id number PRIMARY KEY, geom mdsys.sdo_geometry );
      --create table succeeded.

      insert into user_sdo_geom_metadata
      values
      (
      'OWM_TEST',
      'GEOM',
      mdsys.sdo_dim_array
      (
      sdo_dim_element('X',-180,180,0.05),
      sdo_dim_element('Y',-90,90,0.05)
      ),8307
      );

      --1 rows inserted

      create index idx_sp_owm_test on owm_test(geom) indextype is mdsys.spatial_index;
      --create index succeeded.

      create sequence seq_owm_test start with 1;
      --create sequence succeeded.


      create or replace trigger tr_ins_owm_test
      before insert on owm_test for each row
      declare
      i number;
      begin
      select seq_owm_test.nextval into i from dual;
      :new.id:=i;
      end;
      --TRIGGER TR_INS_OWM_TEST compiled

      insert into owm_test(geom) values(MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(86,36,null),null,null));
      --1 rows inserted

      commit;
      --commited

      execute dbms_wm.enableversioning('OWM_TEST');
      --Error starting at line 33 in command:
      --execute dbms_wm.enableversioning('OWM_TEST')
      --Error report:
      --ORA-20229: statement 'DECLARE
      -- err_num integer;
      -- err_msg varchar2(1000);
      -- prfx_len integer;
      -- BE' failed during EnableVersioning. Error:
      --ORA-24344: success with compilation error
      --ORA-06512: at "WMSYS.LT", line 9150
      --ORA-06512: at line 1


      insert into owm_test(geom) values(MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(76,26,null),null,null));

      --Error starting at line 36 in command:
      --insert into owm_test(geom) values(MDSYS.SDO_GEOMETRY(2001,8307,MDSYS.SDO_POINT_TYPE(76,26,null),null,null))
      --Error at Command Line:36 Column:12
      --Error report:
      --SQL Error: ORA-04098: trigger 'TEST.OVM_INSERT_19' is invalid and failed re-validation
      --04098. 00000 - "trigger '%s.%s' is invalid and failed re-validation"
      *Cause:    A trigger was attempted to be retrieved for execution and was
      -- found to be invalid. This also means that compilation/authorization
      -- failed for the trigger.
      *Action:   Options are to resolve the compilation/authorization errors,
      -- disable the trigger, or drop the trigger.


      TIA,
      Prabhjot