SQL*Loader Case Studies

SQL*Loader features are illustrated in a variety of case studies. The case studies are based upon the Oracle demonstration database tables, emp and dept, owned by the user scott. (In some case studies, additional columns have been added.)The case studies are numbered 1 through 11, starting with the simplest scenario and progressing in complexity.

Note:

Files for use in the case studies are located in the $ORACLE_HOME/rdbms/demo directory. These files are installed when you install the Oracle Database 12c Examples (formerly Companion) media. See Table 7-1 for the names of the files.

The following is a summary of the case studies:

  • Case Study 1: Loading Variable-Length Data - Loads stream format records in which the fields are terminated by commas and may be enclosed by quotation marks. The data is found at the end of the control file.

  • Case Study 2: Loading Fixed-Format Fields - Loads data from a separate data file.

  • Case Study 3: Loading a Delimited, Free-Format File - Loads data from stream format records with delimited fields and sequence numbers. The data is found at the end of the control file.

  • Case Study 4: Loading Combined Physical Records - Combines multiple physical records into one logical record corresponding to one database row.

  • Case Study 5: Loading Data into Multiple Tables - Loads data into multiple tables in one run.

  • Case Study 6: Loading Data Using the Direct Path Load Method - Loads data using the direct path load method.

  • Case Study 7: Extracting Data from a Formatted Report - Extracts data from a formatted report.

  • Case Study 8: Loading Partitioned Tables - Loads partitioned tables.

  • Case Study 9: Loading LOBFILEs (CLOBs) - Adds a CLOB column called resume to the table emp, uses a FILLER field (res_file), and loads multiple LOBFILEs into the emp table.

  • Case Study 10: REF Fields and VARRAYs - Loads a customer table that has a primary key as its OID and stores order items in a VARRAY. Loads an order table that has a reference to the customer table and the order items in a VARRAY.

  • Case Study 11: Loading Data in the Unicode Character Set - Loads data in the Unicode character set, UTF16, in little-endian byte order. This case study uses character-length semantics.

Case Study Files

Generally, each case study is comprised of the following types of files:

  • Control files (for example, ulcase5.ctl)

  • Data files (for example, ulcase5.dat)

  • Setup files (for example, ulcase5.sql)

These files are installed when you install the Oracle Database 12c Examples (formerly Companion) media. They are installed in the $ORACLE_HOME/rdbms/demo directory.

If the sample data for the case study is contained within the control file, then there will be no .dat file for that case.

Case study 2 does not require any special set up, so there is no .sql script for that case. Case study 7 requires that you run both a starting (setup) script and an ending (cleanup) script.

Table 7-1 lists the files associated with each case.

Table 7-1 Case Studies and Their Related Files

Case .ctl .dat .sql

1

ulcase1.ctl

N/A

ulcase1.sql

2

ulcase2.ctl

ulcase2.dat

N/A

3

ulcase3.ctl

N/A

ulcase3.sql

4

ulcase4.ctl

ulcase4.dat

ulcase4.sql

5

ulcase5.ctl

ulcase5.dat

ulcase5.sql

6

ulcase6.ctl

ulcase6.dat

ulcase6.sql

7

ulcase7.ctl

ulcase7.dat

ulcase7s.sql

ulcase7e.sql

8

ulcase8.ctl

ulcase8.dat

ulcase8.sql

9

ulcase9.ctl

ulcase9.dat

ulcase9.sql

10

ulcase10.ctl

N/A

ulcase10.sql

11

ulcase11.ctl

ulcase11.dat

ulcase11.sql

Running the Case Studies

In general, you use the following steps to run the case studies (be sure you are in the $ORACLE_HOME/rdbms/demo directory, which is where the case study files are located):

  1. At the system prompt, type sqlplus and press Enter to start SQL*Plus. At the user-name prompt, enter scott. At the password prompt, enter tiger.

    The SQL prompt is displayed.

  2. At the SQL prompt, execute the SQL script for the case study. For example, to execute the SQL script for case study 1, enter the following: 
    SQL> @ulcase1

    This prepares and populates tables for the case study and then returns you to the system prompt.

  3. At the system prompt, start SQL*Loader and run the case study, as follows: 
    sqlldr USERID=scott CONTROL=ulcase1.ctl LOG=ulcase1.log

    Substitute the appropriate control file name and log file name for the CONTROL and LOG parameters and press Enter. When you are prompted for a password, type tiger and then press Enter.

Be sure to read the control file for each case study before you run it. The beginning of the control file contains information about what is being demonstrated in the case study and any other special information you need to know. For example, case study 6 requires that you add DIRECT=TRUE to the SQL*Loader command line.

Case Study Log Files

Log files for the case studies are not provided in the $ORACLE_HOME/rdbms/demo directory. This is because the log file for each case study is produced when you execute the case study, provided that you use the LOG parameter. If you do not want to produce a log file, then omit the LOG parameter from the command line.

Checking the Results of a Case Study

To check the results of running a case study, start SQL*Plus and perform a select operation from the table that was loaded in the case study. This is done, as follows:

  1. At the system prompt, type sqlplus and press Enter to start SQL*Plus. At the user-name prompt, enter scott. At the password prompt, enter tiger.

    The SQL prompt is displayed.

  2. At the SQL prompt, use the SELECT statement to select all rows from the table that the case study loaded. For example, if the table emp was loaded, then enter: 
    SQL> SELECT * FROM emp;

    The contents of each row in the emp table will be displayed.