Previous | Table of Contents | Next

Page 221

Chapter 10

Using SQL*LOADER

This chapter teaches you about the PL/SQL language used in applications discussed in the next several parts of the book. You'll also learn about SQL *LOADER, which is used to load data into the Oracle tables.n

Page 222

NOTE
Many of the figures in this chapter depict files that can be executed. However, they are not intended for practice execution. It can be a complex task to load tables that currently contain data and have table constraints. I felt it was best to place the load files in figures rather than in executable files to prevent using them for practice. I feel it would be best to read the entire chapter before practicing the various concepts.

In most cases, the control files in the figures can be executed if a replace load style option is added. However, this may disrupt your database. The exercises at the end of the chapter were intended to be used for your practice. An answer file that works is also included in the book. In addition, the Chapter also has an installment to load the Employee Information system tables. The purpose of this installment is to offer the reader additional practice using SQL*LOADER. Working files for this task are also contained in the book.

If you would like to execute the files contained in the figures, remember the database can be restored to its original shape using the original database load instructions or by following the steps in the review exercises.n

Understanding SQL*LOADER

SQL*LOADER is Oracle's product for reading text files and placing the data into Oracle tables. SQL*LOADER has the ability to read data that is formatted in a variety of methods. You can use the product to load fixed position or comma separated value files. The product offers the developer the ability to load multiple Oracle tables during the loading procedure. SQL*LOADER also enables you to use logic to control the records that can be loaded into the table. The condition expressions are based on the same type of conditional logic used in a where clause.

SQL*LOADER is Oracle's product for reading text files and placing the data into Oracle tables. SQL*LOADER can read data that is formatted in a variety of ways.

You can use SQL*LOADER to load fixed-position and variable-formatted records. SQL*LOADER can load multiple Oracle tables during the loading procedure.

SQL*LOADER enables you to use document logic that controls which records can be loaded into a table. The condition expressions are based on the same type of conditional logic used in a where clause.

SQL*LOADER offers the developer the ability to manipulate data fields before the data is added to the table. The product has the ability to combine several physical records into a logical record. SQL*LOADER can treat physical records as multiple logical records.

You also have the ability to manipulate data fields before the data is added to the table. SQL*LOADER can has the ability to combine several physical records into a logical record. It also can treat a physical record as multiple logical records and allows you to load multiple tables from the same load procedure.

Page 223

SQL*LOADER is the product to use when converting data from a non-relational database to an Oracle database.

SQL*LOADER is the product to use when you're converting data from a nonrelational database to an Oracle database. I use it for two purposes. For example, you can use it when you're converting a PC-based system to a new Oracle Forms system that you've developed. This is part of my conversion work.

SQL*LOADER is the product to use when converting data from a non-relational database to an Oracle database. For example, you can use it when you're converting a PC based system to a new Oracle Forms system. Or you can use SQL*LOADER to read external data into an existing system.

NOTE
I have developed a transformer at my place of employment. Each transformer manufacturer sends test results for our newly purchased transformers. Each month a clerk uses SQL*LOADER to read the results from a text file into an Oracle table. When you have a data source comprised of many different file types, it's easiest to turn the data into a text file and load it into the Oracle table by using SQL*LOADER. The manufacturer supplies test results with each shipment of their transformers. The results arrive in a variety of formats and file types.n

When you have so many different file types of files, we have found it's easiest to turn the data into a text file and load it into the Oracle table by using SQL*LOADER. It has worked well for the tasks for which we've used it.

How Do You Start Executing SQL*LOADER?

You can start executing SQL*LOADER two ways. The first method is to execute the statements from the operating system command line. This is the method that was used before the product was moved into Windows. It is still used with non-Windows operating systems in non-Windows environments such as UNIX. (More on this method will be described at the end of the chapter.)

The second method is to double-click the SQLLOAD icon or launch the SQL*LOADER executable file in the Oracle icon grouping. This icon is shown in Figure 10.1. It is shaped like a funnel with letters of the alphabet on top. It is displayed with a number of other icons for Oracle products.

If you cannot locate the icon, a search of the \orawin\bin directory on your PC or server can be performed to locate the executable that will start SQL*LOADER. The name of the executable is different depending upon the version of the database. The executable for Oracle7 is SQLLDR72.EXE or SQLLDR73.EXE. The executable for Oracle8 is SQLLDR80.EXE. Each version of the product operates the same.

Double-clicking the icon or executing the SQL*LOADER utility brings up will bring up the SQL*LOADER dialog box, as shown in Figure 10.2.

The dialog box contains two sets of fields. The top fields must be filled in. The bottom fields contained in the Optional frame do not have to be filled in. SQL*LOADER will provide the

Previous | Table of Contents | Next