asked on

SQLLDR not removing temporary files - making copies of data input files

Hi ...
SQLLDR seems to be making copies of input data files and not deleting them after processing is finished ?

On windows under:
C:\Documents and Settings\userAccount\Local Settings\Temp\input#####.csv
On unix under:
/var/

We're calling SQLLDR from ANT/Java tasks that we've written using System.getRuntime().exec(“SQLLDR”) API.
The control files/data file paths are passed in by users at runtime as command line arguments to the ANT/Java task.
** See below: Java Code/Ant Task -- and resulting SQLLDR command are attached

These "temp" files I'm describing -- are exact copies of the data files
that SQLLDR is instructed to load but are named input#####.cvs (where ##### is
incremented by SQLLDR automatically).....I'm assuming they are some kind of
working file that does not get deleted.

Hope someone here has some thoughts about how:
1) How can SQLLDR be instructed to a) not make copies of input data or at the very least b) remove temporary working files when finished ?
2) What is recommended best practice to manage temporary SQLLDR files; when SQLLDR is being repeatedly launched by 3rd party applications ?
CRON job to delete -- or should the 3rd party application (that launched SQLLDR) also be expected to remove/clean-up after SQLLDR ?
Hope to hear from someone soon.....

----------------------------------------------------------------------------------------------
Resulting SQLLDR command
----------------------------------------------------------------------------------------------
sqlldr bad=bad.txt
log=log.txt
data=myInputFile.csv
control=control.ctl
discard=discard.txt
userid=user/pass@dbSID
direct=TRUE
parallel=TRUE
silent=(header,feedback)
errors=9223372036854775807

**NOTE: copies of "myInputFile.csv" are made into system TEMP and named input#####.csv
I believe SQLLDR is actually working with input#####.csv
----------------------------------------------------------------------------------------------
Java Code that produces above SQLLDR command
----------------------------------------------------------------------------------------------
String progName = "sqlldr";
String directCmd = direct ? TRUE : FALSE;
String parallelCmd = parallel ? TRUE : FALSE;
// the command line.
this.setArgs(new String[] { progName, "BAD=" + badFile.getPath(),
"LOG=" + logFile.getPath(), "DATA=" + inputFile.getPath(),
"CONTROL=" + controlFile.getPath(),
"DISCARD=" + discardFile.getPath(),
"USERID=" + userName + "/" + password + "@" + dbServiceId, "DIRECT=" + directCmd,
"PARALLEL=" + parallelCmd, "SILENT=(HEADER,FEEDBACK)",
"ERRORS=" + Long.MAX_VALUE });

Runtime runtime = Runtime.getRuntime();
Process process;
try {
System.out.println("Executing: " + args);
process = runtime.exec(args);
} catch (IOException e) {
throw new SqlLoaderException(e);
}
----------------------------------------------------------------------------------------------

Hope someone hear can help me....
Thanks
Frank

ASKER CERTIFIED SOLUTION

MikeOM_DBA

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

fmisa

ASKER

My first thought was our Java/Ant tasks were somehow making these TEMP files.....
But I don't see it :(

I'll go back and trace through the code again -- it helps to have a DBA confirm that SQLLDR is NOT responsible for these files.....

I'll put my focus elsewhere for now....

Can you please provide more than a short sentance on:
>>ORGANIZATION external
How's this supposed to help me ?

Thanks for replying....

fmisa

ASKER

Silly me -- you're right .....
I was too quick to jump to conclusion that SQLLDR was creating these files....
It was our Java/ANT code after all -- a little hidden -- but I found the point this is happening....

Would still like feedback on "Organization External" -- what did you mean by this.

Thanks

MikeOM_DBA

Look here at ORGANIZATION EXTERNAL: http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_73a.htm#2129378

Here is the example from the manual:

CREATE TABLE dept_external (
deptno NUMBER(6),
dname VARCHAR2(20),
loc VARCHAR2(25)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY admin
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
BADFILE 'ulcase1.bad'
DISCARDFILE 'ulcase1.dis'
LOGFILE 'ulcase1.log'
SKIP 20
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
(
deptno INTEGER EXTERNAL(6),
dname CHAR(20),
loc CHAR(25)
)
)
LOCATION ('ulcase1.ctl')
)
REJECT LIMIT UNLIMITED;