Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 Oracle PL/SQL: SQL*Loader Jump to:  
Category: >> Oracle PL/SQL >> SQL*Loader Bookmark and Share

<< 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 
 


Free
Oracle Magazine
Subscriptions
and Oracle White Papers


SQL University.net courses meet the most demanding needs of the business world for advanced education in a cost-effective manner. SQL University.net courses are available immediately for IT professionals and can be taken without disruption of your workplace schedule or processes.

Compared to traditional travel-based training, SQL University.net saves time and valuable corporate resources, allowing companies to do more with less. That's our mission, and that's what we deliver.

Click here to find out more
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 256 users online    © 2009 psoug.org

PSOUG LOGIN
Username: 
Password: 
Forgot your password?