Data can be modified as it loads into the Oracle Database. One can also populate columns with static or derived values. However, this only applies for the conventional load path (and not for direct path loads
SQL*Loader provides the following methods to load data:
Conventional path loads – construct INSERT statements from the contents of the input datafile based on the predefined specification and execute the inserts.
Direct path loads – creates data blocks in Oracle database block format from the datafile and directly writes the data block to the database. This way is much faster than the conventional path but subject to some restrictions.
External table loads – create an external table for the data stored in the datafile and execute INSERT statements to insert the data from the datafile into the target table. The external table loads support parallel loading if datafile is big enough.
To execute the SQL*Load tool, you need at least three files:
The input data file stores delimited or raw data
The parameter file stores the location of the input/output files
The control file contains the specification on how data is loaded.
Sample Sql lOADER Commadns :
sqlldr username/password contorl=controlfile.ctl bad=bad.log data=data.dat logfile=log1.log direct=true parallel=true multithreading=true discard=discard.log silent=feedback
errors=1000000 bindsize=1000000 readsize=1000000 rows=1000000 multithreading=true
SQL*Loader Tuning options :
1) Direct mode
2) bindsize=1000000 readsize=1000000 rows=1000000
3) Multiple controlfile using load and skip
4) use append hint
5) multithread and parallel
Sample Control file contents :
options (errors=10000)
load data
APPEND INTO TABLE AAA ( COLUMN )
INSERT INTO TABLE SSS ( DDDD )
cat example1.ctl
load data
infile '/home/ramesh/employee.txt'
into table employee
fields terminated by ","
( id, name, dept, salary )
If you like to insert more data to the tables without having to delete the existing rows, use the “append’ command as shown in the following control file.
$ vi sqlldr-append-more.ctl
load data
infile '/home/ramesh/newemployee.txt'
append
into table employee
fields terminated by ","
( id, name, dept, salary )
LOAD DATA
INFILE *
INTO TABLE tab1 WHEN tab = 'tab1'
( tab FILLER CHAR(4),
col1 INTEGER
)
INTO TABLE tab2 WHEN tab = 'tab2'
( tab FILLER POSITION(1:4),
col1 INTEGER
)
BEGINDATA
tab1|1
tab1|2
tab2|2
tab3|3
LOAD DATA
CHARACTERSET WE8EBCDIC500
INFILE data.ebc "fix 86 buffers 1024"
BADFILE data.bad'
DISCARDFILE data.dsc'
REPLACE
INTO TABLE temp_data
(
field1 POSITION (1:4) INTEGER EXTERNAL,
field2 POSITION (5:6) INTEGER EXTERNAL,
field3 POSITION (7:12) INTEGER EXTERNAL,
field4 POSITION (13:42) CHAR,
field5 POSITION (43:72) CHAR,
field6 POSITION (73:73) INTEGER EXTERNAL,
field7 POSITION (74:74) INTEGER EXTERNAL,
field8 POSITION (75:75) INTEGER EXTERNAL,
field9 POSITION (76:86) INTEGER EXTERNAL
)
Unload data from database Table To Flat File :
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off
set colsep ','
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1, col2, col3
from tab1
where col2 = 'XYZ';
spool off
declare fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %sn', 'TextField', 55);
utl_file.fclose(fp);
end;
/
Reference :
https://docs.oracle.com/en/database/oracle/oracle-database/19/sutil/oracle-sql-loader-commands.html#GUID-CD662CD8-DAA7-4A30-BC84-546E4C40DB31
