7.13 SQL*Loader Case Studies

To learn how you can use SQL*Loader features, you can run a variety of case studies that Oracle provides.

Parent topic: Understanding How to Use SQL*Loader

7.13.1 How to Access and Use the Oracle SQL*Loader Case Studies

Oracle provides 11 case studies that illustrate features of SQL*Loader

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 Examples (formerly Companion) media.

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.

Parent topic: SQL*Loader Case Studies

7.13.2 Case Study Files

Each of the SQL*Loader case study files has a set of files required to use that case study

Usage Notes

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 Examples (formerly Companion) media. They are installed in the directory $ORACLE_HOME/rdbms/demo.

If the example data for the case study is contained within the control file, then there is 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.

The following table 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

Parent topic: SQL*Loader Case Studies

7.13.3 Running the Case Studies

The typical steps for running SQL*Loader case studies is similar for all of the cases.

Be sure you are in the $ORACLE_HOME/rdbms/demo directory, which is where the case study files are located.

Also, 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.

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

    SQL> @ulcase1

    This command 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.

    For example, to run case 1, enter the following command:

    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.

Parent topic: SQL*Loader Case Studies

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

Parent topic: SQL*Loader Case Studies

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

  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 you load the table emp, then enter the following statement: 

    SQL> SELECT * FROM emp;

    The contents of each row in the emp table are displayed.

Parent topic: SQL*Loader Case Studies