SQL*Loader-522: lfiopn failed for file

bash shell oracle11g sql-loader
sabya picture sabya · May 20, 2015 · Viewed 28.1k times · Source

I am getting below error in my script which is running a SQLLDR : 

SQL*Loader-522: lfiopn failed for file (/home/abc/test_loader/load/badfiles/TBLLOAD20150520.bad)

As far my knowledge this is the error related to permission,but i am wondering in the folder "/load" there is no "badfiles" folder present .i have already define badfiles folder outside the load folder,but why in the error it is taking this location ? is it like my input file having some problem and SQLLDR trying to create a bad file in the mention location ?

below is the SQLLDR command :

$SQLLDR $LOADER_USER/$USER_PWD@$LOADER_HOSTNAME control=$CTLFDIR/CTL_FILE.ctl BAD=$BADFDIR/$BADFILE$TABLE_NAME ERRORS=
0 DIRECT=TRUE PARALLEL=TRUE LOG=$LOGDIR/$TABLE_NAME$LOGFILE &

below is the control file temp :

LOAD DATA
INFILE '/home/abc/test_loader/load/FILENAME_20150417_001.csv' "STR '\n'"
APPEND   
INTO TABLE STAGING.TAB_NAME
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
  COBDATE,
--
--
--
  FUTUSE30 TERMINATED BY WHITESPACE
)

Answer

Daniel Smolka picture Daniel Smolka · Sep 25, 2015

Yes, your input file is having a problem so the sqlldr wants to create a file containing rejected rows (BAD file). The BAD file creation fails due to insufficient privileges - the user who runs the sqlldr does not have rights to create file in the folder you defined to contain BAD files.

Add write privileges on the BAD folder to the user who runs the sqlldr or place the BAD folder elsewhere.

Related questions

How to concatenate string variables in Bash

In PHP, strings are concatenated together as follows: $foo = "Hello"; $foo .= " World"; Here, $foo becomes "Hello World". How is this accomplished in Bash?

Read More
How can I check if a directory exists in a Bash shell script?

What command can be used to check if a directory exists or not, within a Bash shell script?

Read More
How do I split a string on a delimiter in Bash?

I have this string stored in a variable: IN="[email protected];[email protected]" Now I would like to split the strings by ; delimiter so that I have: ADDR1="[email protected]" ADDR2="[email protected]" I don't necessarily …

Read More