Run Oracle SQL*Loader from SQL or PL/SQL

In continuation of my previous blog entry where a generic framework for invoking Operating System commands from SQL or PL/SQL was described [read], we will now try to demonstrate how this service can be used to invoke SQL*Loader utility.

Business Case: Suppose you want to load a data file in ‘.csv‘ format containing list of employees to ‘emp‘ table in ‘hr‘ schema from SQL. A step by step instructions for the implementation of this business case is described as follows:

Step 1: Create a folder named  myprojects in c drive

Step 2: Download ‘‘ file and copy it to ‘c:/myprojects‘ folder

Step 3: Download SQL*Loader control file with name ‘emp.ctl‘ and copy it to ‘c:/myprojects‘ folder. Contents of this file are shown below            

load data
infile ”
into table employee
fields terminated by “,”
( code, name, salary )

Step 4: Download a sample data file with name ‘emp.csv‘ and copy it to ‘c;/myprojects‘ folder. Contents of this file is shown below              


Step 5: Create emp table             

create table hr.emp
       code varchar2(30),
       name varchar2(100),
       salary integer

Step 6: Open command window and first change the directory to ‘c:/myprojects‘ and then run the following command

python c:/myprojects/

Step 7: Open SQLPlus and connect to user ‘sys as sysdba‘ and then run the following query

select utl_http.request(utl_url.escape(‘http://localhost:9090/run?command=sqlldr -userid=hr/hr control=./emp.ctl data=./emp.csv log=./emp.log‘)) from dual;

Incase you want to execute the above query from another database user (for example hr) then you need to perform the following:

–  grant execute on utl_http to hr;
–  grant execute on utl_url to hr;
–  configure the Network ACL so that hr user can invoke the web-service

Step 8: Verify the results by issuing the following query            

select count(*) from hr.emp;


About Moiz

I am working as a data warehouse architect. My areas of expertise includes data modeling. Recently i have completed Data Science Specialization through Coursera and trying to take my data skills to next level.
This entry was posted in File system, Operating System, Oracle, Python, Web Service and tagged , , , , , , . Bookmark the permalink.

3 Responses to Run Oracle SQL*Loader from SQL or PL/SQL

  1. Neelesh says:

    Hi Dear, may i want to know something about ‘’ and when iam type in sql terminal by using ‘host command’ its not getting the please give me some information about this issue and how load the external files(like CSV) into oracle table by using SQL * Loader in Sql Plus.

    • Moiz says: is just a sample implementation (based on python’s cherrypy web framework) of a webservice that takes a command as a http request parameter. Here ‘http://localhost:9090/run’ is the name of webservice and ‘sqlldr -userid=hr/hr control=./emp.ctl data=./emp.csv log=./emp.log’ is the command that is passed as a request parameter. If you want to know about the implementation details of ‘’, please read my previous blog entry ‘Run Operating System Command in Oracle PL/SQL’

      Regarding the issues you are facing, please provide exact error messages you are receiving.

  2. Shailendra says:

    Hi Moiz,

    I need invoke sqlldr from a python script. I’m creating a csv file in the script and I want to load this into a table using sqlldr. I know there are different methods of loading this data into a table, but I want to use only sqlldr. I’m using the call() method, somehow that seems to be not working.

    retcode = call([COMMAND, CONNECTION_STRING, “control=”+CONTROL_FILE, “log=”+TEMP_LOG_FILE, MODE , “data=”+loadfile])

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s