I have faced with the error:
"ORA-03113: end-of-file on communication channel
Process ID: 3942
Session ID: 13 Serial number: 58997"
and after that the session is disconnected:
"ORA-03114: not connected to ORACLE".
In LiveSQL it caused "Internal Error..".
How to repeat:
create table TEST_TRG
(
a1 NUMBER not null,
a2 VARCHAR2(100),
a3 TIMESTAMP(6),
a4 DATE
);
/
CREATE OR REPLACE TRIGGER TEST_COMPOUND
FOR INSERT OR UPDATE ON TEST_TRG
COMPOUND TRIGGER
v_num number;
v_time timestamp;
v_date date;
procedure set_defaults is
begin
if INSERTING then
if :new.a1 is null then
:new.a1 := v_num; // NUMBER: OK
end if;
if :new.a3 is null then
:new.a3 := v_time; // TIMESTAMP: ERROR!!!
end if;
if :new.a4 is null then
:new.a4 := v_date; // DATE: OK
end if;
end if;
end;
--Executed before DML statement BEFORE STATEMENT IS
BEFORE STATEMENT IS
BEGIN
v_time := systimestamp;
v_date := sysdate;
END BEFORE STATEMENT;
--Executed before each row change- :NEW, :OLD are available
BEFORE EACH ROW IS
BEGIN
if inserting then
select nvl(max(a1), 0) + 1 into v_num from TEST_TRG;
end if;
if updating then
v_num := :old.a1;
end if;
set_defaults;
END BEFORE EACH ROW;
END;
After objects are created, let's insert into the table:
insert into test_trg values(1,'Y',null,null);
and then you are happy to see the error.
It works well for other types but error happened for timestamp.
The workaround for the case is to create and call an external procedure.
Unfortunately I forget my password in MetaLink so I can't search for the error.