Home » RDBMS Server » Server Utilities » Loading data by sql loader after truncating Master table failed (oracle 10g on linux 64 bit)
Loading data by sql loader after truncating Master table failed [message #340583] Wed, 13 August 2008 06:14 Go to next message
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 #340590 is a reply to message #340583] Wed, 13 August 2008 06:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Any way, you can't truncate a table with a foreign pointing to it. You must first disable the foreign keys (in addition to the primary key on the table).

Regards
Michel
Re: Loading data by sql loader after truncating Master table failed [message #340840 is a reply to message #340590] Thu, 14 August 2008 09:56 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
i have disabled all the foreign key pointing towards the table and sql loader is showing that 5000 logical records loaded but table is empty.
Re: Loading data by sql loader after truncating Master table failed [message #340845 is a reply to message #340583] Thu, 14 August 2008 10:15 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above


Quote:

sql loader is showing that 5000 logical records loaded but table is empty.


If you say so.

I can only hope that Sherlock Holmes is monitoring this thread & can assist solving this mystery when no real clues are offered.

You're On Your Own (YOYO)!
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 Go to previous messageGo to next message
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 #340968 is a reply to message #340958] Fri, 15 August 2008 06:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Database character set?
Data that is invalid?

Regards
Michel
Re: Loading data by sql loader after truncating Master table failed [message #340972 is a reply to message #340968] Fri, 15 August 2008 06:16 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
But individual record from bad file is being loaded by sql loader,does it has anything to do with record
Re: Loading data by sql loader after truncating Master table failed [message #340980 is a reply to message #340972] Fri, 15 August 2008 06:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But individual record from bad file is being loaded by sql loader

If it is loaded, it is NOT in bad file.
If it is in bad file, it is NOT loaded.

And don't try to think about what we ask, just post it if you want help.

Regards
Michel

[Updated on: Fri, 15 August 2008 06:58]

Report message to a moderator

Re: Loading data by sql loader after truncating Master table failed [message #340994 is a reply to message #340980] Fri, 15 August 2008 08:10 Go to previous messageGo to next message
MIFI
Messages: 256
Registered: February 2008
Location: U.K.
Senior Member
Please try to understand, what i am asking.
I repeat again that when i am inserting same record individually by sql loader, it is working fine.

Why more than one records are not being inserted by sql loader?
Re: Loading data by sql loader after truncating Master table failed [message #340996 is a reply to message #340994] Fri, 15 August 2008 08:17 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
TRIM only gets rid of blank spaces. It will not get rid of control characters or newlines.
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 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: can any help me out
Next Topic: Exporting whole database from oracle 9.0 to 9.2 (windows
Goto Forum:
  


Current Time: Sat Dec 03 18:34:15 CST 2016

Total time taken to generate the page: 0.12900 seconds