3

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
)
sabya
  • 109
  • 2
  • 4
  • 20
  • What is the command you're using to run SQL\*Loader, and what does your control file contain? (Please add as an edit to the question, not as a comment!). I imagine you're using relative paths. – Alex Poole May 20 '15 at 11:39
  • try to execute the loader using strace `strace -f -e trace=file sqllder ...`. And you will how it interacts with the filesystem. – ibre5041 May 20 '15 at 11:49
  • What are the values of $BADFDIR, $BADFILE and $TABLE_NAME? If they together don't for a full path, where are you running your script from? I'm hoping it'll become obvious what's happening when you see those values and how they relate to where it tries to create the file. – Alex Poole May 20 '15 at 12:55
  • All $BADFDIR ,$BADFILE and $TABLE_NAME are the path and table name which i define in a config file and i already relate those path,but no where i mention the path which is coming in the ERROR,even the path (FOLDER NAME) in the error is nowhere exist in the file directory . I am not able to understand where this path coming from in the error ? @AlexPoole – sabya May 21 '15 at 08:23
  • I understand what the variables represent; you have not said what *values* those variables have when the script is run and produces that error. Or which directory you run the script from. If $BADFDIR is just `badfiles' then it's using a relative path, and if you run the script from the `load` directory (or the script changes to that) they it would be relative to that, which would give you the `.../load/badfiles` full path. But I'm having to guess because you haven't given the actual values in the question. – Alex Poole May 21 '15 at 08:38

2 Answers2

5

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.

Daniel Smolka
  • 176
  • 1
  • 5
  • Not only write priv could be a problem in my case the problem was that bad folder doesn't exist - before (3 years) there were only good records – dabal Jul 18 '16 at 09:14
  • Looks like this same error can crop up when there are problems with access to the log file as well. This answer led me to it, but it seems like there could be a host of other issues related to file or folder access that could exhibit the same symptoms. – jdmcnair Mar 21 '19 at 01:19
0

This is likely some kind of permissions issue on writing the log file, maybe after moving services to a different server.

I ran into the same error. Problem was resolved by changing the name of the existing log file in filesystem and rerunning process. Upon rerunning, the SQLLDR process was able to recreate the log file, and subsequent executions were able to rewrite the log.

alexherm
  • 1,362
  • 2
  • 18
  • 31