KEMBAR78
6 SQL Loader | PDF | Sql | Computing
Scribd
Upload
54 views3 pages

6 SQL Loader

The document describes the process of loading data from a flat file into Oracle Applications base tables using SQL*Loader. SQL*Loader is used to load data from a file into a database table. It requires a control file (.ctl file) that specifies the data file, table, and column details. The .ctl file is registered as a concurrent program to load the data. Options like insert, append, truncate control how the data is loaded into the table. Additional options allow loading a subset of rows, skipping rows, and handling comma within data fields.

Uploaded by

Sadasiva M
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
54 views3 pages

6 SQL Loader

The document describes the process of loading data from a flat file into Oracle Applications base tables using SQL*Loader. SQL*Loader is used to load data from a file into a database table. It requires a control file (.ctl file) that specifies the data file, table, and column details. The .ctl file is registered as a concurrent program to load the data. Options like insert, append, truncate control how the data is loaded into the table. Additional options allow loading a subset of rows, skipping rows, and handling comma within data fields.

Uploaded by

Sadasiva M
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 3

IN Bound Interfaces

------------------------------------

The process of loading the data from flat file to Oracle Apps Base tables.

Process
-------------------
SQL*Loader Import Program
xls -----------> csv ----------------------> Interface table
----------------------> Base table

SQL*Loader
--------------------------------
It is tool used to load the data from file to a table.
It requires control file to load the data.
Its is developers responsibility to create .ctl file.

Syntax of control file:


----------------------------------

LOAD DATA
infile '<data file path >'
insert / append/ truncate into table <table_name > fields terminated by ','
( col1, col2, coln )

Ex:
------------

LOAD DATA
infile 'C:\data\student_data.txt'
insert into table student fields terminated by ','
( sno, sname, marks )

----------------------------------------

Register the ctl file as concurrent program.

Step 1 : Transfer the data file to the server.


/d01/oracle/VIS/apps/apps_st/appl/inv/12.0.0/out

Step 2: Transfer the ctl file to the bin directory.

ctl file
-----------------------
LOAD DATA
infile '/d01/oracle/VIS/apps/apps_st/appl/inv/12.0.0/out/student_data.txt'
insert into table student fields terminated by ','
( sno, sname, marks )

Step 3: Register the ctl file as concurrent program.


Executable Name -- MZ_STUDENT_LOAD_EXEC
Short Name -- MZ_STUDENT_LOAD_EXEC
Application -- Inventory
Method - SQL*Loader
Executable file name - mz_load ( ctl file )

Save.

++++++++++++++++++++++++++++++++++++

Program -- MZ_STUDENT_LOAD_PROG

Request Group - All Inclusive GUI


++++++++=++++++++++++++++++++++

insert -- Table should be empty.


append -- Add to existing data. -- 10,000 + 20 = 10020

Truncate -- Truncate the table and data is loaded.

++++++++++++++++++++++++++++++++++++++++

Load first 'n' rows


--------------------------

options ( load = 5 )
LOAD DATA
infile '/d01/oracle/VIS/apps/apps_st/appl/inv/12.0.0/out/student_data.txt'
truncate into table student fields terminated by ','
( sno, sname, marks )

+++++++++++++++++++++++++++++++++++++++

Skip the rows


-----------------------

options ( skip = 5 )
LOAD DATA
infile '/d01/oracle/VIS/apps/apps_st/appl/inv/12.0.0/out/student_data.txt'
truncate into table student fields terminated by ','
( sno, sname, marks )

+++++++++++++++++++++++++++++++++++++++++

Bad file
-------------
Contains rejected rows.
Bad file information is available in log file.
Bad file name is same as request ID

++++++++++++++++++++++++++++++++++++++++++++++

How to load comma as part data


-------------------------------------------------
Step 1: Enclose the data in " "

104,"arun,rao",40
105,"arun,rao",40

Step 2: Use optionally enclosed by clause in ctl file.

Ex:
-------
LOAD DATA
infile '/d01/oracle/VIS/apps/apps_st/appl/inv/12.0.0/out/student_data.txt'
truncate into table student fields terminated by ',' optionally enclosed by
'"'
( sno, sname, marks )

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

You might also like