Oracle 10g Rel 2.0 and LOG Errors + Direct Path Insert

Ok... We all know how the batch jobs for loading the data are written...

You know the usual

Cursor

Fetch / FOR CURSOR Loops

Exception Blocks etc.

Oracle 10g Rel 2 has some thing very powerful and efficient that can make the life much easier and improve the performance to a mind bogggling level!

Its the new feature for Logging errors while loading data from one table to another.

Here is an example:

INSERT INTO Target (Col1, Col2, Col3, ... Coln)
SELECT Col1, Col2, Col3, ... Coln FROM Source;

When we use this thing, and there is some problem while loading the data into the Target table, one cannot tell what column caused the problem so we are forced to use the Cursors and loops and handle records individually to trap the errors records.


With Oracle 10g Rel 2.0 we can use the new error loging clause for the Insert statement as:

INSERT INTO Target (Col1, Col2, Col3, ... Coln)
SELECT Col1, Col2, Col3, ... Coln FROM Source
LOG ERRORS REJECT LIMIT UNLIMITED;

Ok that is cool now...

But before we do this, we have got to tell Oracle that there is a table to be used for Error Handling, for this we have to execute a package as:


BEGIN
DBMS_ERRLOG.CREATE_ERROR_LOG('Target', 'ErrorLog_Target');
END;

This will tell Oracle to create a new table called "ErrorLog_Target" and use it to log errors for any DML on "Target" table where ever we specify the LOG ERRORS clause

After creating the ErrorLog_Target table, loading can take place and it will be much faster compared to CURSORS and LOOPS. We will see how!


There is another very useful Hint that we can use to improve the INSERT performance alot! This is called Driect-Path Inserting

Here is how its done:

INSERT /*+ APPEND */ INTO Target (Col1, Col2, Col3, ... Coln)
SELECT Col1, Col2, Col3, ... Coln FROM Source
LOG ERRORS REJECT LIMIT UNLIMITED;

Simply using the APPEND Hint will do the trick :-)

Here is my test based on a 500k!


SQL> SET SERVEROUTPUT ON SIZE 1000000;
SQL> SET TIMING ON;
SQL> ALTER SESSION SET SQL_TRACE = TRUE;
Session altered.
Elapsed: 00:00:00.57

SQL>
SQL> DECLARE
2 P_SOURCE VARCHAR2(200);
3 P_DATE DATE;
4
5 BEGIN
6 P_COUNTRY := 'AU';
7 P_DATE := '22-AUG-2008';
8
9 SP_INPUT_FULL ( P_SOURCE, P_DATE );
10 COMMIT;
11 END;
12 /

PL/SQL procedure successfully completed.
Elapsed: 00:10:20.45
SQL>

After I add the APPEND Hint
SQL> /
Elapsed: 00:02:05.35

:-)

ORA-24324 & ORA-24323 Oracle Configuration Assistant

I just installed Oracle 10g Rel 2 on my notebook, Installation was successful but at the time of Oracle Configuration Assistant I got the following Error

ORA-24324: service handle not initialized
ORA-24323: value not allowed
ORA-28547: connection to server failed, probable Oracle Net admin error

After checking/banging my head for hours I changed one of the parameters in the SQLNet.ORA and it worked :-)


Here is what I changed to solve the issue:

SQLNET.AUTHENTICATION_SERVICES = (NTS)

Modified to

SQLNET.AUTHENTICATION_SERVICES = (NONE)

:-)