Insert bulk data into Oracle Using SQLLoader in C# VB.Net

Sometimes we need to insert a large volume of data into the Oracle Database. Data might be comes from comma separated CSV file or from a comma separated Text file. By reading this article you can insert Bulk data from both CSV & Text File to Oracle Database by Using SQLLoader. Before implementing this example must check your development PC’s Oracle set up path. If the path (usually C:\oracle\product\11.2.0\client_32\BIN) contains sqlldr.exe file then you are ready to implement the example. Otherwise you have to reinstall either Client or Server again.

There are mainly two ways to insert bulk data into Oracle:
1. Using Bindarray
2. Using SqlLoader

Here in this article i will explain how one can use SQLLoader to insert bulk data from text file or csv file into oracle.

To do that please follow the below steps:
1. Create a folder in your c: drive and named it WorkingFolder.
2. Create a controller file in the WorkingFolder using below code:

LOAD DATA
INFILE DataOut.txt
BADFILE dataFile.bad
APPEND INTO TABLE ASP_Net_C_SHARP_Articles
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
(ID,Name,Category)

And name this file To “Ctrl.txt”.
3. Now create a text file which contins data like below:

1,Insert Data Into Oracle Using SQLLDR,CSharp;C#;VB.Net;ADO.Net;Asp.Net
2,BULK INSERT Data into ORACLE Using C#,CSharp;C#;VB.Net;ADO.Net;Asp.Net
3,Using SQLLDR To insert Bulk Data into Oracle,CSharp;C#;VB.Net;ADO.Net;Asp.Net
4,Insert Group Data into Oracle Using C#,CSharp;C#;VB.Net;ADO.Net;Asp.Net
5,BULKCOPY to Insert Data into Oracle Using C# VB.Net,CSharp;C#;VB.Net;ADO.Net;Asp.Net

And name this file To “DataOut.txt”. The name already defined in Ctrl.tx File.
4. Now create a sample table in oracle like below:

CREATE TABLE ASP_Net_C_SHARP_Articles
     (
     ID INT NOT NULL,
     Name VARCHAR2(500),
     Category VARCHAR2(100)
	   );

5. Now Write the below code under Page_Load Event:
C# Code:

            Process proc = new Process();
            string myCommand = @"CMD.EXE";
            proc.StartInfo = new ProcessStartInfo(myCommand);

            //Set up arguments for CMD.EXE
            proc.StartInfo.Arguments = @"/c SQLLDR USERNAME/PASSWORD@SCHEMA CONTROL=C:\WorkingFolder\Ctrl.txt";
            proc.StartInfo.RedirectStandardOutput = true;
            proc.StartInfo.RedirectStandardError = true;
            proc.StartInfo.UseShellExecute = false;
            proc.StartInfo.WorkingDirectory = @"c:\WorkingFolder\";
            proc.Start();
            proc.WaitForExit();

            if (proc.ExitCode == 0) // Successfully completed
               MessageBox.Show("Successfully Inserted");

            else
               MessageBox.Show(proc.StandardError.ReadToEnd());

Note: Don’t forget to add “using System.Diagnostics;” namespace.

VB.Net Code:

        Dim proc As Process = New Process()
        Dim myCommand As String = "CMD.EXE"
        proc.StartInfo = New ProcessStartInfo(myCommand)

        'Set up arguments for CMD.EXE
        proc.StartInfo.Arguments = "/c SQLLDR USERNAME/PASSWORD@SCHEMA CONTROL=C:\WorkingFolder\Ctrl.txt"
        proc.StartInfo.RedirectStandardOutput = True
        proc.StartInfo.RedirectStandardError = True
        proc.StartInfo.UseShellExecute = False
        proc.StartInfo.WorkingDirectory = "c:\WorkingFolder\"
        proc.Start()
        proc.WaitForExit()

        If proc.ExitCode = 0 Then 'Successfully completed
            MessageBox.Show("Successfully Inserted")
        Else
            MessageBox.Show(proc.StandardError.ReadToEnd())
        End If

Note: Don’t forget to add “Imports System.Diagnostics” namespace.

6. Now run the above code & hope you will get below output:
SQLLoader_Oracle

Download Code Example C#        Download Code Example VB.Net

Leave a Reply

Your email address will not be published. Required fields are marked *

AlphaOmega Captcha Classica  –  Enter Security Code