Search code examples
oracle-databaseplsqloracle10gsql-loaderctl

SQLLDR and .ctl files

I have a ddl script to create some tables but the data is in .ctl files and I never use it before. I did some researches but I didn't quite understand how to use SQLLDR. How it works? Can I use some other way to execute the .ctl file? I'm just using PL/SQL and Oracle 10G

Solution

  • The way you put it, it would go like this:

    • using DDL script, create all those tables

    • if CTL files contain data, I presume it is within the BEGINDATA section. Fine, couldn't be better because - as soon as you run the loader, it'll know where to find data to be loaded (it also means that control file uses infile *, right?)

    • you have to have access to SQL*Loader

      • if you can connect to the database server, it is there
      • if you're using your own PC, see whether it is installed
        • along with the Client software
        • or, you might even have a database on your PC (XE?)

    • once you have it (the sqlldr.exe), make sure its directory is contained with the PATH environment variable, or - if not - invoke it by specifying the whole path to it

    • open command prompt of your operating system

    • navigate to directory that contains CTL files

    • run the loader as

      sqlldr scott/tiger control=file1.ctl log=file1.log

    If everything is OK, data will be loaded. Check log files!