Unable to Load BLOB/CLOB into Another User Schema (SQL*Loader-929) (Doc ID 739876.1)

Last updated on NOVEMBER 28, 2016

Applies to:

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

Symptoms

You try to load a table with a LOB located in another schema like in the following example:

1. Create the environment

connect / as sysdba
drop user usr001 cascade;
drop user usr002 cascade;
purge dba_recyclebin;

create user usr001 identified by passwd default tablespace users temporary tablespace temp;
create user usr002 identified by passwd default tablespace users temporary tablespace temp;
grant connect, resource to usr001, usr002;

connect usr001/passwd
create table blob001
(
   col001 number,
   col002 varchar2(10),
   col003 blob
);

grant insert on blob001 to usr002;

2. Write the SQL*Loader control file ldr.ctl

LOAD DATA
INFILE 'ldr.dat'
INTO TABLE USR001.BLOB001
FIELDS TERMINATED BY ';'
TRAILING NULLCOLS
(
   COL001,
   COL002,
   COL_FILL FILLER,
   COL003 LOBFILE (COL_FILL) TERMINATED BY EOF
)

3. Write the input file ldr.dat

1;JPG;FOTO001.JPG

Note: The JPEG file FOTO001.JPG you want to load into BLOB exists in same directory.

4. Start SQL*Loader

#> sqlldr usr002/passwd control=ldr.ctl

This returns:

SQL*Loader-929: Error parsing insert statement for table USR001.BLOB001.
ORA-01031: insufficient privileges

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