Insert bulk data into oracle using SQLLoader in C#

Please visit my new Web Site https://coderstechzone.com

Sometimes we need to insert a large volume of data into the Oracle database. Sometimes data comes from comma seperated csv file or from a comma seperated text file. By reading this article you can insert data from both source to oracle. 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 to insert bulk data from text file or csv file into oracle
using SQLLoader.

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 SASN
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' 
(CDR_Type,Rec_Type)

And name this file by "Ctrl.txt".
3. Now create a text file which contins data like below:

1,2
3,4
5,6

And name this file "DataOut.txt".
4. Now create a sample table in oracle like below:



5. Now from your c# project run the below sample 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 User/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());

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