Protected by Copyscape Web Copyright Protection Software

Pages

Search This Blog

Saturday, August 4, 2012

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

No comments:

Post a Comment