Previous | Table of Contents | Next

Page 228

Using the Log File

The most important of the three output files is the log file, which contains the results of the load procedure. You should always review this file after executing a load procedure. Listing 10.1 contains an example of a typical log file.

The file informs the developer whether the load procedure was successful. The log file has several parts. The first part contains the names of the various files used in the procedure: control, data, discard, and bad.

The second part lists the values of the various load options. It tells you if the procedure is to load all the records from the data file. It lists the number of records the program was to skip, and how many errors the procedure can encounter before it terminates. Finally, the section lists the bind array value, and the load path.

The next part lists the load style option. This is discussed at length in the next section on control files. Following this option is a listing of all the table data fields that were to be populated. Also listed are the expected data type and format of the load data. (Additional information is also contained in the Defining a control file section.)

The fourth section is important because it displays an error message for any record that could not be loaded. Listing 10.1 is an example of a log file that has error messages.

The error message tells you the problem: the section tells you that eight records failed the load procedures because of formatting errors. The error message tells you the problem. The associated record number tells you the row number of the record in the data file. It helps the developer to identify the record to be fixed.

Listing 10.1 L_10_01.TXT—The Log File Holds the Run Statistics for the Load Procedure Run Statistics; These Tell You the Results of the Procedure

SQL*LOADER: Release 7.1.4.0.2 - Production on Thu May 15 21:44:24 1997
Copyright  Oracle Corporation 1979, 1994.  All rights reserved.
Control File:   C:\BOOK\INSTALL\TOOL1.DAT
Data File:      C:\BOOK\INSTALL\TOOL1.DAT
  Bad File:     C:\BOOK\INSTALL\TOOL1.BAD
 Discard File: C:\BOOK\INSTALL\TOOL1.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 TOOLS, loaded from every logical record.
Insert option in effect for this table: INSERT
  Column Name                  Position   Len  Term Encl Datatype
-----------------------------------------------------------------------------
FK_PAYROLL_NUMBER                   FIRST     *   ,  O(") CHARACTER
PURCHASE_DATE                        NEXT     *   ,  O(") DATE dd-mon-yy
PAYROLL_DEDUCT                       NEXT     *   ,  O(") CHARACTER

Page 229



TOOL_NAME                            NEXT     *   ,  O(") CHARACTER
TOOL_COST                            NEXT     *   ,  O(") CHARACTER
PAYMENT_AMOUNT                       NEXT     *   ,  O(") CHARACTER
LAST_PAYMENT_AMOUNT                  NEXT     *   ,  O(") CHARACTER
FIRST_PAYMENT_DATE                   NEXT     *   ,  O(") DATE dd-mon-yy
LAST_PAYMENT_DATE                    NEXT     *   ,  O(") DATE dd-mon-yy
Record 1: Rejected - Error on table TOOLS, column PURCHASE_DATE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table TOOLS, column PURCHASE_DATE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table TOOLS, column PURCHASE_DATE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table TOOLS, column PURCHASE_DATE.
No terminator found after TERMINATED and ENCLOSED field
Record 5: Rejected - Error on table TOOLS, column TOOL_NAME.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 6: Rejected - Error on table TOOLS, column LAST_PAYMENT_AMOUNT.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 7: Rejected - Error on table TOOLS, column PAYROLL_DEDUCT.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 8: Rejected - Error on table TOOLS, column PURCHASE_DATE.
Column not found before end of logical record (use TRAILING NULLCOLS)
Table TOOLS:
 34 Rows successfully loaded.
  8 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:                  63180 bytes(27 rows)
Space allocated for memory besides bind array:   107167 bytes
Total logical records skipped:          0
Total logical records read:            42
Total logical records rejected:         8
Total logical records discarded:        0
Run began on Thu May 15 21:44:24 1997
Run ended on Thu May 15 21:44:33 1997
Elapsed time was:     00:00:08.68


CPU time was:         00:00:00.00     (May not include ORACLE CPU time)

Following the error part is the run part. I always look at this part when I run a load procedure. This part, along with the error part, tells you the results of the procedure and identifies any problems that occurred.

These statistics tell you how many rows were loaded into the table, how many were rejected because of a format or database error, how many were discarded due to control file logic, and how many were not loaded because all of the fields in the logical record were null.

This section along with the error section tells you the results of the procedure and identifies any problems that occurred.

The remaining parts offer statistics on the number of logical records that were read, the number of records skipped, the number discarded, and the number rejected by the database. They also show the time the procedure was executed and the time it was completed.

Page 230

NOTE
I mentioned earlier in this section that I always review the log file after I load data. I do this because the status screen doesn't list errors unless the procedure is terminated. The log file holds all errors even if the application is not terminated.

As you saw in Listing 10.1, there were errors for eight records. They were rejected and placed in the bad file. Yet, the status screen doesn't give you a clue that errors occurred. This is why it shows the importance of viewing the log file.n

Using the Bad File

The records rejected by Oracle during the load procedure are placed in a file with an extension of .BAD. The records that exist in this file will have the same format as the records Oracle found in the load data file. You can look at this file to determine where to correct the records.

Following are some of the common errors you may encounter when loading data that causes records to be rejected.

The bad file is a good useful tool to identify problems with the data. After you view the error message in the log file, you may have to view the record to find and fix the error. The easiest way to do this is to look at the record in the bad file to view the incorrect data.

There's less data to look through in the bad file than in the data file. In addition, each record in the bad file has an error. Listing 10.2 shows the bad file for the tools load procedure. Notice that we have been discussing. You might notice that the bad file looks the same as the data file except that it has fewer records.

Listing 10.2 L_10_02.TXT—The Contents of the Bad File for Tools Loading Procedure Bad File

25,01-OCT-22,Y,Pliers,"25",10,"5",01-SEP-22,"01-OCT-22"
25,01-FEB-23,N,Vice Grips,"10",10,,,
35,04-JUN-80,Y,3/4" Wrench,"4",2,"2",01-JUL-80,"15-JUL-80"
35,06-NOV-82,Y,Tool Chest,"16.75",6,"4.75",01-DEC-80,"01-JAN-81"
35,24-APR-81,N,Knife,"7.95",7.95,,,

Previous | Table of Contents | Next