quick reference of sqlldr
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Nov 1 16:52:59 2009
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Usage: SQLLDR keyword=value [,keyword=value,...]
Valid Keywords:
userid -- ORACLE username/password
control -- control file name
log -- log file name
bad -- bad file name
data -- data file name
discard -- discard file name
discardmax -- number of discards to allow (Default all)
skip -- number of logical records to skip (Default 0)
load -- number of logical records to load (Default all)
errors -- number of errors to allow (Default 50)
rows -- number of rows in conventional path bind array or between direct path data saves
(Default: Conventional path 64, Direct path all)
bindsize -- size of conventional path bind array in bytes (Default 256000)
silent -- suppress messages during run (header,feedback,errors,discards,partitions)
direct -- use direct path (Default FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- do parallel load (Default FALSE)
file -- file to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued (Default FALSE) readsize -- size of read buffer (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array (Default 5000)
streamsize -- size of direct path stream buffer in bytes (Default 256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE (Default 7200)
date_cache -- size (in entries) of date conversion cache (Default 1000)
PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords. An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'. One may specify parameters by position before
but not after parameters specified by keywords. For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.
TBL_LOAD_DATAに任意100項目以内のCSVデータdata.csvをロードするサンプルです。
カラム、データタイプと関係ないから、共通といえます。
また、必要に応じて項目数とオプションを調整することができます。
テーブル作成用SQL。あらかじめsqlplusで実行します。
create table TBL_LOAD_DATA ( LOAD_ID NUMBER(9) , LINE_NO NUMBER(9) , DATA_TYPE VARCHAR2(100) , LOAD_TAG VARCHAR(100) , LOAD_DT DATE DEFAULT SYSDATE , D01 VARCHAR2(100) , D02 VARCHAR2(100) , D03 VARCHAR2(100) , D04 VARCHAR2(100) , D05 VARCHAR2(100) , D06 VARCHAR2(100) , D07 VARCHAR2(100) , D08 VARCHAR2(100) , D09 VARCHAR2(100) , D10 VARCHAR2(100) , D11 VARCHAR2(100) , D12 VARCHAR2(100) , D13 VARCHAR2(100) , D14 VARCHAR2(100) , D15 VARCHAR2(100) , D16 VARCHAR2(100) , D17 VARCHAR2(100) , D18 VARCHAR2(100) , D19 VARCHAR2(100) , D20 VARCHAR2(100) , D21 VARCHAR2(100) , D22 VARCHAR2(100) , D23 VARCHAR2(100) , D24 VARCHAR2(100) , D25 VARCHAR2(100) , D26 VARCHAR2(100) , D27 VARCHAR2(100) , D28 VARCHAR2(100) , D29 VARCHAR2(100) , D30 VARCHAR2(100) , D31 VARCHAR2(100) , D32 VARCHAR2(100) , D33 VARCHAR2(100) , D34 VARCHAR2(100) , D35 VARCHAR2(100) , D36 VARCHAR2(100) , D37 VARCHAR2(100) , D38 VARCHAR2(100) , D39 VARCHAR2(100) , D40 VARCHAR2(100) , D41 VARCHAR2(100) , D42 VARCHAR2(100) , D43 VARCHAR2(100) , D44 VARCHAR2(100) , D45 VARCHAR2(100) , D46 VARCHAR2(100) , D47 VARCHAR2(100) , D48 VARCHAR2(100) , D49 VARCHAR2(100) , D50 VARCHAR2(100) , D51 VARCHAR2(100) , D52 VARCHAR2(100) , D53 VARCHAR2(100) , D54 VARCHAR2(100) , D55 VARCHAR2(100) , D56 VARCHAR2(100) , D57 VARCHAR2(100) , D58 VARCHAR2(100) , D59 VARCHAR2(100) , D60 VARCHAR2(100) , D61 VARCHAR2(100) , D62 VARCHAR2(100) , D63 VARCHAR2(100) , D64 VARCHAR2(100) , D65 VARCHAR2(100) , D66 VARCHAR2(100) , D67 VARCHAR2(100) , D68 VARCHAR2(100) , D69 VARCHAR2(100) , D70 VARCHAR2(100) , D71 VARCHAR2(100) , D72 VARCHAR2(100) , D73 VARCHAR2(100) , D74 VARCHAR2(100) , D75 VARCHAR2(100) , D76 VARCHAR2(100) , D77 VARCHAR2(100) , D78 VARCHAR2(100) , D79 VARCHAR2(100) , D80 VARCHAR2(100) , D81 VARCHAR2(100) , D82 VARCHAR2(100) , D83 VARCHAR2(100) , D84 VARCHAR2(100) , D85 VARCHAR2(100) , D86 VARCHAR2(100) , D87 VARCHAR2(100) , D88 VARCHAR2(100) , D89 VARCHAR2(100) , D90 VARCHAR2(100) , D91 VARCHAR2(100) , D92 VARCHAR2(100) , D93 VARCHAR2(100) , D94 VARCHAR2(100) , D95 VARCHAR2(100) , D96 VARCHAR2(100) , D97 VARCHAR2(100) , D98 VARCHAR2(100) , D99 VARCHAR2(100) ) -- TABLESPACE tbs_data_load ;
ロードコントロールファイルdata.ctl。CSVファイルと同じフォルダに格納します。
OPTIONS(LOAD=-1,SKIP=0,ERRORS=-1,ROWS=1000) LOAD DATA CHARACTERSET JA16SJISTILDE INFILE 'data.csv' "STR x'0d0a'" BADFILE 'data.bad' DISCARDFILE 'data.dis' --APPEND, INSERT, REPLACE TRUNCATE INTO TABLE TBL_LOAD_DATA --WHEN (1) = 'ABC' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( D01 CHAR , D02 CHAR , D03 CHAR , D04 CHAR , D05 CHAR , D06 CHAR , D07 CHAR , D08 CHAR , D09 CHAR , D10 CHAR , D11 CHAR , D12 CHAR , D13 CHAR , D14 CHAR , D15 CHAR , D16 CHAR , D17 CHAR , D18 CHAR , D19 CHAR , D20 CHAR , D21 CHAR , D22 CHAR , D23 CHAR , D24 CHAR , D25 CHAR , D26 CHAR , D27 CHAR , D28 CHAR , D29 CHAR , D30 CHAR , D31 CHAR , D32 CHAR , D33 CHAR , D34 CHAR , D35 CHAR , D36 CHAR , D37 CHAR , D38 CHAR , D39 CHAR , D40 CHAR , D41 CHAR , D42 CHAR , D43 CHAR , D44 CHAR , D45 CHAR , D46 CHAR , D47 CHAR , D48 CHAR , D49 CHAR , D50 CHAR , D51 CHAR , D52 CHAR , D53 CHAR , D54 CHAR , D55 CHAR , D56 CHAR , D57 CHAR , D58 CHAR , D59 CHAR , D60 CHAR , D61 CHAR , D62 CHAR , D63 CHAR , D64 CHAR , D65 CHAR , D66 CHAR , D67 CHAR , D68 CHAR , D69 CHAR , D70 CHAR , D71 CHAR , D72 CHAR , D73 CHAR , D74 CHAR , D75 CHAR , D76 CHAR , D77 CHAR , D78 CHAR , D79 CHAR , D80 CHAR , D81 CHAR , D82 CHAR , D83 CHAR , D84 CHAR , D85 CHAR , D86 CHAR , D87 CHAR , D88 CHAR , D89 CHAR , D90 CHAR , D91 CHAR , D92 CHAR , D93 CHAR , D94 CHAR , D95 CHAR , D96 CHAR , D97 CHAR , D98 CHAR , D99 CHAR , --LINE_NO DECIMAL EXTERNAL "SEQ_LOAD_DATA.NEXTVAL", --LINE_NO SEQUENCE (MAX, 1), DATA_ID RECNUM, LOAD_ID CONSTANT 1, DATA_TAG CONSTANT 'DATA TAG', DATA_TYPE CONSTANT 'TYPE' )
コマンドラインloaddata.bat。CSVと同じフォルダに格納して、実行します。
sqlldr username/password@tnsname control=data.ctl
必須ではないが、自動的にロードコントロールファイル作成するdata_ctl_gen.bat(Perlが必要)。
@rem = '--*-Perl-*-- @echo off perl -x -S %0 %* if NOT "%COMSPEC%" == "%SystemRoot%\system32\cmd.exe" goto endofperl if %errorlevel% == 9009 echo You do not have Perl in your PATH. if errorlevel 1 goto script_failed_so_exit_with_non_zero_val 2>nul goto endofperl @rem '; #!perl #line 15 #use strict; #use warnings; if ($#ARGV < 1 or $ARGV[0] eq '-h' or $ARGV[0] eq '--help' or $ARGV[0] eq '/?') { print STDERR "Usage: loaddata <username/password@tnsname> [tag] [type]"; } else { print << "_EOF_"; OPTIONS(LOAD=-1,SKIP=0,ERRORS=-1,ROWS=1000) LOAD DATA CHARACTERSET JA16SJISTILDE INFILE '$ARGV[1]' "STR x'0d0a'" BADFILE 'data.bad' DISCARDFILE 'data.dis' --APPEND, INSERT, REPLACE TRUNCATE INTO TABLE TBL_LOAD_DATA --WHEN (1) = 'ABC' FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS ( D01 CHAR , D02 CHAR , D03 CHAR , D04 CHAR , D05 CHAR , D06 CHAR , D07 CHAR , D08 CHAR , D09 CHAR , D10 CHAR , D11 CHAR , D12 CHAR , D13 CHAR , D14 CHAR , D15 CHAR , D16 CHAR , D17 CHAR , D18 CHAR , D19 CHAR , D20 CHAR , D21 CHAR , D22 CHAR , D23 CHAR , D24 CHAR , D25 CHAR , D26 CHAR , D27 CHAR , D28 CHAR , D29 CHAR , D30 CHAR , D31 CHAR , D32 CHAR , D33 CHAR , D34 CHAR , D35 CHAR , D36 CHAR , D37 CHAR , D38 CHAR , D39 CHAR , D40 CHAR , D41 CHAR , D42 CHAR , D43 CHAR , D44 CHAR , D45 CHAR , D46 CHAR , D47 CHAR , D48 CHAR , D49 CHAR , D50 CHAR , D51 CHAR , D52 CHAR , D53 CHAR , D54 CHAR , D55 CHAR , D56 CHAR , D57 CHAR , D58 CHAR , D59 CHAR , D60 CHAR , D61 CHAR , D62 CHAR , D63 CHAR , D64 CHAR , D65 CHAR , D66 CHAR , D67 CHAR , D68 CHAR , D69 CHAR , D70 CHAR , D71 CHAR , D72 CHAR , D73 CHAR , D74 CHAR , D75 CHAR , D76 CHAR , D77 CHAR , D78 CHAR , D79 CHAR , D80 CHAR , D81 CHAR , D82 CHAR , D83 CHAR , D84 CHAR , D85 CHAR , D86 CHAR , D87 CHAR , D88 CHAR , D89 CHAR , D90 CHAR , D91 CHAR , D92 CHAR , D93 CHAR , D94 CHAR , D95 CHAR , D96 CHAR , D97 CHAR , D98 CHAR , D99 CHAR , --LINE_NO DECIMAL EXTERNAL "SEQ_LOAD_DATA.NEXTVAL", --LINE_NO SEQUENCE (MAX, 1), DATA_ID RECNUM, _EOF_ $username_password_tnsname = $ARGV[0] $load_id = `echo select SEQ_LOAD_DATA.NEXTVAL from dual; \| sqlplus -S $ARGV[0]`; $load_id =~ s/\n//g; print "LOAD_ID CONSTANT " . $load_id . "," . "\n"; print << "_EOF_"; LOAD_TAG CONSTANT '$ARGV[2]', DATA_TYPE CONSTANT '$ARGV[3]' ) _EOF_ } __END__ :endofperl
自動ロードコントロールファイル作成を利用する場合loaddata.batがこのように変わります。
data_ctl_gen.bat username/password@tnsname filename.csv tag... type... > data.ctl sqlldr username/password@tnsname control=data.ctl
sqlldr demo files(the content of case2 is binary, so that cannot post here.)
ulcase.sh
#!/bin/sh # # $Header: ulcase.sh 20-jul-99.19:08:34 cmlim Exp $ # # ulcase.sh # # Copyright (c) Oracle Corporation 1999. All Rights Reserved. # # NAME # ulcase.sh - run sqlldr demos # # DESCRIPTION # Shell script to run sqlldr demos. # Please append to script as more ulcase* demos are added. # # MODIFIED (MM/DD/YY) # cmlim 07/20/99 - create shell script for running all sqlldr demos # cmlim 07/20/99 - Creation # # CASE1 sqlplus scott/tiger @ulcase1 sqlldr scott/tiger ulcase1.ctl # CASE2 sqlldr scott/tiger ulcase2 # CASE3 sqlplus scott/tiger @ulcase3 sqlldr scott/tiger ulcase3 # CASE4 sqlplus scott/tiger @ulcase4 sqlldr scott/tiger ulcase4 # CASE5 sqlplus scott/tiger @ulcase5 sqlldr scott/tiger ulcase5 # CASE6 sqlplus scott/tiger @ulcase6 sqlldr scott/tiger ulcase6 direct=true # CASE7 sqlplus scott/tiger @ulcase7s sqlldr scott/tiger ulcase7 sqlplus scott/tiger @ulcase7e # CASE8 sqlplus scott/tiger @ulcase8 sqlldr scott/tiger ulcase8 # CASE9 sqlplus scott/tiger @ulcase9 sqlldr scott/tiger ulcase9 # CASE10 sqlplus scott/tiger @ulcase10 sqlldr scott/tiger ulcase10
ulcase1.ctl
-- Copyright (c) 1991 by Oracle Corporation -- NAME -- ulcase1.ctl - <one-line expansion of the name> -- DESCRIPTION -- <short description of component this file declares/defines> -- RETURNS -- -- NOTES -- <other useful comments, qualifications, etc.> -- MODIFIED (MM/DD/YY) -- cheigham 08/28/91 - Creation -- -- $Header: ulcase1.ctl,v 1.1 1991/09/02 14:50:39 CHEIGHAM Stab $ case1.ctl -- LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO, DNAME, LOC) BEGINDATA 12,RESEARCH,"SARATOGA" 10,"ACCOUNTING",CLEVELAND 11,"ART",SALEM 13,FINANCE,"BOSTON" 21,"SALES",PHILA. 22,"SALES",ROCHESTER 42,"INT'L","SAN FRAN"
ulcase1.sql
rem rem $Header: ulcase1.sql 14-jul-99.14:22:19 mjaeger Exp $ rem rem Copyright (c) 1991, 1999, Oracle Corporation. All rights reserved. rem rem NAME rem ulcase1.sql - <one-line expansion of the name> rem DESCRIPTION rem <short description of component this file declares/defines> rem RETURNS rem rem NOTES rem <other useful comments, qualifications, etc.> rem MODIFIED (MM/DD/YY) rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines rem jstenois 06/17/99 - cleanup tables before load and show feedback rem ksudarsh 03/01/93 - comment out vms specific host command rem ksudarsh 12/29/92 - Creation rem cheigham 08/28/91 - Creation rem set termout off rem host write sys$output "Building first demonstration tables. Please wait" drop table emp; drop table dept; create table emp (empno number(4) not null, ename char(10), job char(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2)); create table dept (deptno number(2), dname char(14) , loc char(13) ) ; exit
ulcase2.ctl
-- Copyright (c) 1991 by Oracle Corporation -- NAME -- ulcase2.ctl - <one-line expansion of the name> -- DESCRIPTION -- <short description of component this file declares/defines> -- RETURNS -- -- NOTES -- <other useful comments, qualifications, etc.> -- MODIFIED (MM/DD/YY) -- ksudarsh 04/08/94 - merge changes from branch 1.3.710.1 -- ksudarsh 02/21/94 - quote dat file -- ksudarsh 03/11/93 - make filename lowercase -- ksudarsh 11/06/92 - infile is ulcase2 -- cheigham 08/28/91 - Creation -- -- $Header: ulcase2.ctl,v 1.4 1994/04/08 13:42:44 ksudarsh Exp $ case2.ctl -- LOAD DATA INFILE 'ulcase2.dat' INTO TABLE EMP ( EMPNO POSITION(01:04) INTEGER EXTERNAL, ENAME POSITION(06:15) CHAR, JOB POSITION(17:25) CHAR, MGR POSITION(27:30) INTEGER EXTERNAL, SAL POSITION(32:39) DECIMAL EXTERNAL, COMM POSITION(41:48) DECIMAL EXTERNAL, DEPTNO POSITION(50:51) INTEGER EXTERNAL)
ulcase2.dat
7782 CLARK MANAGER 7839 2572.50 10 7839 KING PRESIDENT 5500.00 10 7934 MILLER CLERK 7782 920.00 10 7566 JONES MANAGER 7839 3123.75 20 7499 ALLEN SALESMAN 7698 1600.00 300.00 30 7654 MARTIN SALESMAN 7698 1312.50 1400.00 30 7658 CHAN ANALYST 7566 3450.00 20
ulcase3.ctl
-- Copyright (c) 1991 by Oracle Corporation -- NAME -- ulcase3.ctl - <one-line expansion of the name> -- DESCRIPTION -- <short description of component this file declares/defines> -- RETURNS -- -- NOTES -- <other useful comments, qualifications, etc.> -- MODIFIED (MM/DD/YY) -- cheigham 08/28/91 - Creation -- -- $Header: ulcase3.ctl,v 1.1 1991/09/02 14:52:19 CHEIGHAM Stab $ case3.ctl -- -- Variable length, delimited and enclosed data format LOAD DATA INFILE * APPEND INTO TABLE EMP FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (empno, ename, job, mgr, hiredate DATE(20) "DD-Month-YYYY", sal, comm, deptno CHAR TERMINATED BY ':', projno, loadseq SEQUENCE(MAX,1) ) BEGINDATA 7782, "Clark", "Manager", 7839, 09-June-1981, 2572.50,, 10:101 7839, "King", "President", , 17-November-1981, 5500.00,, 10:102 7934, "Miller", "Clerk", 7782, 23-January-1982, 920.00,, 10:102 7566, "Jones", "Manager", 7839, 02-April-1981, 3123.75,, 20:101 7499, "Allen", "Salesman", 7698, 20-February-1981, 1600.00, 300.00, 30:103 7654, "Martin", "Salesman", 7698, 28-September-1981, 1312.50, 1400.00, 30:103 7658, "Chan", "Analyst", 7566, 03-May-1982, 3450,, 20:101
ulcase3.sql
rem rem $Header: ulcase3.sql 14-jul-99.14:23:36 mjaeger Exp $ rem rem Copyright (c) 1991, 1999, Oracle Corporation. All rights reserved. rem rem NAME rem ulcase3.sql - <one-line expansion of the name> rem DESCRIPTION rem <short description of component this file declares/defines> rem RETURNS rem rem NOTES rem ulcase2.sql must be executed before this pocedure. rem MODIFIED (MM/DD/YY) rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines rem jstenois 06/17/99 - cleanup tables before load and show feedback rem ksudarsh 03/11/93 - comment out vms specific host command rem ksudarsh 12/29/92 - Creation rem cheigham 08/28/91 - Creation rem set termout off rem Do not clean up table because this example shows appending to existing rem rows in table that also has new columns. rem host write sys$output "Adding columns to emp. Please wait." alter table emp add (projno number, loadseq number); exit
ulcase4.ctl
-- Copyright (c) 1991 by Oracle Corporation -- NAME -- ulcase4.ctl - <one-line expansion of the name> -- DESCRIPTION -- <short description of component this file declares/defines> -- RETURNS -- -- NOTES -- <other useful comments, qualifications, etc.> -- MODIFIED (MM/DD/YY) -- ksudarsh 11/06/92 - infile is ulcase4 -- cheigham 08/28/91 - Creation -- -- $Header: ulcase4.ctl,v 1.2 1992/11/06 11:09:40 KSUDARSH Exp $ case4.ctl -- LOAD DATA INFILE "ulcase4.dat" DISCARDFILE "ulcase4.dis" DISCARDMAX 999 REPLACE CONTINUEIF (1) = '*' INTO TABLE EMP ( EMPNO POSITION(01:04) INTEGER EXTERNAL, ENAME POSITION(06:15) CHAR, JOB POSITION(17:25) CHAR, MGR POSITION(27:30) INTEGER EXTERNAL, SAL POSITION(32:39) DECIMAL EXTERNAL, COMM POSITION(41:48) DECIMAL EXTERNAL, DEPTNO POSITION(50:51) INTEGER EXTERNAL, HIREDATE POSITION(52:60) INTEGER EXTERNAL)
ulcase4.dat
*7782 CLARK MA NAGER 7839 2572.50 -10 2512-NOV-85 *7839 KING PR ESIDENT 5500.00 2505-APR-83 *7934 MILLER CL ERK 7782 920.00 2508-MAY-80 *7566 JONES MA NAGER 7839 3123.75 2517-JUL-85 *7499 ALLEN SA LESMAN 7698 1600.00 300.00 25 3-JUN-84 *7654 MARTIN SA LESMAN 7698 1312.50 1400.00 2521-DEC-85 *7658 CHAN AN ALYST 7566 3450.00 2516-FEB-84 * CHEN AN ALYST 7566 3450.00 2516-FEB-84 *7658 CHIN AN ALYST 7566 3450.00 2516-FEB-84
ulcase4.sql
rem rem $Header: ulcase4.sql 14-jul-99.14:24:22 mjaeger Exp $ rem rem Copyright (c) 1990, 1999, Oracle Corporation. All rights reserved. rem rem NAME rem <name> rem FUNCTION rem NOTES rem MODIFIED (MM/DD/YY) rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines rem jstenois 06/17/99 - cleanup tables before load and show feedback rem ksudarsh 03/11/93 - comment out vms specific host command rem ksudarsh 12/29/92 - Creation rem cheigham 08/28/91 - Creation rem Heigham 11/21/90 - create UNIQUE index rem set termout off rem host write sys$output "Building case 4 demonstration tables. Please wait" drop table emp; create table emp (empno number(4) not null, ename char(10), job char(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2)); create unique index empix on emp(empno); exit
ulcase5.ctl
-- Copyright (c) 1991 by Oracle Corporation -- NAME -- ulcase5.ctl - <one-line expansion of the name> -- DESCRIPTION -- <short description of component this file declares/defines> -- RETURNS -- -- NOTES -- <other useful comments, qualifications, etc.> -- MODIFIED (MM/DD/YY) -- ksudarsh 04/08/94 - merge changes from branch 1.3.710.1 -- ksudarsh 02/21/94 - quote dat file -- ksudarsh 03/11/93 - make filename lowercase -- ksudarsh 11/06/92 - infile is ulcase5 -- cheigham 08/28/91 - Creation -- -- $Header: ulcase5.ctl,v 1.4 1994/04/08 13:44:31 ksudarsh Exp $ case5.ctl -- -- Loads EMP records from first 23 characters -- Creates and loads PROJ records for each PROJO listed -- for each employee LOAD DATA INFILE 'ulcase5.dat' BADFILE 'ulcase5.bad' DISCARDFILE 'ulcase5.dis' REPLACE INTO TABLE EMP (EMPNO POSITION(1:4) INTEGER EXTERNAL, ENAME POSITION(6:15) CHAR, DEPTNO POSITION(17:18) CHAR, MGR POSITION(20:23) INTEGER EXTERNAL) INTO TABLE PROJ -- PROJ has two columns, both not null: EMPNO and PROJNO WHEN PROJNO != ' ' (EMPNO POSITION(1:4) INTEGER EXTERNAL, PROJNO POSITION(25:27) INTEGER EXTERNAL) -- 1st proj INTO TABLE PROJ WHEN PROJNO != ' ' (EMPNO POSITION(1:4) INTEGER EXTERNAL, PROJNO POSITION(29:31) INTEGER EXTERNAL) -- 2nd proj INTO TABLE PROJ WHEN PROJNO != ' ' (EMPNO POSITION(1:4) INTEGER EXTERNAL, PROJNO POSITION(33:35) INTEGER EXTERNAL) -- 3rd proj
ulcase5.dat
1234 BAKER 10 9999 101 102 103 1234 JOKER 10 9999 777 888 999 2664 YOUNG 20 2893 425 abc 102 5321 OTOOLE 10 9999 321 55 40 2134 FARMER 20 4555 236 456 2414 LITTLE 20 5634 236 456 40 6542 LEE 10 4532 102 321 14 2849 EDDS xx 4555 294 40 4532 PERKINS 10 9999 40 1244 HUNT 11 3452 665 133 456 123 DOOLITTLE 12 9940 132 1453 MACDONALD 25 5532 200
ulcase5.sql
rem rem $Header: ulcase5.sql 20-jul-99.18:06:21 cmlim Exp $ rem rem Copyright (c) 1991, 1999, Oracle Corporation. All rights reserved. rem rem NAME rem ulcase5.sql - <one-line expansion of the name> rem DESCRIPTION rem <short description of component this file declares/defines> rem RETURNS rem rem NOTES rem <other useful comments, qualifications, etc.> rem MODIFIED (MM/DD/YY) Rem cmlim 07/20/99 - add unique index on empno rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines rem jstenois 06/17/99 - cleanup tables before load and show feedback rem ksudarsh 03/11/93 - comment out vms specific host command rem ksudarsh 12/29/92 - Creation rem cheigham 08/28/91 - Creation rem set termout off rem host write sys$output "Building case 5 demonstration tables. Please wait" drop table emp; drop table proj; create table emp (empno number(4) not null, ename char(10), job char(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2)); create unique index empix on emp(empno); create table proj (empno number, projno number); exit
ulcase6.ctl
-- Copyright (c) 1991, 1997 by Oracle Corporation -- NAME -- ulcase6.ctl - <one-line expansion of the name> -- DESCRIPTION -- <short description of component this file declares/defines> -- RETURNS -- -- NOTES -- <other useful comments, qualifications, etc.> -- MODIFIED (MM/DD/YY) -- msakayed 09/02/97 - remove nullif on empno -- jhealy 01/27/95 - merge changes from branch 1.2.720.1 -- ksudarsh 11/06/92 - infile is ulcase6 -- cheigham 08/28/91 - Creation -- -- $Header: ulcase6.ctl 02-sep-97.15:39:53 msakayed Exp $ -- load data infile 'ulcase6.dat' replace into table emp sorted indexes (empix) (empno position(1:4), ename position(6:15), job position(17:25), mgr position(27:30) nullif mgr=blanks, sal position(32:39) nullif sal=blanks, comm position(41:48) nullif comm=blanks, deptno position(50:51) nullif empno=blanks)
ulcase6.dat
7499 ALLEN SALESMAN 7698 1600.00 300.00 30 7566 JONES MANAGER 7839 3123.75 20 7654 MARTIN SALESMAN 7698 1312.50 1400.00 30 7658 CHAN ANALYST 7566 3450.00 20 7782 CLARK MANAGER 7839 2572.50 10 7839 KING PRESIDENT 5500.00 10 7934 MILLER CLERK 7782 920.00 10
ulcase6.sql
rem rem $Header: ulcase6.sql 14-jul-99.14:25:48 mjaeger Exp $ rem rem Copyright (c) 1991, 1999, Oracle Corporation. All rights reserved. rem rem NAME rem ulcase6.sql - <one-line expansion of the name> rem DESCRIPTION rem <short description of component this file declares/defines> rem RETURNS rem rem NOTES rem <other useful comments, qualifications, etc.> rem MODIFIED (MM/DD/YY) rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines rem jstenois 06/17/99 - cleanup tables before load and show feedback rem ksudarsh 02/21/94 - create unique index "empix" rem ksudarsh 03/11/93 - comment out vms specific host command rem ksudarsh 12/29/92 - Creation rem ksudarsh 12/28/92 - Don't drop dept rem cheigham 08/28/91 - Creation rem cheigham 03/19/91 - Creation set termout off rem host write sys$output "Building case 6 demonstration tables. Please wait" drop table emp; create table emp (empno number(4) not null, ename char(10), job char(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2)); create unique index empix on emp(empno); exit
ulcase7.ctl
-- Copyright (c) 1991, 1999 by Oracle Corporation -- NAME -- ulcase7.ctl - <one-line expansion of the name> -- DESCRIPTION -- <short description of component this file declares/defines> -- RETURNS -- -- NOTES -- <other useful comments, qualifications, etc.> -- MODIFIED (MM/DD/YY) -- rpfau 07/27/99 - Fixing ulcase7.ctl -- ksudarsh 04/08/94 - merge changes from branch 1.1.710.1 -- ksudarsh 02/21/94 - quote dat file -- ksudarsh 12/30/92 - Creation -- ksudarsh 12/27/92 - Creation -- -- $Header: ulcase7.ctl 27-jul-99.16:07:41 rpfau Exp $ ulcase7.ctl -- LOAD DATA INFILE 'ulcase7.dat' DISCARDFILE 'ulcase7.dis' APPEND INTO TABLE emp WHEN (57)='.' TRAILING NULLCOLS (hiredate SYSDATE, deptno POSITION(1:2) INTEGER EXTERNAL(3) NULLIF deptno=BLANKS, job POSITION(7:14) CHAR TERMINATED BY WHITESPACE NULLIF job=BLANKS "UPPER(:job)", mgr POSITION(28:31) INTEGER EXTERNAL TERMINATED BY WHITESPACE NULLIF mgr=BLANKS, ename POSITION (34:41) CHAR TERMINATED BY WHITESPACE "UPPER(:ename)", empno INTEGER EXTERNAL TERMINATED BY WHITESPACE, sal POSITION(51) CHAR TERMINATED BY WHITESPACE "TO_NUMBER(:sal,'$99,999.99')", comm INTEGER EXTERNAL ENCLOSED BY '(' AND '%' ":comm * 100" )
ulcase7.dat
Today's Newly Hired Employees Dept Job Manager MgrNo Emp Name EmpNo Salary/Commission ---- -------- -------- ----- -------- ----- ----------------- 20 Salesman Blake 7698 Shepard 8061 $1,600.00 (3%) Falstaff 8066 $1,250.00 (5%) Major 8064 $1,250.00 (14%) 30 Clerk Scott 7788 Conrad 8062 $1,100.00 Ford 7369 DeSilva 8063 $800.00 Manager King 7839 Provo 8065 $2,975.00
ulcase7e.sql
rem rem $Header: ulcase7e.sql 14-jul-99.14:26:51 mjaeger Exp $ rem rem Copyright (c) 1991, 1999, Oracle Corporation. All rights reserved. rem rem NAME rem ulcase7e.sql - <one-line expansion of the name> rem DESCRIPTION rem <short description of component this file declares/defines> rem RETURNS rem rem NOTES rem <other useful comments, qualifications, etc.> rem MODIFIED (MM/DD/YY) rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines rem jstenois 06/17/99 - cleanup tables before load and show feedback rem ksudarsh 03/11/93 - comment out vms specific host command rem ksudarsh 12/30/92 - Creation rem ksudarsh 12/27/92 - Creation rem rem ULDEMO7E.SQL rem End-script for SQL*Loader Examples, Case 7 set termout off rem host write sys$output "Cleaning up Case 7 Trigger and Package." DROP PACKAGE uldemo7; DROP TRIGGER uldemo7_emp_insert; EXIT
ulcase7s.sql
rem rem $Header: ulcase7s.sql 26-jul-99.09:39:37 mjaeger Exp $ rem rem Copyright (c) 1991, 1999, Oracle Corporation. All rights reserved. rem rem NAME rem ulcase7s.sql - <one-line expansion of the name> rem DESCRIPTION rem <short description of component this file declares/defines> rem RETURNS rem rem NOTES rem <other useful comments, qualifications, etc.> rem MODIFIED (MM/DD/YY) rem mjaeger 07/26/99 - bug 808870: OCCS: convert tabs, no long lines rem jstenois 06/17/99 - cleanup tables before load and show feedback rem ksudarsh 03/11/93 - comment out vms specific host command rem ksudarsh 12/30/92 - Creation rem ksudarsh 12/27/92 - Creation rem rem ULDEMO7S.SQL rem Start-script for SQL*Loader Examples, Case 7 rem The variables the insert-trigger uses to save the last valid value rem are defined in a package so they will persist between calls. rem Since these values will be accessed by anyone inserting into EMP, only rem the user doing the load should have access to EMP during this time rem (Alternatively, the trigger could be modified to check the USERENV fnction rem in a WHEN clause and only perform its functions for a particular user.) set termout off rem host write sys$output "Building Package and Trigger for Case 7.Please wait" CREATE OR REPLACE PACKAGE uldemo7 AS last_deptno NUMBER; last_job CHAR(9); last_mgr NUMBER; END uldemo7; / CREATE OR REPLACE TRIGGER uldemo7_emp_insert BEFORE INSERT ON emp FOR EACH ROW BEGIN IF :new.deptno IS NOT NULL THEN uldemo7.last_deptno := :new.deptno; -- save value for later use ELSE :new.deptno := uldemo7.last_deptno; -- use last valid value END IF; IF :new.job IS NOT NULL THEN uldemo7.last_job := :new.job; -- save value for later use ELSE :new.job := uldemo7.last_job; -- use last valid value END IF; IF :new.mgr IS NOT NULL THEN uldemo7.last_mgr := :new.mgr; -- save value for later use ELSE :new.mgr := uldemo7.last_mgr; -- use last valid value END IF; END; / EXIT
ulcase8.ctl
-- The data contains records for multiple partitions of lineitem but we are -- loading only partition ship_q1. We will get a message for every record -- that cannot be loaded into that partition. LOAD DATA INFILE 'ulcase8.dat' "fix 129" BADFILE 'ulcase8.bad' TRUNCATE INTO TABLE lineitem PARTITION (ship_q1) (l_orderkey position (1:6) char, l_partkey position (7:11) char, l_suppkey position (12:15) char, l_linenumber position (16:16) char, l_quantity position (17:18) char, l_extendedprice position (19:26) char, l_discount position (27:29) char, l_tax position (30:32) char, l_returnflag position (33:33) char, l_linestatus position (34:34) char, l_shipdate position (35:43) char, l_commitdate position (44:52) char, l_receiptdate position (53:61) char, l_shipinstruct position (62:78) char, l_shipmode position (79:85) char, l_comment position (86:128) char)
ulcase8.dat
1 151978511724386.60 7.04.0NO09-SEP-6412-FEB-9622-MAR-96DELIVER IN PERSONTRUCK iPBw4mMm7w7kQ zNPL i261OPP 1 2731 73223658958.28.09.06NO12-FEB-9628-FEB-9620-APR-96TAKE BACK RETURN MAIL 5wM04SNyl0AnghCP2nx lAi 1 3370 3713 810210.96 .1.02NO29-MAR-9605-MAR-9631-JAN-96TAKE BACK RETURN REG AIRSQC2C 5PNCy4mM 1 5214 46542831197.88.09.06NO21-APR-9630-MAR-9616-MAY-96NONE AIR Om0L65CSAwSj5k6k 1 6564 6763246897.92.07.02NO30-MAY-9607-FEB-9603-FEB-96DELIVER IN PERSONMAIL CB0SnyOL PQ32B70wB75k 6Aw10m0wh 1 7403 160524 31329.6 .1.04NO30-JUN-9614-MAR-9601 APR-96NONE FOB C2gOQj OB6RLk1BS15 igN 2 8819 82012441659.44 0.08NO05-AUG-9609-FEB-9711-MAR-97COLLECT COD AIR O52M70MRgRNnmm476mNm 3 9451 721230 41113.5.05.01AF05-SEP-9629-DEC-9318-FEB-94TAKE BACK RETURN FOB 6wQnO0Llg6y 3 9717 1834440788.44.07.03RF09-NOV-9623-DEC-9315-FEB-94TAKE BACK RETURN SHIP LhiA7wygz0k4g4zRhMLBAM 3 9844 1955 6 8066.64.04.01RF28-DEC-9615-DEC-9314-FEB-94TAKE BACK RETURN REG AIR6nmBmjQkgiCyzCQBkxPPOx5j4hB 0lRywgniP1297
ulcase8.sql
rem Rem $Header: ulcase8.sql 20-jul-99.18:20:38 cmlim Exp $ rem rem ulcase8.sql rem rem Copyright (c) 1998, 1999, Oracle Corporation. All rights reserved. rem rem NAME rem ulcase8.sql - Setup for SQL Loader example 8 rem rem DESCRIPTION rem Create partitioned table for example 8 rem rem NOTES rem Note that all partitions are created in the default tablespace. rem Normally, each partition would be in a saparate tablespace, but we rem use the same tablespace to keep the example simple. rem rem MODIFIED (MM/DD/YY) Rem cmlim 07/20/99 - fix syntax for create table rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines rem jstenois 06/17/99 - cleanup tables before load and show feedback rem jstenois 11/06/98 - example of loading fix record length file rem jstenois 11/06/98 - Created rem set termout off rem host write sys$output "Building case 8 demonstration tables. Please wait" drop table lineitem; create table lineitem (l_orderkey number, l_partkey number, l_suppkey number, l_linenumber number, l_quantity number, l_extendedprice number, l_discount number, l_tax number, l_returnflag char, l_linestatus char, l_shipdate date, l_commitdate date, l_receiptdate date, l_shipinstruct char(17), l_shipmode char(7), l_comment char(43)) partition by range (l_shipdate) ( partition ship_q1 values less than (TO_DATE('01-APR-1996', 'DD-MON-YYYY')), partition ship_q2 values less than (TO_DATE('01-JUL-1996', 'DD-MON-YYYY')), partition ship_q3 values less than (TO_DATE('01-OCT-1996', 'DD-MON-YYYY')), partition ship_q4 values less than (TO_DATE('01-JAN-1997', 'DD-MON-YYYY')) ); exit
ulcase9.ctl
-- This is an example of using SQL Loader to load LOBs from secondaray data -- file. In this example, we are loading the RESUME filed in the EMP table. -- We have one file per resume (the "TERMINATED BY EOF" clause indicates -- this) and the name of the file containing the resume is in field RES_FILE. -- -- Note that since RES_FILE is a FILLER field, the file name stored in the -- field is not loaded into any field in the table. -- Also note that the field name for column RESUME is quoted since RESUME is -- also a keyword for SQL Loader. The quotes force SQL Loader to treat it -- as a column name instead. LOAD DATA INFILE * INTO TABLE EMP REPLACE FIELDS TERMINATED BY ',' ( EMPNO INTEGER EXTERNAL, ENAME CHAR, JOB CHAR, MGR INTEGER EXTERNAL, SAL DECIMAL EXTERNAL, COMM DECIMAL EXTERNAL, DEPTNO INTEGER EXTERNAL, RES_FILE FILLER CHAR, "RESUME" LOBFILE (RES_FILE) TERMINATED BY EOF NULLIF RES_FILE = 'NONE' ) BEGINDATA 7782,CLARK,MANAGER,7839,2572.50,,10,ulcase91.dat 7839,KING,PRESIDENT,,5500.00,,10,ulcase92.dat 7934,MILLER,CLERK,7782,920.00,,10,ulcase93.dat 7566,JONES,MANAGER,7839,3123.75,,20,ulcase94.dat 7499,ALLEN,SALESMAN,7698,1600.00,300.00,30,ulcase95.dat 7654,MARTIN,SALESMAN,7698,1312.50,1400.00,30,ulcase96.dat 7658,CHAN,ANALYST,7566,3450.00,,20,NONE
ulcase9.sql
rem rem $Header: ulcase9.sql 14-jul-99.14:28:05 mjaeger Exp $ rem rem ulcase9.sql rem rem Copyright (c) 1998, 1999, Oracle Corporation. All rights reserved. rem rem NAME rem ulcase9.sql - setup for SQL Loader example 9 rem rem DESCRIPTION rem Add RESUME column to EMP for example of using SQL Loader to load LOBs rem rem NOTES rem Assumes an EMP table already exists rem rem MODIFIED (MM/DD/YY) rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines rem jstenois 06/17/99 - cleanup tables before load and show feedback rem jstenois 10/26/98 - demo of 8.1 features for sqlldr rem jstenois 10/26/98 - Created rem set termout off rem host write sys$output "Building case 9 demonstration tables. Please wait" drop table emp; create table emp (empno number(4) not null, ename char(10), job char(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2), resume clob); exit
ulcase10.ctl
-- Inital load of CUSTOMERS table and ORDERS table. Load one customer record -- and one order record for the customer -- -- This example shows how to load a REF field and a VARRAY. The ORDER table -- has a primary key reference to the customer table and it stores the order -- items in a VARRAY. -- LOAD DATA INFILE * CONTINUEIF THIS (1) = '*' INTO TABLE customers replace fields terminated by "," ( cust_no char, name char, addr char ) INTO TABLE orders replace fields terminated by "," ( order_no char, cust_no FILLER char, cust REF (CONSTANT 'CUSTOMERS', cust_no), item_list_count FILLER char, item_list varray count (item_list_count) ( item_list column object ( item char, cnt char, price char ) ) ) BEGINDATA *00001,Spacely Sprockets,15 Space Way, *00101,00001,2, *Sprocket clips, 10000, .01, Sprocket cleaner, 10, 14.00 *00002,Cogswell Cogs,12 Cogswell Lane, *00100,00002,4, *one quarter inch cogs,1000,.02, *one half inch cog, 150, .04, *one inch cog, 75, .10, Custom coffee mugs, 10, 2.50
ulcase10.sql
rem Rem $Header: ulcase10.sql 11-may-00.13:48:45 rpfau Exp $ rem rem ulcase10.sql rem rem Copyright (c) 1998, 1999,, 2000 Oracle Corporation. All rights reserved. rem rem NAME rem ulcase10.sql - set up for SQL Loader example 10 rem rem DESCRIPTION rem Create tables need to for example of using SQL Loader to load rem VARRAYS and references rem rem NOTES rem none rem rem MODIFIED (MM/DD/YY) Rem rpfau 05/11/00 - Remove slashes on create table commands so it Rem runs successfully using sqlplus. Rem cmlim 07/27/99 - add / rem mjaeger 07/14/99 - bug 808870: OCCS: convert tabs, no long lines rem jstenois 06/17/99 - cleanup tables before load and show feedback rem jstenois 10/26/98 - demo of 8.1 features for sqlldr rem jstenois 10/26/98 - Created rem rem host write sys$output "Building case 10 demonstration tables. Please wait" rem do all cleanup drop table orders; drop table customers; drop type item_list_type; drop type item_type; drop type customer_type; rem Create an ORDER record that has a VARRAY for the items that comprise the rem order and has a reference field to a record in the CUSTOMER table for rem the customer placing the order. rem create customer type create type customer_type as object ( cust_no char(5), name char(20), addr char(20) ); / rem create object table for customer type create table customers of customer_type (primary key (cust_no)) object id primary key; rem create type for order items create type item_type as object ( item varchar(50), cnt number, price number(7,2) ); / rem create varray type for order items create type item_list_type as varray (1000) of item_type; / rem create orders table with varray for items and ref to object table create table orders ( order_no char(5), cust ref customer_type references customers, item_list item_list_type ); exit; /
ulcase11.ctl
-- Copyright (c) 2001 by Oracle Corporation -- NAME -- ulcase11.ctl - Load Data in the Unicode Character Set UTF-16 -- DESCRIPTION -- Loads data in the Unicode character set UTF-16. The data is in litle -- endian byte order. This means that depending on whether SQL*Loader is -- running on a little endian or a big endian system, it will have to -- byte swap the UTF-16 character data as necessary. This load uses -- character length semantics, the default for the character set UTF-16. -- -- This case study is modeled after case study 3 (ulcase3), which loads -- variable length delimited (terminated and enclosed) data. -- -- RETURNS -- -- NOTES -- None -- MODIFIED (MM/DD/YY) -- rpfau 02/06/01 - Merged rpfau_sqlldr_add_case_study_11 -- rpfau 01/30/01 - Creation -- LOAD DATA CHARACTERSET utf16 BYTEORDER little INFILE ulcase11.dat REPLACE INTO TABLE EMP FIELDS TERMINATED BY X'002c' OPTIONALLY ENCLOSED BY X'0022' (empno integer external (5), ename, job, mgr, hiredate DATE(20) "DD-Month-YYYY", sal, comm, deptno CHAR(5) TERMINATED BY ":", projno, loadseq SEQUENCE(MAX,1) )
ulcase11.sql
Rem Rem $Header: ulcase11.sql 06-feb-2001.14:26:12 rpfau Exp $ Rem Rem ulcase11.sql Rem Rem Copyright (c) Oracle Corporation 2001. All Rights Reserved. Rem Rem NAME Rem ulcase11.sql - Set up for SQL Loade example 11 Rem Rem DESCRIPTION Rem Create table emp for example loading little endian unicode (UTF-16) Rem data. Rem Rem NOTES Rem None Rem Rem MODIFIED (MM/DD/YY) Rem rpfau 02/06/01 - Merged rpfau_sqlldr_add_case_study_11 Rem rpfau 01/30/01 - Created Rem set termout off rem host write sys$output "Building demonstration tables for case study 11. Please wait" drop table emp; create table emp (empno number(4) not null, ename char(10), job char(9), mgr number(4), hiredate date, sal number(7,2), comm number(7,2), deptno number(2), projno number, loadseq number); exit
ulcase91.dat
Resume for Mary Clark Career Objective: Manage a sales team with consistent record breaking performance. Education: BA Business University of Iowa 1992 Experience: 1992-1994 - Sales Support at MicroSales Inc. Won "Best Sales Support" award in 1993 and 1994 1994-Present - Sales Manager at MicroSales Inc. Most sales in mid-South division for 2 years
ulcase92.dat
Resume for Monica King Career Objective: President of large computer services company Education: BA English Literature Bennington, 1985 Experience: 1985-1986 - Mailroom at New World Services 1986-1987 - Secretary for sales management at New World Services 1988-1989 - Sales support at New World Services 1990-1992 - Saleman at New World Services 1993-1994 - Sales Manager at New World Services 1995 - Vice President of Sales and Marketing at New World Services 1996-Present - President of New World Services
ulcase93.dat
Resume for Dan Miller Career Objective: Work as a sales support specialist for a services company Education: Plainview High School, 1996 Experience: 1996 - Present: Mail room clerk at New World Services
ulcase94.dat
Resume for Alyson Jones Career Objective: Work in senior sales management for a vibrant and growing company Education: BA Philosophy Howard Univerity 1993 Experience: 1993 - Sales Support for New World Services 1994-1995 - Salesman for New World Services. Led in US sales in both 1994 and 1995. 1996 - present - Sales Manager New World Services. My sales team has beat its quota by at least 15% each year.
ulcase95.dat
Resume for David Allen Career Objective: Senior Sales man for agressive Services company Education: BS Business Administration, Weber State 1994 Experience: 1993-1994 - Sales Support New World Services 1994-present - Salesman at New World Service. Won sales award for exceeding sales quota by over 20% in 1995, 1996.
ulcase96.dat
Resume for Tom Martin Career Objective: Salesman for a computing service company Education: 1988 - BA Mathematics, University of the North Experience: 1988-1992 Sales Support, New World Services 1993-present Salesman New World Services
sqlldr … external_table=…
can generate external table from sqlldr control file.
the available values of external_table are NOT_USE, GENERATE_ONLY, EXEUTE.
NOT_USE | disable the function |
GENERATE_ONLY | check the function and create the script |
EXEUTE | load data with external table |
Example
empxt1.dat
360,Jane,Janus,ST_CLERK,121,17-MAY-2001,3000,0,50,jjanus 361,Mark,Jasper,SA_REP,145,17-MAY-2001,8000,.1,80,mjasper 362,Brenda,Starr,AD_ASST,200,17-MAY-2001,5500,0,10,bstarr 363,Alex,Alda,AC_MGR,145,17-MAY-2001,9000,.15,80,aaldag
empxt1.ctl
LOAD DATA INFILE 'empxt1.dat' badfile 'empxt1.bad' APPEND INTO TABLE employees_ldr fields terminated by ',' ( employee_id, first_name, last_name, job_id, manager_id, hire_date date "dd-mon-yyyy", salary, commission_pct, department_id, email )
generate external table script with sqlldr
sqlldr hr/hr control=empxt1.ctl external_table=GENERATE_ONLY
SQL*Loader: Release 10.2.0.1.0 - Production on Sun Nov 1 16:43:38 2009 Copyright (c) 1982, 2005, Oracle. All rights reserved. Control File: empxt1.ctl Data File: empxt1.dat Bad File: empxt1.bad Discard File: none specified (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Continuation: none specified Path used: External Table Table EMPLOYEES_LDR, loaded from every logical record. Insert option in effect for this table: APPEND Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- EMPLOYEE_ID FIRST * , CHARACTER FIRST_NAME NEXT * , CHARACTER LAST_NAME NEXT * , CHARACTER JOB_ID NEXT * , CHARACTER MANAGER_ID NEXT * , CHARACTER HIRE_DATE NEXT * , DATE dd-mon-yyyy SALARY NEXT * , CHARACTER COMMISSION_PCT NEXT * , CHARACTER DEPARTMENT_ID NEXT * , CHARACTER EMAIL NEXT * , CHARACTER CREATE TABLE statement for external table: ------------------------------------------------------------------------ CREATE TABLE "SYS_SQLLDR_X_EXT_EMPLOYEES_LDR" ( "EMPLOYEE_ID" NUMBER(4), "FIRST_NAME" VARCHAR2(20), "LAST_NAME" VARCHAR2(25), "JOB_ID" VARCHAR2(10), "MANAGER_ID" NUMBER(4), "HIRE_DATE" DATE, "SALARY" NUMBER(8,2), "COMMISSION_PCT" NUMBER(2,2), "DEPARTMENT_ID" NUMBER(4), "EMAIL" VARCHAR2(25) ) ORGANIZATION external ( TYPE oracle_loader DEFAULT DIRECTORY ADMIN_DAT_DIR ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII BADFILE 'ADMIN_LOG_DIR':'empxt1.bad' LOGFILE 'empxt1.log_xt' READSIZE 1048576 FIELDS TERMINATED BY "," LDRTRIM REJECT ROWS WITH ALL NULL FIELDS ( "EMPLOYEE_ID" CHAR(255) TERMINATED BY ",", "FIRST_NAME" CHAR(255) TERMINATED BY ",", "LAST_NAME" CHAR(255) TERMINATED BY ",", "JOB_ID" CHAR(255) TERMINATED BY ",", "MANAGER_ID" CHAR(255) TERMINATED BY ",", "HIRE_DATE" CHAR(255) TERMINATED BY "," DATE_FORMAT DATE MASK "dd-mon-yyyy", "SALARY" CHAR(255) TERMINATED BY ",", "COMMISSION_PCT" CHAR(255) TERMINATED BY ",", "DEPARTMENT_ID" CHAR(255) TERMINATED BY ",", "EMAIL" CHAR(255) TERMINATED BY "," ) ) location ( 'empxt1.dat' ) )REJECT LIMIT UNLIMITED INSERT statements used to load internal tables: ------------------------------------------------------------------------ INSERT /*+ append */ INTO EMPLOYEES_LDR ( EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOB_ID, MANAGER_ID, HIRE_DATE, SALARY, COMMISSION_PCT, DEPARTMENT_ID, EMAIL ) SELECT "EMPLOYEE_ID", "FIRST_NAME", "LAST_NAME", "JOB_ID", "MANAGER_ID", "HIRE_DATE", "SALARY", "COMMISSION_PCT", "DEPARTMENT_ID", "EMAIL" FROM "SYS_SQLLDR_X_EXT_EMPLOYEES_LDR" statements to cleanup objects created by previous statements: ------------------------------------------------------------------------ DROP TABLE "SYS_SQLLDR_X_EXT_EMPLOYEES_LDR" Run began on Sun Nov 01 16:43:38 2009 Run ended on Sun Nov 01 16:43:38 2009 Elapsed time was: 00:00:00.08 CPU time was: 00:00:00.03