Oracle7 Server Utilities

Error Handling

Row Errors

Row errors occur when a row is rejected for some reason, such as a violation of integrity constraints or invalid data. If a row error occurs, Import displays a warning message but continues processing the rest of the table.

Failed Integrity Constraints

A row error is generated if a row violates one of the integrity constraints in force on your system, including:

not null constraints

uniqueness constraints

primary key (not null and unique) constraints

referential integrity constraints

check constraints

See Chapter 5, "Maintaining Data Integrity", in the Oracle7 Server Application Developer's Guide and Chapter 7, "Data Integrity", in the Oracle7 Server Concepts manual for more information on integrity constraints.

Invalid Data

Row errors also occur when importing data into an existing table with a slightly shorter character-data column. The error is caused by data that is too long to fit into a new table's columns, by invalid data types, and by any other INSERT error.

Long Raw Data Errors

Some LONG RAW data columns may be too large to fit into Import's data buffer due to memory limitations. On Export, Oracle allows selecting such columns in sections. However, on Import, a contiguous region of memory is required. As a result, some columns that are successfully exported may not be importable until a sufficiently large contiguous region of memory is available. Such columns can produce row errors.

Setting Buffer Size for LONG Data

Tables with LONG data usually require large insert buffer sizes. If you are trying to import LONG data and Import fails with the message:

IMP-00020 column (size num) in export file too large for column

          buffer (size num)

then you can gradually increase the insert buffer size (for example, by 10,000 bytes at a time) up to 66,000 or greater.

Solving LONG Data Errors

If LONG or LONG RAW data on your system can become sufficiently large to create an import problem, then you need to make alternative plans for backup or file transfer. One suggestion is to put such columns in a separate table so that all other data can be exported and imported normally.

Object Errors

Object errors can occur for many reasons, some of which are described below. When an object error does occur, import of the current object is discontinued. Import then attempts to continue with the next object in the export file. If COMMIT=N has been specified, a rollback occurs before Import continues. Otherwise, a commit is issued. (See the description of COMMIT

for more information.)

Object Already Exists

If an object to be imported already exists in the database, then an object creation error occurs. What happens next depends on the setting of the IGNORE parameter.

If IGNORE=N (the default), the error is reported, and Import continues with the next object. The current object is not replaced. For tables, this behavior means that rows contained in the export file are not imported.

If IGNORE=Y, object creation errors are not reported. Although the object is still not replaced, if the object is a table, rows are imported into it. Note that only object creation errors will be ignored, all other errors (operating system, database, SQL, etc.) will be reported and processing may stop.

Warning: Specifying IGNORE=Y can cause duplicate rows to be entered into a table unless one or more columns of the table are specified with the UNIQUE integrity constraint. This could occur, for example, if Import were run twice.

Sequences

Before importing an export file into an existing database, sequences should be dropped. A sequence that is not dropped before the import will not be set to the value captured in the export dump file since Import does not drop and re-create a sequence that already exists. If the sequence already exists, then the export file's "create sequence" statement fails and the sequence is not imported.

Resource Errors

Resource limitations can cause objects to be skipped. When importing tables, for example, resource errors can occur as a result of internal problems, or when a resource such as memory has been exhausted.

If a resource error occurs while importing a row, Import stops processing the current table and skips to the next table. If you have specified COMMIT=Y, Import will commit the partial import of the current table. If not, a rollback of the current table will occur before Import continues. (See the description of COMMIT for information about the COMMIT parameter.)

Fatal Errors

When a fatal error occurs, Import terminates. For example, entering an invalid username/password combination or attempting to run Export or Import without having prepared the database by running the scripts CATEXP.SQL or CATALOG.SQL will cause a fatal error and Import/Export will terminate.