This discussion is archived
1 Reply Latest reply: Jun 3, 2010 8:21 AM by Ben Speckhard RSS

Versioning Spatial Tables ;Trigger fails

763172 Newbie
Currently Being Moderated
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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points