Sqlldr

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.

SQL*LOADER基本のサンプル(共通)

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

sqlldr cn