Oracle Loading Data from Flat Files Using EM

Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

The new Load Data wizard enhancements enable you to load data from external flat files into the database. It uses the table name and data file name that you specify, along with other information, to scan the data file and define a usable SQL*Loader control file. The wizard will create the control file for you. It then uses SQL*Loader to load the data.

Note: Not all control file functionality is supported in the Load Data wizard.

You can access the Load Data page from: Maintenance tabbed page | Move Row Data section

DML Error Logging Table

This feature (in Release 2) allows bulk DML operations to continue processing, when a DML error occurs, with the ability to log the errors in a DML error logging table.

DML error logging works with INSERT, UPDATE, MERGE, and DELETE statements.

To insert data with DML error logging:

1. Create an error logging table.

This can be automatically done by the DBMS_ERRLOG.CREATE_ERROR_LOG procedure. It creates an error logging table with all of the mandatory error description columns plus all of the columns from the named DML table.

DBMS_ERRLOG.CREATE_ERROR_LOG(<DML
table_name>[,<error_table_name>])

default logging table name is ERR$_ plus first 25 characters of table name You can create the error logging table manually using the normal DDL statements but it must contain the following mandatory columns:

ORA_ERR_NUMBER$ NUMBER
ORA_ERR_MESG$ VARCHAR2(2000)
ORA_ERR_ROWID$ ROWID
ORA_ERR_OPTYP$ VARCHAR2(2)
ORA_ERR_TAG$ VARCHAR2(2000)

2. Execute an INSERT statement and include an error logging clause.

LOG ERRORS [INTO <error_table>] [('<tag>')]
[REJECT LIMIT <limit>]

If you do not provide an error logging table name, the database logs to an error logging table with a default name.

You can also specify UNLIMITED for the REJECT LIMIT clause. The default reject limit is zero, which means that upon encountering the first error, the error is logged and the statement rolls back.

DBMS_ERRLOG.CREATE_ERROR_LOG('DW_EMPL')
INSERT INTO dw_empl
SELECT employee_id, first_name, last_name,
hire_date, salary, department_id
FROM employees
WHERE hire_date > sysdate - 7
LOG ERRORS ('daily_load') REJECT LIMIT 25

Asynchronous Commit

In Oracle 10.2 COMMITs can be optionally deferred. This eliminates the wait for an I/O to the redo log but the system must be able to tolerate loss of asynchronously committed transaction.

COMMIT [ WRITE [ IMMEDIATE|BATCH] [WAIT |
NOWAIT]

IMMEDIATE - specifies redo should be written immediately by LGWR process when transaction is committed (default)

BATCH - causes redo to be buffered to redo log

WAIT - specifies commit will not return until redo is persistent in online redo log (default)

NOWAIT - allows commit to return before redo is persistent in redo log

COMMIT; -- =IMMEDIATE WAIT
COMMIT WRITE; -- = COMMIT;
COMMIT WRITE IMMEDIATE;-- = COMMIT;
COMMIT WRITE IMMEDIATE WAIT; -- = COMMIT;
COMMIT WRITE BATCH; -- = BATCH WAIT
COMMIT WRITE BATCH NOWAIT; -- = BATCH NOWAIT

COMMIT_WRITE initialization parameter determines default value of COMMIT WRITE statement.

Can be modified using ALTER SESSION statement

ALTER SESSION SET COMMIT_WRITE = 'BATCH,NOWAIT'