Home » RDBMS Server » Server Utilities » problem with sqlldr
problem with sqlldr [message #253321] Mon, 23 July 2007 08:51 Go to next message
xleigaj
Messages: 7
Registered: July 2007
Junior Member
Hi,

Is here someone who can tell me how catch exceptions eg.(ORA-01722 invalid number) when using sqlldr? Is any possibility to do it? It would be very useful because i have some file and I use a sqlldr to load it into db(10g) and i should report exceptions in specific format. I think about something like "EXCEPTION" clause in PL/SQL.
Please help me.
Re: problem with sqlldr [message #253322 is a reply to message #253321] Mon, 23 July 2007 08:56 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can't but you have the bad file which is the same as the EXCEPTION clause except that it does not remove the messages.
You can aslo have a look at external tables.

Regards
Michel
Re: problem with sqlldr [message #253409 is a reply to message #253321] Mon, 23 July 2007 15:36 Go to previous messageGo to next message
xleigaj
Messages: 7
Registered: July 2007
Junior Member
ok. thanks. I think I find some solution.
Re: problem with sqlldr [message #253410 is a reply to message #253409] Mon, 23 July 2007 15:37 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
Post your solution for other guys.
Re: problem with sqlldr [message #253421 is a reply to message #253321] Mon, 23 July 2007 16:29 Go to previous messageGo to next message
xleigaj
Messages: 7
Registered: July 2007
Junior Member
I'm going to use triggers before insert. When sqlldr load data into table I can verify it by my triggers. When an exception shows up I can message it.
Re: problem with sqlldr [message #253458 is a reply to message #253421] Tue, 24 July 2007 01:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64153
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It will not work.
Values are checked before trigger is triggered:
SQL> create table t (col integer);

Table created.

SQL> create or replace trigger trg before insert on t for each row
  2  begin
  3    dbms_output.put_line('in trigger');
  4  end;
  5  /

Trigger created.

SQL> insert into t values(0);
in trigger

1 row created.

SQL> insert into t values ('a');
insert into t values ('a')
                      *
ERROR at line 1:
ORA-01722: invalid number

Regards
Michel
Re: problem with sqlldr [message #253489 is a reply to message #253458] Tue, 24 July 2007 02:09 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
As you use 10g, I guess that use of an external table (as Michel already suggested) might be a solution for you.
Re: problem with sqlldr [message #253501 is a reply to message #253321] Tue, 24 July 2007 02:31 Go to previous message
xleigaj
Messages: 7
Registered: July 2007
Junior Member
OK. I'll think about it.
Previous Topic: Problem with special character........
Next Topic: sql*Loader
Goto Forum:
  


Current Time: Sun Dec 11 02:40:00 CST 2016

Total time taken to generate the page: 0.10479 seconds