Tuesday, August 27, 2024

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