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
|