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 ‘osservice.py‘ 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 ”
append
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              

code,name,salary
E1,Akbar,20000
E2,John,30000
E3,Sam,15000

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/osservice.py

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 these ads

About Moiz

I am working as a data warehouse architect. My areas of expertise includes data modeling. I use Python for data integration and system administration related tasks.
This entry was posted in File system, Operating System, Oracle, Python, Web Service and tagged , , , , , , . Bookmark the permalink.

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

  1. Neelesh says:

    Hi Dear, may i want to know something about ‘osservice.py’ and when iam type in sql terminal by using ‘host command’ its not getting the results.so 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:

      osservice.py 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 ‘ossservice.py’, 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.

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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