TeamTOM,

I'm trying to use Sql Loader to load a text file into a normal Oracle table (as opposed to an external one). Unfortunately I only have insert rights to the table via a non default role. If I were just doing a simple insert statement in sql*plus/sql developer/toad/whatever, I could do a "set role all" command prior to running the insert command but I can't find in the documentation how I can do that for sql loader.

I've included the below code to hopefully present a valid test case. (Sorry, Livesql does not appear to allow creation of users and roles).

The key is "ALTER USER some_other_user DEFAULT ROLE CONNECT;" does not include "some_role".

CREATE TABLE some_user.SOME_TABLE(
   Col1 number(10) not null,
   col2 varchar2(100) not null,
   col3 varchar2(100) not null
);

CREATE ROLE SOME_ROLE NOT IDENTIFIED;

GRANT SELECT,  INSERT,  UPDATE, DELETE  ON some_user.SOME_TABLE TO SOME_ROLE;

CREATE USER some_other_user IDENTIFIED BY some_password DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP;

ALTER USER some_other_user QUOTA UNLIMITED ON USERS;

GRANT CONNECT TO some_other_user;
GRANT SOME_ROLE TO some_other_user;

ALTER USER some_other_user DEFAULT ROLE CONNECT ;
--notice in the above the SOME_ROLE role is not listed

GRANT CREATE SESSION TO some_other_user;

Assume my sql loader control file is really simple like below.

LOAD DATA
  INFILE 'some_file.dat'
  BADFILE 'some_file.bad'
  DISCARDFILE 'some_file.dsc'
  INSERT
  INTO TABLE some_user.SOME_TABLE
  FIELDS TERMINATED BY '|'
  TRAILING NULLCOLS
 (
      col1 SEQUENCE(1,1),
      col2,
      col3
)

Assume some_file.dat is the following:

some text|some other text