Skip to Main Content
  • Questions
  • Is it known error with timestamp field update in a compound trigger internal procedure?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Vladyslav.

Asked: January 19, 2017 - 3:38 pm UTC

Last updated: January 19, 2017 - 4:51 pm UTC

Version: 12.1.0.2.0

Viewed 1000+ times

You Asked

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.

with LiveSQL Test Case:

and Chris said...

I'm not happy to see the error, but yes, I do see it!

I can't find any anything in MOS about this; I'll raise a bug.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Great!

Vladyslav, January 19, 2017 - 5:35 pm UTC

Thank you!

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library