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
fmisaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MikeOM_DBACommented:


SQL Loader DOES NOT make copies of your data files.

Try creating "Organization External" tab;les on these files.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
fmisaAuthor Commented:
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....
fmisaAuthor Commented:
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_DBACommented:

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;

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.