| Loading data by sql loader after truncating Master table failed [message #340583] |
Wed, 13 August 2008 06:14  |
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
|
Senior Member |
|
|
Hi,
I am using Oracle 10g on Linux, Loading data sql loader, i need to load data on table1 whose primary key is being used as foreign key in 10 more tables. Ten more tables are loaded without any problem but their foreign key are disabled as table1 still have old data.
We need to delete old data and then insert new records by using sql loader following is my content of controlfile of test1
LOAD DATA
TRUNCATE
INTO TABLE TABLE1
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
ID INTEGER EXTERNAL,
OPTION1 CHAR,
REF1 CHAR,
OTHER_REF CHAR,
TITLE "TRIM(:TITLE)",
FORENAME "TRIM(:FORENAME)",
SURNAME "TRIM(:SURNAME)",
JOINT_TITLE "TRIM(:JOINT_TITLE)",
JOINT_FORENAME "TRIM(:JOINT_FORENAME)",
JOINT_SURNAME "TRIM(:JOINT_SURNAME)",
)
Following is the error in loading table1
SQL*Loader-926: OCI error while executing delete/truncate (due to REPLACE/TRUNCATE keyword) for table Table1
ORA-02266: unique/primary keys in table referenced by enabled foreign keys
Do we have any other way, instead of sql loader
|
|
|
|
|
|
|
|
|
|
| Re: Loading data by sql loader after truncating Master table failed [message #340958 is a reply to message #340845] |
Fri, 15 August 2008 04:43  |
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
|
Senior Member |
|
|
Problem in log showing that field length of SURNAME field is more than table field size.
Following is the error in log file of sql loader
Record 21: Rejected - Error on table TABLE1, column
SURNAME.
ORA-12899: value too large for column SURNAME (actual: 65, maximum: 64)
and it is evident from my controlfile that i am using trim to discard any space then why it is giving an error.
I checked the bad file and count number of characters, they are 64 characters.
When i am inserting individual record from bad file by sql loader, it is loading
[Updated on: Fri, 15 August 2008 06:01] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Loading data by sql loader after truncating Master table failed [message #341016 is a reply to message #340994] |
Fri, 15 August 2008 09:19 |
 |
Michel Cadot
Messages: 68728
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
| Quote: | Please try to understand, what i am asking.
|
Please to think about our situation, we don't have your data, your file, your table, your database, none of these, so post all what can help us to reproduce what you say and help you.
Regards
Michel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
