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: to
This problem can occur on any platform.


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
   insert into test_clob (id1, c1) values (:new.id1, :new.c1);

The control file prepared for SQL*Loader is:

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


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