Oracle Sql Loader handy commands

 

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