Home » RDBMS Server » Server Utilities » LOADER. Get all errors at once (11g. Unix)
LOADER. Get all errors at once [message #611124] Thu, 27 March 2014 18:26 Go to next message
rmon
Messages: 2
Registered: March 2014
Location: Madrid relaxing cup...
Junior Member
Hi all.

I want to load data from text file into the following table:

Create table employee(
idemp NUMBER(6,0),
name VARCHAR2(150),
salary NUMBER(6,0));

I want to get a log file with all errors by row read from the file, using only one call to SQL Loader or one execution query on external table.

When I try to load this source record with wrong values for idemp and salary columns:

AAAAAA|Jhon Williams|bbbb

the loader only write the first error (idemp bad value) but not evaluate the salary value. Is possible to detect all errors by row with only one execution?


Thanks in advance.
Re: LOADER. Get all errors at once [message #611125 is a reply to message #611124] Thu, 27 March 2014 18:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

It can be done but it is non-trivial.
Please Read The Fine Manual on ERROR LOGGING
http://docs.oracle.com/cd/E16655_01/server.121/e17209/statements_9014.htm#SQLRF55098
Re: LOADER. Get all errors at once [message #611221 is a reply to message #611125] Sun, 30 March 2014 18:55 Go to previous messageGo to next message
rmon
Messages: 2
Registered: March 2014
Location: Madrid relaxing cup...
Junior Member
Hi.

Thanks for the previous links. I have tried to apply ERROR LOGGING but I could not get my purpose, which is to detect all errors for each row at once.

I have created three tables:

CREATE TABLE DUSCALOAN.CENTRAL_AGREEMENTS_AUX
( AGREEMENT_CODE VARCHAR2(12 BYTE),
AGREEMENT_DS VARCHAR2(500 BYTE),
COUNTRY_CODE VARCHAR2(4 BYTE));

SQL> SELECT * FROM DUSCALOAN.CENTRAL_AGREEMENTS_AUX;

AGREEMENT_CODE AGREEMENT_DS COUNTRY_CODE
------------------------------ ------------------------------ ------------------------------
1 AGG1 1
2 AGG2 2
AGREEMENT AGG3 AAAA


CREATE TABLE DUSCALOAN.CENTRAL_AGREEMENTS
( AGREEMENT_CODE NUMBER(12,0),
AGREEMENT_DS VARCHAR2(500 BYTE),
COUNTRY_CODE NUMBER(4,0));

EXECUTE DBMS_ERRLOG.CREATE_ERROR_LOG ('DUSCALOAN.CENTRAL_AGREEMENTS', 'ERR$_CENTRAL_AGREEMENTS', 'DUSCALOAN', 'CPVLOAXXDO', TRUE);

After, we insert the content of CENTRAL_AGREEMENTS_AUX into CENTRAL_AGREEMENTS

SQL> INSERT INTO central_agreements (SELECT * FROM central_agreements_aux)
LOG ERRORS INTO err$_central_agreements REJECT LIMIT UNLIMITED; 2

2 rows created.

And only one error is logged, although there are two wrong values in the third row (AGREEMENT and AAAA)

SQL> SELECT ora_err_number$, ora_err_mesg$, agreement_code, agreement_ds, country_code FROM err$_central_agreements;

ORA_ERR_NUMBER$ ORA_ERR_MESG$ AGREEMENT_CODE AGREEMENT_DS COUNTRY_CODE
--------------- ---------------------------------------- ------------------------------ ------------------------------ ------------
########## ORA-01722: invalid number AGREEMENT AGG3 AAAA

What can I do to detect the two invalid numbers at once?

Thanks.
Re: LOADER. Get all errors at once [message #611226 is a reply to message #611221] Mon, 31 March 2014 00:14 Go to previous message
Littlefoot
Messages: 21806
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
One option is to create additional table which would contain all VARCHAR2 columns large enough to accept all input values. How large is large enough? I don't know; maybe VARCHAR2(50), maybe (100), maybe (4000). Then, using SQL*Loader, load all data into that table - there shouldn't be any errors.

Now, write a procedure which would transfer data from that table into the "original" target table. It would contain two loops: one to fetch a row, another one to loop through row's columns. The first action would be INSERT, another ones would be UPDATE. Using exception handler, you'd be able to continue updating columns even though any of them fails.

Another option - which basically does the same - is to use input file as an external table. Once again, with all large enough VARCHAR2 columns. The procedure would still be the same.

I expect it to be a slow and painful process as you'll have to handle everything: match data types, convert characters to dates, etc.

I don't have any smarter idea, but someone else might. If so, he/she might save your life.
Previous Topic: IMPDP hangs on table data
Next Topic: Increasing parallel process in impdp in runtime
Goto Forum:
  


Current Time: Thu Mar 28 13:33:54 CDT 2024