ORA-22816 When Loading Data Into a View With CLOB Column and an INSTEAD OF Trigger (Doc ID 795956.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7.1 to 11.1.0.7
This problem can occur on any platform.

Symptoms

You try to load data using SQL*Loader into a view with a LOB that has an instead of trigger. This ends with error message:

Record 1: Rejected - Error on table TEST_CLOB_LOAD_VW, column ID1.
ORA-22816: unsupported feature with RETURNING clause


The next test demonstrates this:

create sequence test_clob_seq start with 1;

create table test_clob
(
   id1 number not null,
   c1  clob   not null
);

create view test_clob_load_vw as
select id1, c1
from   test_clob;

create or replace trigger test_clob_load_trig
instead of insert on test_clob_load_vw
for each row
begin
   insert into test_clob (id1, c1) values (:new.id1, :new.c1);
end;
/

The control file prepared for SQL*Loader is:

load data
infile *
append
into table test_clob_load_vw
fields terminated by ',' optionally enclosed by '"'
(
   id1 expression "test_clob_seq.nextval",
   c1  char(10000)
)
begindata
"this is a test to show that the instead of trigger breaks with clobs and sqlldr"

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms