Hi
I have imported a database dump from Oracle 9i to Oracle 11gr2. Every thing is running fine but i am unable to compile two triggers. The trigger are as under:
{code}
create or replace trigger tm_daily_activity_t_bf_update before update of start_t on tm_daily_activity_t
for each row
Declare
Time_now DATE;
Terminal varchar2(40);
name varchar2(255);
v_program varchar2(255);
begin
Time_now := SYSDATE;
Terminal := USERENV('TERMINAL');
select email into name from tm_employee_t where COID_ID0 = :OLD.EMPLOYEE_OBJ_ID0;
select program into v_program from v$session;
insert into tm_daily_activity_t_log values (:OLD.START_T,:OLD.EMPLOYEE_OBJ_ID0, :OLD.ROWID, :OLD.CREATED_T, SYSDATE, TERMINAL, NAME, v_program);
end;
{/code}
{code}
create or replace trigger tm_daily_activity_t_af_update after update of start_t on tm_daily_activity_t
for each row
Declare
v_program varchar2(255);
begin
select program into v_program from v$session where sid = (select distinct sid from sys.v$mystat);
if upper(v_program) = 'TOAD.EXE' or upper(v_program) = 'SQLPLUSW.EXE' or upper(v_program) = 'SQLPLUS.EXE' or upper(v_program) = 'SQLPLUS@' then
raise_application_error (-20001,'trigger commit completed',true);
end if;
end;
{/code}
Both the triggers gave error ora-00942 for the table v$session but if i run select statement outside the triggers it works fine.
Any ideas what permission is required?