SQL * LOADER

SQL* Loader

           SQL* Loader is a tool in Oracle. It is used to transfer data from flat file to Oracle database tables.

There are 5 types of files in SQL * Loader given below.

1)      Flat File: -     It is also called as data File. It 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 and .sql.

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

Syntax of Control file:

OPTIONS(SKIP=1)

LOAD DATA

infile '*'

REPLACE/INSERT/APPEND into table table_name

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

Column1,

Column2,

Column3,

Column n

 "REPLACE(:Column n,CHR(13),'')"

)

3)      Bad File: -     whenever we execute the control file, if SQL * Loader rejects the records then the bad file will be created automatically. Extension 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.

4)      Discard File: - Discard file 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 on 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’.

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

SQL * Loader Methods: -     SQL *Loader methods are 3 types. They were shown below

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

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

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

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

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

After setting the environment variable (.env file)

2.     sqlldr 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 XXST_VENDOR_SITE_UPDATE (VENDOR_ID NUMBER,

                                                                               VENDOR_NAME VARCHAR2 (4000),

                                                                               SEGMENT1 VARCHAR2 (4000),

                                                                                VENDOR_SITE_ID NUMBER,

                                                                                VENDOR_SITE_CODE VARCHAR2 (4000),

                                                                                TERMS_DATE_BASIS VARCHAR2 (4000),

                                                                                TERMS_NAME VARCHAR2 (4000)

                                                                               );

Data file:-

VENDOR_ID	VENDOR_NAME	SEGMENT1	VENDOR_SITE_ID	VENDOR_SITE_CODE	TERMS_DATE_BASIS	Terms Name
2	Service Tax Authority(India)	100002	1	INDIA	Invoice Received	30 NET
3	VAT Authorities(India)	100003	4	INDIA	Goods Received	60 NET
4	TDS Authorities (India)	100004	41006	192B_INDV	Current	IMMEDIATE
6	Synechron Limited (UK)	100006	67	ST_UK	Goods Received	5 NET
2011	HDFC Bank Credit Card No. 4050 2810 0036 4759 ( Parag Deshpande)	101019	1985	IND_SITE	Invoice Received	10 NET
2012	Hammer Publishers Private Limited	101020	1986	IND_SITE	Current	20 NET
2013	Hindustan Advertising Agencies	101021	1987	IND_MH_SITE	Current	15 NET
2014	Hinjewadi Industries Association	101022	1988	IND_SITE	Goods Received	45 NET

Control file:-

OPTIONS(SKIP=1)

LOAD DATA

infile '*'

REPLACE into table XXST_VENDOR_SITE_UPDATE

fields terminated by ','

optionally enclosed by '"'

trailing nullcols

(

VENDOR_ID   ,

VENDOR_NAME ,

SEGMENT1    ,

VENDOR_SITE_ID    ,

VENDOR_SITE_CODE  ,

TERMS_DATE_BASIS,

TERMS_NAME

 "REPLACE(:TERMS_NAME,CHR(13),'')"

)

open the command prompt

sqlldr apps/apps@databasename control='$AP_TOP/bin/vendor_site_update.ctl' data='$AP_TOP/bin/vendor_site_update.csv' log='$AP_TOP/bin/vendor_site_update.log'

Example1:-

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

Control file:-

           Load data

           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 data

           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)

Without 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 registration in Oracle Apps:-

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

2.     Then 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 name as control file name without extinction.

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

5.     Create Request Group and attach Concurrent Program.

6.     Create Responsibility and attach Request Group to responsibility.

7.     Create User and attach Responsibility to User.

8.     User will submit the Request from SRS Window.

SQL * Script Registration into Oracle Apps:-

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

2.     Then 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 and attach Request Group to Responsibility.

7.     Create User and 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