SQL * LOADER

SQL* Loader:-

           SQL* Loader is one of the Oracle tool which will be used to transfer data from flat file to Oracle database tables.

SQL* Loader Files:-  SQL * Loader files are 5 types. They were given below.

Flat File :-     Flat file contains the data in specific format. This format may be fixed length or ‘,’ separated or variable format. Extensions of the flat files are ‘.txt’,’.dat’,’.csv’ (comma separated view) or Excel sheet.

Control File :-          Control file is the SQL * Loader file or program it will be used to transfer data from file to table. It contains flat file path and table name and column mappings. Extension of control file is ‘.ctl’.

Syntax for Control file:-

           LOAD DATA

           INFILE ‘DATA FILE PATH’

           INSERT INTO TABLE <TABLE NAME>

FIELDS TERMINATED BY ‘,’

(COLUMN 1, COLUMN2)

Bad File :-     whenever we execute the control file bad file will be created automatically if SQL * Loader rejects the records. Extinction of bad file is ‘.bad’. Bad file will have the rejected records which are rejected by SQL * Loader. SQL * Loader will rejects the records if data is not incorrect format and if any internal error occurs.

Discard File :-          It contains the records, which are rejected by control file. Control file will rejects the records if we write any conditions system will check each record based whether it is satisfies the condition or not . If record is not satisfying the condition it will come into the discard file. Extension for discard file is ‘.dis’.

Log File :-     It will be created automatically after completion of control file execution process. It contains the information like successful records count and bad file discard file count and so on. Extension for log file is ‘.log’.

SQL * Loader Methods:-     There are 3 types of methods in SQL *Loader. They were given below

INSERT:-  In INSERT mode system will insert the data into the table but table should be empty.

APPEND:-  In APPEND mode system will add the new data for the existing data.

REPLACE:-  In REPLACE mode system will delete or remove the existing data and inserts the new data into the table.

SQL * Loader Command:-  After completing the control file we will execute the control file by using following command.

1.     Login to command prompt where the SQL *Loader was installed.

2.     sql ldr userid/password@database name press ENTER then

control = <control filename>

ex:-  c:\orant\bin>   sql ldr apps/apps@prod

      control  =  emp.ctl

1. Before going to work with SQL * Loader we should have to verify two  things
1. Data file
2. Table
2. Develop the control file as per the flat file and table format.
3. Go to the command prompt execute the control file by using following syntax.

Example:-

           Create table custom_dept (deptno number(3), dname varchar2(100), project varchar2(10), start date, end date)

Data file:-

           10-jan-2002,10-mar-2002,10,computers,sap

           10-aug-2000, 15-sep-2000,10,computers,sap

           10-mar-2002,15-mar-2002,20,finance,oracle

Control file:-

           LOAD DATE

           INFILE ‘C:/DEPT.TXT’

INSERT INTO TABLE CUSTOM_DEPT

FIELDS TERMINATED BY ‘,’

(STARTDATE,ENDDATE,DEPTNO,DNAME,PROJECT)

open the command prompt

   c:\orant\bin> sql ldr scott/tiger prod

           control= c:\emp.ctl

Example1:-

           Create table csv_emp (empno number(3), ename varchar2(100), joindate date, deptno number(3));

Control file:-

           LOAD DATE

           INFILE ‘C:/SLOADER\EMP_DETAILS.CSV’

           DISCARD FILE ‘C:\SLOADER\EMP_DETAILS.DIS’

INSERT INTO TABLE CSV_EMP

WHERE DEPTNO=’10’

FIELDS TERMINATED BY ‘,’

(STARTDATE,ENDDATE,DEPTNO,DNAME,PROJECT)

Trialing NULL Columns:-   We can use this syntax into the null columns to insert null values if data file is not having data.

Syntax:-

           LOAD DATE

           INFILE ‘C:/SLOADER\EMP_DETAILS.CSV’

           DISCARD FILE ‘C:\SLOADER\EMP_DETAILS.DIS’

INSERT INTO TABLE CSV_EMP

WHERE DEPTNO=’10’

FIELDS TERMINATED BY ‘,’

TRAILING NULL COLS

(EMPNO “SEMPNO.NEXTVAL”,

 ENAME “INIT CAP(:ENAME)”,

DEPTNO,

JOINDATE   SYSDATE)

Whenever the client gave data file in fixed format like (data is not separated by commas(‘,’)) tempo is first 5 digits, ename is 10 digits and deptno is 2 digits. Then the control file is looks like this

LOAD DATA

INFILE ‘C:\SLOADER\FIXED_EMP.DAT’

INSERT INTO TABLE FIXED_EMP

(EMPNO POSITION  (1:5);

 ENAME POSITION (6:15);

 DEPTNO POSITION(16:17))

Inserting Data Into Multiple Tables:-

           LOAD DATA

INFILE ‘C:\SLOADER\FIXED.DAT’

INSERT INTO TABLE FIXED_EMP

WHEN EMPNO = ‘’

(EMP NO  POSITION (1:5),

 ENAME POSITION(6:15),

 DEPT NO (16:17))

INTO TABLE  FIXED_DEPT

WHERE DNAME = ‘’

(DNAME POSITION (18:25).,

 PROJECT POSITION (26:28),

STARTDATE POSITION(29 : 39)

Inserting Data into Single Table from Multiple Files:-

           LOAD DATA

INFILE ‘C:\FIRST.DAT’

INFILE ‘C:\SECOND.DAT’

INSERT INTO TABLE FIXED_EMP

FIELDES TERMINATED BY ‘,’

(EMPNO, ENAME, DEPTNO)

With out data file creating the control file:-

           LOAD DATA

INFILE *

INSERT INTO TABLE FIXED_EMP

FIELDES TERMINATED BY ‘,’

(EMPNO, ENAME, DEPTNO)

           BEGIN DATA

                       1014,operations,10

           1234,Scott,10

           456,urman,20

           789,sysadmin,10

          

FILLER:-      If you want to ignore complete data in a column we can used the ‘FILLER’. It is reserved word for SQL * Loader. System will not consider the particular column.

SQL * Loader program registered in Oracle Apps:-

1.     We will develop the SQL* Loader file as per data file and table format.

2.     Move this control file into the server.

3.     Connect to System Administrator Responsibility then create executable and execution method as SQL * Loader and execution file as control file name without extinction.

4.     Create Concurrent Program and attach executable parameters and incompatibilities.

5.     Create Request Group attach Concurrent Program.

6.     Create Responsibility attach Request Group to Responsility.

7.     Create User attach Responsibility to User.

8.     User will submit the Request from SRS Window.

SQL * Script Registration into Oracle Apps:-

1.     We will develop the SQL* Script as per client requirement which has got SQL* Plus commands.

2.     We will move this ‘.sql’ file into the server.

3.     Create Executable with execution method as SQL* Plus and we will give the SQL filename.

4.     Create Concurrent Program and attach executable parameters and incompatibilities.

5.     Create Request Group attaches Concurrent Program.

6.     Create Responsibility attach Request Group to Responsibly.

7.     Create User attach Responsibility to User.

8.     User will submit the Request from SRS Window.

Develop the SQL* script save it as .sql file move this file into sql folder and create executable