Forum Stats

  • 3,734,451 Users
  • 2,246,973 Discussions
  • 7,857,297 Comments

Discussions

How to import data files using SQL Loader on a FTP site daily

Hi,

I need to import daily a file with data into a table using SQL Loader. This file would be put everyday automatically on the FTP site and I need to import the data daily into a table.

If there are duplicates records, there should be skipped in order for the importation to run without errors.

The Database is version 12.1, the DB server is running under Windows 2012 R2 Standard.

Is there any documentation or how-to on how to achieve this ? I am a novice with SQL Loader.

Best regards,

Christian

Best Answer

  • EdStevens
    EdStevens Member Posts: 28,143 Gold Crown
    Accepted Answer

    I was able to create a ctl file and to use sql loader with a local file. How can I specify an ftp site for the file containing the data ?

    The file has to be on the machine running the sqlldr. Not necessarily physically on that machine, but at least on a disk system that is mounted to that machine. You will need to move the file from the ftp site to the machine running sqlldr.

Answers

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,018 Silver Trophy

    I'm going to assume the process of getting the file from the remote FTP site to the DB server is happening outside of the database process itself. I suppose it could be initiated via the Scheduler and an external job, but that's assuming basic FTP. SFTP can be a bit more tricky due but is possible.

    Once the file is on the server, have you considered using an EXTERNAL TABLE to access this data instead of using SQL Loader. It is a similar concept but the DB sees that file on disk as a normal table and can perform queries against it. This would allow your query to handle duplicates in the manner needed.

    Not an answer but a starting point at least for thought/discussion.

  • EdStevens
    EdStevens Member Posts: 28,143 Gold Crown

    Jason - with the requirement that a new file is loaded on a regular basis, it could very well be the case that the new file doesn't have all of the data as the previous - the possibility of duplicates not withstanding. If that is the case, the external table would not be appropriate.


    Christian - there are two issues here: scheduling the sqlloader job, and actually configuring/running sqlldr? Which part are you having difficulty with? The use of sqlldr is fully documented in the Utilities manual. Give it your best shot, and when you have specific questions about a specific aspect, come back, show your work, and ask.

    As for overall approach, it shouldn't be rocket science to write a .cmd script (you said it is hosted on Windblows) that sets the environment and calls sqlldr, then use the Windblows scheduler to schedule the script to run on a recurring basis. After all, sqlldr is simply a command-line utility.

  • Christian Pitet
    Christian Pitet Member Posts: 1,035 Silver Badge

    I was able to create a ctl file and to use sql loader with a local file. How can I specify an ftp site for the file containing the data ?

  • EdStevens
    EdStevens Member Posts: 28,143 Gold Crown
    Accepted Answer

    I was able to create a ctl file and to use sql loader with a local file. How can I specify an ftp site for the file containing the data ?

    The file has to be on the machine running the sqlldr. Not necessarily physically on that machine, but at least on a disk system that is mounted to that machine. You will need to move the file from the ftp site to the machine running sqlldr.

  • Jason_(A_Non)
    Jason_(A_Non) Member Posts: 2,018 Silver Trophy

    If you can batch script out the entire process of retrieving the file from the remote site and running sql loader, then you could trigger the whole process via dbms_scheduler.create_job with job_type => 'EXTERNAL_SCRIPT'. This is available in 12c and later. You'll also need to use dbms_credential to create the OS credentials (user/pass) that Oracle uses when running the external script.

    Here's a link I found that also verifies you could also do all this from within PL/SQL if desired (or in Java that is loaded into the DB)


Sign In or Register to comment.