Sqlldr Official Demos
sqlldr demo files(the content of case2 is binary, so that cannot post here.)
Table of Contents
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