How to run SQL *Loader using C, PHP and Java

Hi Guys 🙂

Today, I am going to tell you how to run sqlldr command using C, PHP and Java. It will be a command line interface for running sqlldr command in all the three language mentioned.

First of all, what is SQL *Loader?

SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database.

The external files mentioned here are the files that contain data/information in some particularly format. Most commonly used such file is CSV files. The CSV files have to be mentioned in a CTL which sqlldr understands. So, a typical sqlldr command looks like:-

/binary/path/to/sqlldr username/password@sid control=/path/to/sample.ctl

Let’s see some example. Take a sample csv file: sample.csv

data1a, data1b, data1c
data2a, data2b, data2c

A sample CTL file in which sample.csv is mentioned: sample.ctl

LOAD DATA
INFILE     	'/path/to/file.csv'
APPEND INTO TABLE XYZ_TABLE
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS (FIELD1, FIELD2, FIELD3)

Suppose the binary of sqlldr is located at /usr/bin/. So, the command for execution will be:

/usr/bin/sqlldr username/password@sid control=/path/to/sample.ctl

That’s it. There are other parameters that we could pass but I am taking here a simple scenario.

Now we will provide an interface for this. First in C language as it is Mother of all language ;-).

sqlldr.c

#include <stdio.h>
#include <stdlib.h>

int main() {  
     char *COMMAND = "/usr/bin/sqlldr username/password@sid control=/path/to/sample.ctl";
     FILE * fp;
     fp = popen(COMMAND, "w"); 
     if (fp == NULL) {
           perror("Command execution failed");
           exit(1);
     }
     return pclose(fp);  
 }

To run this code, execute the below commands at terminal:
a) gcc sqlldr.c. This will create a complied file by named a.out.
b) ./a.out.

Done. 🙂

Now let’s see how to do same thing using PHP.

sqlldr.php

class SQLLDR {
	public function runsqlldr() {
		passthru("/usr/bin/sqlldr username/password@sid control=/path/to/sample.ctl", $returnValue);
		if(!$returnValue) {
	                echo "Command execution failed";
		}
	}
}
$obj = new SQLLDR;
$obj-> runsqlldr();

Just execute the following command at terminal:
php sqlldr.php
OR open in browser: http://localhost:80/sqlldr.php

You are done. 🙂

PHP is interesting, isn’t it? You can run same file from command line and in browser. How much fun!!

Now let’s see in most popular and extensively used programming language, Java.

SQLLDR.java

import java.io.IOException;
public class SQLLDR {

	public void runSqlldr () throws InterruptedException {
		String[] stringCommand = { "bash", "-c", "/usr/bin/sqlldr username/password@sid control=/path/to/sample.ctl"};
		System.out.println("SQLLDR Started");
		Runtime rt = Runtime.getRuntime();
		Process proc = null;
		try {
			proc = rt.exec(stringCommand);
		}catch (IOException e) {
			e.printStackTrace();
		} catch (NullPointerException e) {
			e.printStackTrace();
		}finally {
			proc.destroy();
		}
		System.out.println("SQLLDR Ended");
	}

	public static void main(String[] args) {
		try {
			new SQLLoadRunner().runControl();
		} catch(InterruptedException e) {
 			e.printStackTrace();
		}
	}
}

Follow the below the steps for execution:
a) javac SQLLDR.java
b) java SQLLDR

That’s it. You are done. 😉

There are a lot of ways of doing same thing. You can play with above codes. For example, I have hard-coded the sqlldr command in code, you can pass those command as parameter to a function/method by making a generic code.

Download C code: HERE
Download PHP code: HERE
Download Java code: HERE

That’s all folks.

Suggestion/critics are very much welcome.

Have a nice day ahead.