| Check data before loading through SQL *Loader [message #124744] |
Tue, 21 June 2005 03:52  |
abc123
Messages: 24
Registered: March 2005
Location: sa
|
Junior Member |
|
|
Hi all,
I have a temp table which is loaded through SQL*Loader.This table is used by a procedure for inserting data into another table.
I get error of 0RA-01722 frequently during procdures execution.
I have decided to check for the error data through the control file itself.
I have few doubts about SQL Loader.
Does declaring column as INTERGER EXTERNAL take care of NULL values.
How to discard record in which character data appears for field declared as NUMBER.
Does a whole record gets discarded if one of the fields is misplaced in the datafile?
Control File is of following format:
LOAD DATA
APPEND INTO TABLE Temp
FIELDS TERMINATED BY "|" optionally enclosed by "'"
trailing nullcols
(
FILEDATE DATE 'DD/MM/YYYY',
ACC_NUM INTEGER EXTERNAL,
REC_TYPE ,
LOGO , (Data:Numeric Declared:VARCHAR)
CARD_NUM INTEGER EXTERNAL,
ACTION_DATE DATE 'DD/MM/YYYY',
EFFECTIVE_DATE DATE 'DD/MM/YYYY',
ACTION_AMOUNT , (Data:Numeric Declared:NUMBER)
ACTION_STORE , (Data:Numeric Declared:VARCHAR)
ACTION_AUTH_NUM ,
ACTION_SKU_NUM ,
ACTION_CASE_NUM )
What changes do I need to make in this file regarding above questions?
|
|
|
|
| Re: Check data before loading through SQL *Loader [message #124961 is a reply to message #124744] |
Wed, 22 June 2005 09:32  |
smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida
|
Senior Member |
|
|
Refer to my other answer to your other post on most of your questions. You may want to read the entire chapter that I referenced.
And yes, if one field is "rejected" or has a problem, then the entire record is rejected and written to the bad file. But it depends on what you mean by "missplaced".
If you have two date fields in a row, for example, if the order of the two is reversed in the data file then they will both load just fine. Oracle can't know about the content or meaning of the data; all it checks is the data type.
But, this is why you want to make your data types in your database tables as restrictive as possible. If you make everything a char or varchar then you can load pretty much anything in there. But if you make fields number or date where appropriate, then oracle can use those "stronger" types to help in loading and validation and other issues.
Likewise with making fields not null if they should never be null.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
