Quick Search:
 
 Oracle PL/SQL: SQL*Loader Jump to:  
Category: >> Oracle PL/SQL >> SQL*Loader  

<< lastnext >>

Snippet Name: SQL*Loader

Description: SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database.

Comment: (none)

Language:
Highlight Mode: PLSQL
Last Modified: March 07th, 2009

SQL Loader Data Types CHAR
DECIMAL EXTERNAL
INTEGER EXTERNAL 
Modes APPEND
INSERT
REPLACE
TRUNCATE 
INFILE INFILE * OR INFILE '<file_name>'
[RECSIZE <integer> BUFFERS <integer>] 
INFILE 'mydata.dat' "RECSIZE 80 BUFFERS 8" 
INTO INTO <table_name> 
INTO TABLE emp 
BADFILE
 
Records WITH formatting errors OR that cause Oracle errors BADFILE '<file_name>' 
BADFILE 'sample.bad' 
DISCARDFILE
 
Records NOT satisfying a WHEN clause DISCARDFILE '<file_name>'
DISCARDMAX <integer> 
DISCARDFILE 'sample.dsc' 
CHARACTERSET CHARACTERSET <character_set_name> 
CHARACTERSET WE8MSWIN1252 
LENGTH LENGTH SEMANTICS <BYTE | CHAR> 
LENGTH SEMANTICS BYTE
-- this is the default for all character sets except UTF16 
LOAD TYPES APPEND
INSERT
REPLACE
TRUNCATE 
APPEND 
OPTIONS CLAUSE BINDSIZE = n
COLUMNARRAYROWS = n
DIRECT = {TRUE | FALSE} 
ERRORS = n
LOAD = n 
MULTITHREADING = {TRUE | FALSE}
PARALLEL = {TRUE | FALSE}
READSIZE = n
RESUMABLE = {TRUE | FALSE}
RESUMABLE_NAME = 'text string'
RESUMABLE_TIMEOUT = n
ROWS = n 
SILENT = {HEADER | FEEDBACK | ERRORS | DISCARDS | PARTITIONS | ALL}
SKIP = n 
SKIP_INDEX_MAINTENANCE = {TRUE | FALSE}
SKIP_UNUSABLE_INDEXES = {TRUE | FALSE}
STREAMSIZE = n 
OPTIONS (BINDSIZE=100000, SILENT=(ERRORS, FEEDBACK)) 
PATHS CONVENTIONAL PATH
DIRECT PATH
 
ALL loads demonstrated below are convention WITH the EXCEPTION OF demo 6. 
 
TERMINATORS Comma ','
 
Tab 0x'09' 
 
TRAILING NULLCOLS TRAILING NULLCOLS 
-- assuming this data
10 Accounting
 
-- the following
INTO TABLE dept 
TRAILING NULLCOLS 
( deptno CHAR TERMINATED BY " ", 
dname CHAR TERMINATED BY WHITESPACE, 
loc CHAR TERMINATED BY WHITESPACE) 
 
-- would generate an error without TRAILING NULLCOLS 
-- as it doesn't have loc data 
WHEN WHEN <condition> 
See Demo 5 below 
 
Assembling Logical Records 
CONCATENATE CONCATENATE <number_of_physical_records> 
CONCATENATE 3 
CONTINUEIF CONTINUEIF THIS [PRESERVE] (start_position:end_position) = VALUE 
CONTINUEIF THIS (1:2) = '%%'
 
CONTINUEIF THIS PRESERVE (1:2) = '%%' 
CONTINUEIF CONTINUEIF NEXT [PRESERVE] (start_position:end_position) = VALUE 
CONTINUEIF NEXT (1:2) = '%%'
 
CONTINUEIF NEXT PRESERVE (1:2) = '%%' 
CONTINUEIF CONTINUEIF LAST (start_position:end_position) = VALUE 
-- Tests against the last non-blank character.
-- Allows only a single character for the test 
PRESERVE Preserves the CONTINUEIF characters 
 


 
   Home |    Search |    Code Library |    Sponsors |    Privacy |    Terms of Use |    Contact Us © 2003 - 2024 psoug.org