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
Sorry, I'm not aware of an option to enable non-default roles in the SQL*Loader command-line/parfile.
So your options are:
- Create an on logon trigger which enables the role you need
- Make the role you need a default role
- Create another user which does have this (and only this) role