-->

Spiga

Import Data Into Oracle Table

We Can Import any text data file into a oracle table.
To Import Text formated data into oracle table we have to create a table and a control file according to the text file.
textfile:-
10001 20056 12122009
10002 20036 12112009
10002 20236 12122009
10002 20776 12112009
10002 20346 12112008

Rename the Text file "textfilename.dat".

1st 5 char are employee number,then 1 space
2nd 5 char are salary,then 1 space
3rd 8 char are date of joining.

Creating oracle table:-
Create table emp(empno varchar2(5), salary number, doj date);

Create Control File:-
LOAD DATA APPEND INTO TABLE EMP(
EMPNO POSITION(1 : 6) CHAR,
SALARY POSITION(1 : 6) CHAR,
DOJ POSITION(1 : 6) CHAR
)

Save the file with extention ".ctl"

How To Run The Control File :-

From Linux/Unix-
Open Terminal.
$>$ORACLE_HOME/bin/sqlldr userid= userid/passwd@sid data=textfilename.dat
control=controlfilename.ctl log=/home/oracle/Desktop/error.log bad=/home/oracle/Desktop/error.bad

From Windows-
Open Command Prompt.
C:\>$ORACLE_HOME/bin/sqlldr userid= userid/passwd@sid data=textfilename.dat
control=controlfilename.ctl log=/home/oracle/Desktop/error.log bad=/home/oracle/Desktop/error.bad

You can change the log and bad file name and path.No need to commit.