It goes without saying that a database is useless without data. This chapter focuses on two aspects of Personal Oracle7: saving existing data so that no data loss occurs and loading data from external sources into a Personal Oracle7 database. To save and restore Personal Oracle7 data, Oracle provides the Export and Import programs. The Export utility writes to a binary file, and the Import utility reads a file produced by the Export utility. As an Oracle application developer, you'll want to be proficient in using both programs.
In addition to Export and Import, Oracle supplies a utility program, SQL*Loader, that loads records from flat files into one or more Oracle tables. In this chapter I demonstrate how SQL*Loader loads fixed and variable length records into a Personal Oracle database.
As I mentioned in Chapter 3, "Personal Oracle7 for Windows95: Database Administration Tools," these three programs--;Export, Import, and SQL*Loader--;are no longer Windows-based programs in Personal Oracle7 for Windows 95. Instead, you must run them in an MS-DOS window. However, the full functionality of each program is still provided in the Windows 95 version. As an alternative to using the Export program, you can export a project using the Navigator.
Export and Import work in conjunction with one another to perform the following tasks:
Whenever you use Export or Import, you must connect to the Oracle database by supplying a valid Oracle username and password. Export offers three modes for saving Oracle data and structures:
The Export program icon is part of the Personal Oracle7 program group. When you double-click the Export icon, a dialog box prompts you for the Oracle username and password. To use Export (and Import), the Personal Oracle7 database must be available and running. (The examples in this chapter use the username and password from the repair store example--;FRAYED_WIRES and HELMHOLTZ.)
After you've successfully connected to the Oracle database, Export provides three principal choices for export mode. The first step is to designate the tables to be exported. On the Database Exporter screen, a section labeled Export These Objects contains the following check boxes:
Unless otherwise indicated, leave these options checked (the default) for the examples in this chapter.
The default name for the export file is the name that was used in the previous export session. You can change the name to 4TABLE.DMP. Click the Tables radio button. Click the Specify button to designate the tables you want to export.
NoteBy default, the export file has an extension of .DMP. Unfortunately, other programs sometimes produce diagnostic or dump files with the same extension. Avoid confusion--;be sure to name your Oracle export files with meaningful names and store them in an appropriate directory where you won't mistake them for dump files.
When you press the Specify button, the Export Objects dialog box displays the tables owned by the user you've used to connect to Oracle--;FRAYED_WIRES. You can export any table(s) that appears on this list. (See Figure 24.1.)
To select a table for export, click the table in the list of Remaining Tables. (Click the Export All button to export all the tables at once.) The first time that you select a table in the list of Remaining Tables, the Export button will be enabled. (See Figure 24.2.) Click the Export button to move the selected table to the Selected Tables list. After you've selected the tables that you want to export, click the OK button to return to the Database Exporter screen.
At this point, you can begin the export process by clicking the Export button. The Export Status window will appear; a box on this screen displays the progress of the database export. (See Figure 24.3.) When all of the tables have been exported, Export displays the following message:
Export terminated successfully without warnings.
In addition, the Close button on the Export Status window will be enabled; click it to return to the Database Exporter screen.
If you want to save the contents of the Export Status window, you have two choices. Your first choice is to copy the contents of the box and then paste them to an editor. As an alternative, you can specify an Export Log file by performing the following steps:
The second export mode is to export all objects owned by a designated Oracle user. To start from the beginning, click the Export icon and provide a valid Oracle username and password to connect to the Personal Oracle7 database. Once again, use FRAYED_WIRES and HELMHOLTZ, respectively, for the Oracle username and password. In the To Be Exported section of the screen, click the Owners radio button. As in other cases, the word owners is synonymous with Oracle user and Oracle account. By default, the owner will be the username that you used when you connected to Personal Oracle7. (See Figure 24.5.)
Simply click the Export button if you want to export all database objects owned only by the Oracle username that you used to connect to the database. However, if your Oracle account has been granted the DBA role, you can also export all objects owned by another Oracle user. In the current example, the Oracle user FRAYED_WIRES has been granted the DBA role. To designate a different Oracle user, click the Specify button.
The Export program will display an Export Objects screen that contains two lists: Selected Owners and Remaining Owners. Selected Owners displays a list of the Oracle owners whose objects should be exported--;yes, you can specify more than one Oracle account to export. Four other buttons are also visible--;Export, Export All, Don't Export, and Export None. (See Figure 24.6.)
You can highlight entries in the Remaining Owners list with a mouse click, and you can export each highlighted owner by clicking the Export button. When you click Export, the owner moves from the Remaining Owners list to the Selected Owners list. (See Figure 24.7.) You can export all owners by clicking the Export All button.
If you want to prevent a selected owner from being exported, highlight the owner in the Selected Owners list and click the Don't Export button. If you want to deselect all owners from being exported, click the Export None button. Click the OK button to return to the Database Exporter screen. Start the export process by clicking the Export button.
The final export option is the full export. This option exports all database users and their objects. A full export is the most thorough method of exporting an Oracle database, but this method is also the most time-consuming.
A user needs two system privileges to perform a full export--;SELECT ANY TABLE and BACKUP ANY TABLE. By default, Oracle provides a role named EXP_FULL_DATABASE that has been granted these two privileges. Therefore, grant the EXP_FULL_DATABASE role to any user that needs to perform a full export.
To perform a full export, click the Export icon and provide a valid Oracle username and password to connect to the Personal Oracle7 database. Again, use FRAYED_WIRES and HELMHOLTZ, respectively, for Oracle username and password. In the To Be Exported section of the screen, click the Full radio button. (See Figure 24.8.) You don't have to specify any other options. When you press the Export button, the Export Status window appears and the full export begins. When the full export is completed, you can close the Export Status window.
When you run the Export program in Windows 95, you must first bring up an MS-DOS window. You have two choices for running the program:
Here's how to use the first option. To invoke the program, type exp at the DOS prompt and press Enter. First, the Export program will prompt you for the Oracle username. (See Figure 24.9.)
Next, Export prompts you for the password of the Oracle username that you entered. Type in the password and press Enter. Because you haven't specified a connect string, the Export program assumes that you want to connect to a local Oracle database. Next, the program prompts you for the name of the export file--;the default name is EXPDAT.DMP.
After you specify the name of the export file, the export program asks you for a number or letter code that corresponds to the type of export:
For example, if you enter T as shown in Figure 24.10, the program asks you if you want to export table data (default is yes) and compress extents (default is yes). Finally, you will be asked to specify each table you want to export. When you have finished exporting tables, simply press Enter and the Export program will terminate.
You also have the option of specifying the export parameters on the command line. To see the syntax for using command-line arguments, type exp help=Y. (See Figure 24.11.) ***24DPO11*
For example, to export the Repair_Header table, you would type exp userid=frayed_wires/helmholtz file=rheader.dmp tables=(repair_header).
The Import program, in concert with the Export program, offers a method for loading Oracle database objects--;tables, views, and others--;and table data into a Personal Oracle7 database. Import relies on the file created by Export--;a DMP file.
Import has three primary modes of operation.
To invoke the Import program, double-click the Import icon in the Personal Oracle7 program group. A dialog box prompts you for the Oracle username and password. Remember, the Personal Oracle7 database must be available and running before you can use the Import utility. Again, use the username and password from the repair store example--;FRAYED_WIRES and HELMHOLTZ.
The next section explains how to import a set of tables.
After you've established a connection to Oracle via Import, enter the name of the export file in the Import File text box in the Database Importer screen. Select Tables for the Import Mode. (See Figure 24.12.)
To indicate which tables you want to import, click the Specify button. The next dialog box displays the tables contained in the export file, that is, the tables you can import. (See Figure 24.13.)
To indicate that a table should be imported, click the table in the list of Remaining Tables. (See Figure 24.14.) You can import all the tables by clicking the Import All button. When you click a table in the list of Remaining Tables, the Import button is enabled. Click the Import button to move the highlighted table to the Tables to Import list. After selecting all the tables that you want to import, click OK to return to the Database Importer screen.
Begin the import by clicking the Import button. A box on the Import Status window displays the progress of the database import. (See Figure 24.15.) When all the tables have been imported, Import displays the following message:
Import terminated successfully without warnings.
Also, the Close button on the Import Status window will be enabled; click it to return to the Database Importer screen.
The Database Importer screen contains a check box labeled Ignore Creation Errors. The default setting for this option is unchecked. If a table is imported into an Oracle account and the table already exists in that Oracle account, Import will not import the table and will display an error to the user. (See Figure 24.16.) If you enable the Ignore Creation Errors option, Import attempts to add the rows from the export file into the existing table. Oracle will enforce any constraints defined for the table or its columns.
Figure 24.17 illustrates how the table's rows can be imported once Ignore Creation Errors is enabled.
The second way to import database objects is by owner. Each object referenced in an export file is associated with an owner. You can import all Oracle objects owned by a specified Oracle user that are contained in an export file. For example, assume that Jean Smith, manager of the Frayed Wires electronics repair store, has an Oracle account with DBA privileges. As an experiment, she decides that she wants to import some tables that were exported from the FRAYED_WIRES Oracle account. Jean invokes Import and connects to her Oracle account in the Personal Oracle database. She then clicks the Users button to indicate the Import Mode and enters the name of the export file. (See Figure 24.18.)
Next, Jean clicks the Specify button to identify the user whose Oracle objects will be imported into her Oracle account.
Import displays an Import Users screen that contains two drop-down lists: Import this User and To This User. Import this User displays a list of the Oracle owners with objects in the export file. To This User displays a list of all Oracle accounts that exist in the Personal Oracle database. (See Figure 24.19.)
Jean clicks FRAYED_WIRES in the Import this User list and selects her own account--;JSMITH--;in the To This User list. She then clicks the OK button, and the Database Importer screen has the input focus. The Users to Import list box shows a single line: FROM FRAYED_WIRES TO JSMITH. (See Figure 24.20.)
Jean starts the import by clicking the Import button. The Import Status window appears, and a box displays the progress of the database import. When all of the tables have been imported, Import displays the following message:
Import terminated successfully without warnings.
The Close button on the Import Status window will be enabled; Jean clicks it to return to the Database Importer screen.
The third operational mode offered by Import is the full import. If you select this mode, the Import program attempts to import all database objects contained in the export file. I don't recommend using this option unless you are trying to reconstruct a database from scratch. I'll discuss this topic further in Chapter 25, "Database Backup and Recovery." *
CautionDo not import objects owned by the SYS account. The SYS account owns all of the Oracle7 data dictionary tables. By adding rows to existing data dictionary tables, you might inadvertently corrupt the Oracle data dictionary with potentially disastrous results. The only exception to this rule is the AUD$ table, which is optionally used to store Oracle7 auditing data.
When you run the Import program in Windows 95, you must first bring up an MS-DOS window. You have two choices for running the program:
To use the first option, type imp at the DOS prompt and press Enter to invoke the Import program. Import prompts you for the Oracle username. (See Figure 24.21.)
At the next prompt type in the password for the Oracle username and press Enter. Because you haven't specified a connect string, the Import program assumes that you want to connect to a local Oracle database. Next, the program prompts you for the name of the import file--;by default, it is EXPDAT.DMP.
After you specify the name of the import file, the Import program asks you for the buffer size--;the default size is 30720, which should be fine for most Personal Oracle7 databases. Figure 24.22 shows the remaining prompts.
Next, the Import program asks if you want to list the contents of the import file only--;the default answer is no.
The Import program then asks if it should ignore create errors due to object existence--;the default is no. However, if you want to import rows into a table that already exists, you should answer yes. This setting prevents the Import program from trying to create the table into which the rows will be stored and, thereby, avoids generating an error message.
The Import program then prompts you to determine if grants should be imported. If you are importing a table from a different database where the table has been granted to users that don't exist in the Personal Oracle7 database, you should probably answer no. However, if you don't, the Import program might display a series of innocuous error messages, indicating that the privilege cannot be granted because the user doesn't exist.
Next, the Import program asks if the table data should be imported. The default answer is yes. However, if you want to import only the table structures but not their contents, specify no.
Then, the Import program asks if the entire file should be imported--;the default answer is no. If you answer no, the Import program asks you for the username to be used in specifying the tables to import. The Import program then prompts you for each table to be imported. When you are finished entering the tables to be imported, type a period (.) to begin the import. ***24DPO22***
You also have the option of specifying the import parameters on the command line. To see the syntax for using command-line arguments, type imp help=Y in the MS-DOS window. (See Figure 24.23.)
For example, to import the Repair_Item table, you would type imp userid=frayed_wires/helmholtz file=frydwire.dmp tables=(repair_item) ignore=Y.
SQL*Loader is an Oracle utility program that loads data into Oracle tables from external files. SQL*Loader is a very flexible utility; it offers many options and settings to deal with a wide variety of data-loading situations. SQL*Loader uses the following files during the loading process:
Figure 24.24 illustrates how these files function with SQL*Loader.
The control file contains a description of the records in the data file, the Oracle table to be loaded, special processing options to be used, and other SQL*Loader settings. Also, the name of the control file, unless indicated otherwise, is used for naming the discard, bad, and log files. For instance, if the control file is named itemmast.ctl, the other filenames default to the following:
Discard file itemmast.dsc
Bad file itemmast.bad
Log file itemmast.log
The data file is the external file that SQL*Loader reads. This file may consist of fixed-length records, variable-length records, and records whose data fields are delimited by specific characters. The data file is referred to as an external file because it is external to the Oracle database.
When SQL*Loader is unable to process a record, it places the record in the bad file. For instance, assume that the control file specifies that the first six characters of each record is a date in the format MMDDYY. SQL*Loader will reject records if the first two characters are not in the set 01, 02, 03,. . . , 12. When SQL*Loader finishes, you can review, correct, and reload the rejected records in the bad file, or they can remain unloaded.
You can optionally configure the SQL*Loader control file to discard records that do not meet specific criteria into a discard file. For example, to load only the records from the ITEMMAST file whose creation date is greater than or equal to 01-JAN-84, you can add a line to the control file to enforce this constraint. Any records whose creation date is earlier than 01-JAN-84 are placed in the discard file.
SQL*Loader records a summary of its activity in the log file. This summary includes the number of successfully loaded records, the number of discarded records, the number of rejected records, and the SQL error associated with each rejected record.
As with the other Personal Oracle database administration tools, SQL*Loader is found in the Personal Oracle7 program group. To invoke SQL*Loader, double-click the SQL*Loader program icon. To use SQL*Loader, the Oracle database be running and available.
The syntax for the SQL*Loader control file can get fairly complex, depending on the data-loading requirements. This chapter deals with two common examples: loading fixed-format records and loading free-format, delimited records. For the sake of clarity, I have omitted some of the details that you would see in a real-world situation.
Once you've invoked SQL*Loader, a screen containing several fields will appear. I have created the username VILLAGE_WIDGET and password VILLAGE for this example.
In this scenario your company, Village Widget Corporation, is rightsizing its computerized manufacturing system. Your task is to load and convert the legacy data to the new Oracle tables.
As a first step, you select the ITEMMAST file, which contains basic information
about each item that Village Widget has purchased. The fixed-field records in the
ITEMMAST file will be loaded into the Item_Master Oracle table. Table 24.1
shows the record structure for the ITEMMAST file.
| Table 24.1. Record structure of the ITEMMAST file. | |||
| Field Number | Field Name | Datatype | Length |
| 1 | ITEM_NO | Alphanumeric | 60 |
| 2 | DESCRIP | Text | 80 |
| 3 | VENDID | Alphanumeric | 6 |
| 4 | LSTPRICE | Numeric | 8 |
| 5 | CREADATE | Numeric | 6 |
| 6 | CREAUSER | Alphanumeric | 6 |
| 7 | MODDATE | Numeric | 6 |
| 8 | MODUSER | Alphanumeric | 6 |
Table 24.2 shows the record structure for the associated VENDMAST file.
| Table 24.2. Record structure of the VENDMAST file. | |||
| Field Number | Field Name | Datatype | Length |
| 1 | VEND_NO | Alphanumeric | 6 |
| 2 | NAME | Text | 80 |
The Item_Master and Vendor_Master tables are created with the following SQL statements:
CREATE TABLE ITEM_MASTER ( ITEM_NO VARCHAR2(12), DESCRIPTION VARCHAR2(2000), VENDOR_ID VARCHAR2(12), RECENT_PRICE DECIMAL(12,4), CREATED_DATE DATE NOT NULL, CREATED_BY VARCHAR2(32) NOT NULL, DATE_MODIFIED DATE, MODIFIED_BY VARCHAR2(32), PRIMARY KEY (ITEM_NO), FOREIGN KEY (VENDOR_ID) REFERENCES VENDOR_MASTER (VENDOR_ID)) CREATE TABLE VENDOR_MASTER ( VENDOR_ID VARCHAR2(12), VENDOR_NAME VARCHAR2(60) NOT NULL, PRIMARY KEY (VENDOR_ID))
Because you realize that the Item_Master table is dependent on the Vendor_Master table, you decide to load the Vendor_Master table first. If you tried to load Item_Master before loading the Vendor_Master, SQL*Loader would reject all of the Item_Master records because they violate a referential integrity constraint.
Using a text editor, you create the following control file for loading the VENDMAST file into the Vendor_Master table:
load data infile vendmast.dat into table VENDOR_MASTER (VENDOR_ID POSITION(01:06) CHAR, VENDOR_NAME POSITION(07:86) CHAR)
To run SQL*Loader, click the SQL*Loader program icon, which you can find in the Personal Oracle7 program group. SQL*Loader displays a window into which you enter the username, password, and the control file to be used for loading data. To load records from VENDMAST, enter the values shown in Figure 24.25 and click the Load button to start the loading process. SQL*Loader displays another window that shows the loading progress. When SQL*Loader has finished processing the data file, the Close button on this window will be enabled. Click the Close button to return to the SQL*Loader window.
After SQL*Loader has finished processing, the log file, vendmast.log, contains the following lines:
SQL*Loader: Release 7.1.4.0.2 - Production on Sat Jun 03 23:38:15 1995 Copyright Oracle Corporation 1979, 1994. All rights reserved. Control File: C:\BOOK\CHAP24\VENDMAST.CTL Data File: vendmast.dat Bad File: C:\BOOK\CHAP24\vendmast.bad Discard File: C:\BOOK\CHAP24\vendmast.dsc (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65024 bytes Continuation: none specified Path used: Conventional Table VENDOR_MASTER, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- VENDOR_ID 1:6 6 CHARACTER VENDOR_NAME 7:86 80 CHARACTER Table VENDOR_MASTER: 5 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 5760 bytes(64 rows) Space allocated for memory besides bind array: 46227 bytes Total logical records skipped: 0 Total logical records read: 5 Total logical records rejected: 0 Total logical records discarded: 0 Run began on Sat Jun 03 23:38:15 1995 Run ended on Sat Jun 03 23:38:19 1995 Elapsed time was: 00:00:04.23 CPU time was: 00:00:00.00 (May not include ORACLE CPU time)
Now that you've loaded the Vendor_Master table, you're ready to begin loading the Item_Master table. Construct a control file, itemmas1.ctl, for loading records from the ITEMMAST file.
load data infile itemmast.dat into table ITEM_MASTER (ITEM_NO POSITION(01:06) CHAR, DESCRIPTION POSITION(07:86) CHAR, VENDOR_ID POSITION(87:92) CHAR, RECENT_PRICE POSITION(93:100) DECIMAL EXTERNAL, CREATED_DATE POSITION(101:106) DATE, CREATED_BY POSITION(107:112) CHAR, DATE_MODIFIED POSITION(113:118) DATE, MODIFIED_BY POSITION(119:124) CHAR)
Click the SQL*Loader program icon. To load records from the ITEMMAST file, enter the following values:
When you click the Load button to start the loading process, SQL*Loader displays another window that shows the loading progress. When SQL*Loader has finished processing the data file, the Close button on the second window will be enabled. Click the Close button to return to the SQL*Loader window.
After SQL*Loader has finished processing, the log file, itemmast.log, contains the following lines:
SQL*Loader: Release 7.1.4.0.2 - Production on Mon Jun 05 23:13:19 1995 Copyright Oracle Corporation 1979, 1994. All rights reserved. Control File: C:\BOOK\CHAP24\ITEMMAS1.CTL Data File: itemmast.dat Bad File: C:\BOOK\CHAP24\itemmast.bad Discard File: C:\BOOK\CHAP24\itemmast.dsc (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65024 bytes Continuation: none specified Path used: Conventional Table ITEM_MASTER, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- ITEM_NO 1:6 6 CHARACTER DESCRIPTION 7:86 80 CHARACTER VENDOR_ID 87:92 6 CHARACTER RECENT_PRICE 93:100 8 CHARACTER CREATED_DATE 101:106 6 DATE DD-MON-YY CREATED_BY 107:112 6 CHARACTER DATE_MODIFIED 113:118 6 DATE DD-MON-YY MODIFIED_BY 119:124 6 CHARACTER Record 1: Rejected - Error on table ITEM_MASTER, column CREATED_DATE. ORA-01843: not a valid month Record 2: Rejected - Error on table ITEM_MASTER, column CREATED_DATE. ORA-01843: not a valid month Table ITEM_MASTER: 0 Rows successfully loaded. 2 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 8960 bytes(64 rows) Space allocated for memory besides bind array: 57016 bytes Total logical records skipped: 0 Total logical records read: 2 Total logical records rejected: 2 Total logical records discarded: 0 Run began on Mon Jun 05 23:13:19 1995 Run ended on Mon Jun 05 23:13:23 1995 Elapsed time was: 00:00:03.79 CPU time was: 00:00:00.00 (May not include ORACLE CPU time)
According to the contents of the log file, none of the records were loaded into the table.
If you look at the error messages in the log file, you see that Oracle returned an error code of [ms]1843, which indicates a problem with the processing of date values. SQL*Loader returned this error because the format of the date in CREATED_DATE and DATE_MODIFIED is MMDDYY; Oracle's default date format is DD-MON-YY. SQL*Loader has now created a bad file that contains the rejected records. For SQL*Loader to interpret the date fields correctly, you must add the date format within single quotes after the keyword DATE, as shown in the following corrected control file (itemmas2.ctl):
load data infile itemmast.dat into table ITEM_MASTER (ITEM_NO POSITION(01:06) CHAR, DESCRIPTION POSITION(07:86) CHAR, VENDOR_ID POSITION(87:92) CHAR, RECENT_PRICE POSITION(93:100) DECIMAL EXTERNAL, CREATED_DATE POSITION(101:106) DATE 'MMDDYY', CREATED_BY POSITION(107:112) CHAR, DATE_MODIFIED POSITION(113:118) DATE 'MMDDYY', MODIFIED_BY POSITION(119:124) CHAR)
Click the SQL*Loader program icon. To load records successfully from the ITEMMAST file (see Figure 24.26), enter the following values:
The itemmas2.ctl control file correctly converts the date column fields in the ITEMMAST file. When you click the Load button to start the loading process, SQL*Loader displays another window that shows the loading progress. When SQL*Loader has finished processing the data file, the Close button on the second window will be enabled. Click the Close button to return to the SQL*Loader window.
Now you can see that SQL*Loader has processed the records as intended.
SQL*Loader: Release 7.1.4.0.2 - Production on Mon Jun 05 23:14:00 1995 Copyright Oracle Corporation 1979, 1994. All rights reserved. Control File: C:\BOOK\CHAP24\ITEMMAS2.CTL Data File: itemmast.dat Bad File: C:\BOOK\CHAP24\itemmast.bad Discard File: C:\BOOK\CHAP24\itemmast.dsc (Allow all discards) Number to load: ALL Number to skip: 0 Errors allowed: 50 Bind array: 64 rows, maximum of 65024 bytes Continuation: none specified Path used: Conventional Table ITEM_MASTER, loaded from every logical record. Insert option in effect for this table: INSERT Column Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- ITEM_NO 1:6 6 CHARACTER DESCRIPTION 7:86 80 CHARACTER VENDOR_ID 87:92 6 CHARACTER RECENT_PRICE 93:100 8 CHARACTER CREATED_DATE 101:106 6 DATE MMDDYY CREATED_BY 107:112 6 CHARACTER DATE_MODIFIED 113:118 6 DATE MMDDYY MODIFIED_BY 119:124 6 CHARACTER Table ITEM_MASTER: 2 Rows successfully loaded. 0 Rows not loaded due to data errors. 0 Rows not loaded because all WHEN clauses were failed. 0 Rows not loaded because all fields were null. Space allocated for bind array: 8960 bytes(64 rows) Space allocated for memory besides bind array: 57524 bytes Total logical records skipped: 0 Total logical records read: 2 Total logical records rejected: 0 Total logical records discarded: 0 Run began on Mon Jun 05 23:14:00 1995 Run ended on Mon Jun 05 23:14:03 1995 Elapsed time was: 00:00:03.30 CPU time was: 00:00:00.00 (May not include ORACLE CPU time)
In addition to being able to specify the format of date values, you can also utilize Oracle built-in functions to modify a field value from the data file. For instance, if you want to modify the item description so that the first letter of each word is capitalized, you can apply the INITCAP function to the Description column.
load data infile itemmast.dat into table ITEM_MASTER (ITEM_NO POSITION(01:06) CHAR, DESCRIPTION POSITION(07:86) CHAR "INITCAP(:DESCRIPTION)", VENDOR_ID POSITION(87:92) CHAR, RECENT_PRICE POSITION(93:100) DECIMAL EXTERNAL, CREATED_DATE POSITION(101:106) DATE 'MMDDYY', CREATED_BY POSITION(107:112) CHAR, DATE_MODIFIED POSITION(113:118) DATE 'MMDDYY', MODIFIED_BY POSITION(119:124) CHAR)
You have successfully loaded the Item_Master and Vendor_Master tables. As it turns
out, one of the data files--;dealing with vendor quality--;that you need to
load was maintained in a separate system. Table 24.3 shows the structure of the VENDQUAL
file.
| Table 24.3. Record structure of the VENDQUAL file. | |||
| Field Number | Field Name | Datatype | Length |
| 1 | VENDID | Alphanumeric | 6 |
| 2 | VENDNAME | Text | 40 |
| 3 | QUALRATE | Alphanumeric | 2 |
| 4 | RATEDATE | Numeric | 8 |
| 5 | RATEUSER | Alphanumeric | 6 |
However, VENDQUAL has been unloaded to a file containing variable-length records with each field delimited by double quotes.
"G01001","GREBNITZ NUTRITIONAL PRODUCTS, INC.","A+","101889","GGOMEZ" "L02001","LOS ANGELES METALWORKING SYSTEMS, LTD.","B+","021291","RSMITH" "R00912","RADIO STEAMERS, INC.","B","040491","CJONES" "K01234","KENTUCKY FRIED WRENCHES, CO.","B-","080992","RSMITH" "H12431","HOLLERITH HAMMERS, LTD.","C+","091192","GGOMEZ"
Create a control file for loading the VENDQUAL file into the Vendor_Quality table.
load data infile 'vendqual.dat' into table VENDOR_QUALITY FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' (vendor_id, vendor_name, quality_rating, rating_date DATE(6) "MMDDYY", user_id)
The fourth line in the control file tells SQL*Loader how to parse each data record.
Click the SQL*Loader program icon to invoke SQL*Loader. Enter the username, password, and the control file to be used for loading data. To load records from VENDQUAL, enter the values shown in Figure 24.27 and click the Load button to start the loading process. SQL*Loader displays another window that shows the loading progress. When SQL*Loader has finished processing the data file, the Close button on the second window will be enabled. Click the Close button to return to the SQL*Loader window.
After SQL*Loader has finished processing, the log file, vendqual.log, contains the following lines:
SQL*Loader: Release 7.1.4.0.2 - Production on Sun Jun 04 00:17:45 1995
Copyright Oracle Corporation 1979, 1994. All rights reserved.
Control File: C:\BOOK\CHAP24\VENDQUAL.CTL
Data File: vendqual.dat
Bad File: C:\BOOK\CHAP24\vendqual.bad
Discard File: C:\BOOK\CHAP24\vendqual.dsc
(Allow all discards)
Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array: 64 rows, maximum of 65024 bytes
Continuation: none specified
Path used: Conventional
Table VENDOR_QUALITY, loaded from every logical record.
Insert option in effect for this table: INSERT
Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
VENDOR_ID FIRST * , O(") CHARACTER
VENDOR_NAME NEXT * , O(") CHARACTER
QUALITY_RATING NEXT * , O(") CHARACTER
RATING_DATE NEXT 6 , O(") DATE MMDDYY
USER_ID NEXT * , O(") CHARACTER
Table VENDOR_QUALITY:
5 Rows successfully loaded.
0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
Space allocated for bind array: 64976 bytes(62 rows)
Space allocated for memory besides bind array: 107565 bytes
Total logical records skipped: 0
Total logical records read: 5
Total logical records rejected: 0
Total logical records discarded: 0
Run began on Sun Jun 04 00:17:45 1995
Run ended on Sun Jun 04 00:17:48 1995
Elapsed time was: 00:00:03.73
CPU time was: 00:00:00.00 (May not include ORACLE CPU time)
As you discovered earlier, sometimes a legacy field has been used to store special values. This situation can occur when the legacy field stores its data as a string rather than as a more restrictive datatype such as an integer.
For example, suppose the LSTPRICE field in the ITEMMAST legacy data file had three special values:
When SQL*Loader tries to load a record that contains any of these three values, the record will be rejected and placed in the bad file because the RECENT_PRICE field is not numeric. Here are some methods for dealing with this situation:
load data
infile itemmast.dat
into table ITEM_MASTER
(ITEM_NO POSITION(01:06) CHAR,
DESCRIPTION POSITION(07:86) CHAR "INITCAP(:DESCRIPTION)",
VENDOR_ID POSITION(87:92) CHAR,
RECENT_PRICE POSITION(93:100) DECIMAL EXTERNAL
"DECODE(:RECENT_PRICE,'N/A',NULL,'UNK',NULL,
:RECENT_PRICE)",
CREATED_DATE POSITION(101:106) DATE 'MMDDYY',
CREATED_BY POSITION(107:112) CHAR,
DATE_MODIFIED POSITION(113:118) DATE 'MMDDYY',
MODIFIED_BY POSITION(119:124) CHAR)
In the preceding example, you loaded the tables so that the foreign key constraint of the Item_Master table wasn't violated. In other words, the tables were loaded in proper order so that the SQL*Loader wouldn't reject any records due to constraint violations. Suppose you need to reload the Vendor_Master table because you forgot to load a field. If you tried to reload the Vendor_Master table, the deletion of the vendor records would fail because the constraint would be violated.
Frankly, table constraints can often interfere with the task of loading many related tables. In fact, you might want to drop (or disable) table constraints until you have achieved the more fundamental goals of data loading. The suggestion to drop (or disable) constraints is based on a major assumption: that no one else is using the tables you are loading. A software developer should work with a separate copy of the tables during the data loading phase.
When you run SQL*Loader in Windows 95, you must first bring up an MS-DOS window. You can then invoke the program with command-line arguments that specify the Oracle username and password, control file, data file, and other optional arguments. For example, to load the VENDQUAL table in the previous example, you would type:
sqlldr userid=frayed_wires/helmholtz control=vendqual data=vendqual
If you don't specify a required argument, SQL*Loader will prompt you for the parameter. For example, if you don't specify the name of the control file, SQL*Loader will ask you for it. To see the syntax for using command-line arguments with SQL*Loader, simply type sqlldr. (See Figure 24.30.)
This chapter presents the following significant topics: