SQL Debugging Records

ORA-01489 result of string concatenation is too long

Ref: Stack Overflow ORA-01489

Update inserted row with trigger

Ref: Stack Overflow

INSERT FAIL ON ORA-04091

1. ORA-04091 table is mutating, trgger/function may not see it

-- 1. create table
create table cdt (a number);

-- 2. create before insert trigger
CREATE OR REPLACE TRIGGER TRIG_CDT
  before insert on cdt
  for each row
declare
  cnt number;
begin
  select count(*)
    into cnt
    from cdt;
end TRIG_CDT;

-- 3. insert
insert into cdt(a)
select 1 from dual

and we got:

ORA-04091: table CDT is mutating, trigger/function may not see it
ORA-06512: at "TRIG_CDT", line 4
ORA-04088: error during execution of trigger 'TRIG_CDT'

However, this works:

insert into cdt(a) values(1);

Explanation

• An insert trigger (both before and after) cannot read the related table.

Solution:

• Don’t use triggers. While the object-oriented Oracle provides “methods” that are associated with tables, most savvy PL/SQL developers avoid triggers unless absolutely necessary.

• Use autonomous transactions. Autonomous transaction makes it independent from the table that calls the procedure.

CREATE OR REPLACE TRIGGER TRIG_CDT
  before insert on cdt
  for each row
declare
  cnt number;
  pragma autonomous_transaction;
begin
  select count(*)
    into cnt
    from tmp_cmi;
end TRIG_CDT;