SQL*Loader Bad And Discard Files Do Not Fllow Umask Setting (Doc ID 1371772.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Database - Enterprise Edition - Version 10.2.0.1 to 11.2.0.3 [Release 10.2 to 11.2]
Information in this document applies to any platform.
***Checked for relevance on 06-Aug-2014***

Symptoms

The Bad and Discard files generated by the SQL*Loader (sqlldr) are not following any OS standard for setting file permission. It always generate a file with -rw-r--r-- no matter what the umask or directory permission is set to.

This can be replicated very easily:

#> sqlplus test/test

create table test1 (name varchar2(10), emp number);

Table created.

SQL*Loader Control file:

#> cat sample.ctl

OPTIONS (ERRORS=0)
LOAD DATA
INFILE 'sample.csv'
BADFILE 'sample.bad'
DISCARDFILE 'sample.dsc'
DISCARDMAX 1
TRUNCATE
CONTINUEIF NEXT PRESERVE (1:2) = '\r\n'
INTO TABLE test1
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
   name,
   emp
)

Input data file:

#> cat sample.csv
'this is a test record',20
'test',15

#> umask 0
#> touch new
#> ls -l new
-rw-rw-rw- 1 oracle oracle 0 Jun 1 14:45 new

Here we can see the permissions 666

'> sqlldr test/test control=sample.ctl

#> ls -l sample* | grep -v sample.ctl | grep -v sample.csv

-rw-r--r-- 1 oracle oracle 27 Jun 1 14:45 sample.bad
-rw-rw-rw- 1 oracle oracle 1784 Jun 1 14:45 sample.log

Difference in the permissions of two files created.

Lets change the umask again and try:

#> umask 020
#> rm new
#> touch new
#> ls -l new

-rw-r--rw- 1 oracle oracle 0 Jun 1 14:51 new

#> rm sample.log sample.bad
#> sqlldr test/test control=sample.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Wed Jun 1 14:52:21 2011

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Commit point reached - logical record count 1

#> ls -l sample* | grep -v sample.ctl | grep -v sample.csv

-rw-r--r-- 1 oracle oracle 27 Jun 1 14:52 sample.bad
-rw-r--rw- 1 oracle oracle 1784 Jun 1 14:52 sample.log

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