| sql loader problem [message #145107] |
Mon, 31 October 2005 22:15  |
trahul4
Messages: 9
Registered: June 2005
|
Junior Member |
|
|
Hi,
Im facing a bit of problem while using sql loader to load data from a text file into my database tables.
Description :
I have a text file (test.txt)which has 2 columns say emp_id and name. I have created a table in my db named test which has again 2 columns emp_id and first_name . The text file is in fixed format , so i can write the control file keeping positions in mind.
My contorl file is something like this
load data
infile 'test.txt'
into table test
append
when emp_id !=''
(
EMP_ID position (01:05),
FIRST_NM position (06:12)
)
For the first time i get the file something like this
12345abcdefg . This will insert 12345 in emp _id and abcdefg in first_name field of my database.
Now, my problem here is the next time i get the text file, i get the same updated records. I mean the emp_id may be same but the first_nm changes.
If i get something like this
12345pqrstuv
then the first name should be updated against emp id in my table .
How can i acheive this in sql loader ? Can i write a condition in control file so that it checks the available emp_ids in my table ? is it possible ? i dint get any idea .
Please help .
Thanks Much ,
Rahul.
|
|
|
|
| Re: sql loader problem [message #145109 is a reply to message #145107] |
Mon, 31 October 2005 22:45  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
OK, OK, stop posting the same question.
Simple answer: you can't. SQL*Loader only has the ability to insert new rows.
You have two choices:
1. Load the data into another (empty) table and then merge this into the real table. If you are using 9i or 10g, you can use the MERGE statement to perform inserts and updates in a single statement. 8i or earlier, you will have to issue 2 separate statements: an UPDATE and an INSERT.
2. If you are using 9i or 10g, you could avoid the SQL*Loader step altogether. Create an Externally Organized Table (EOT) that references your data file. The equivalent of a SQL*Loader script is embedded in the CREATE TABLE statement for the EOT. Now use a MERGE statement to INSERT/UPDATE the rows from EOT into the real table.
Option 2 is more efficient and uses less permanent space. To create the EOT, you need to read the Oracle SQL Reference - see the CREATE TABLE statement. It only gives you part of the syntax, and will refer you to the Oracle Utilities Reference. For the MERGE statement, see the Oracle SQL reference.
______________
Ross Leishman
|
|
|
|
| Re: sql loader problem [message #145115 is a reply to message #145109] |
Mon, 31 October 2005 23:29 |
trahul4
Messages: 9
Registered: June 2005
|
Junior Member |
|
|
Thanks Ross.
but if i create one more table ( as im using 8i ) , how can i write the update statement ? I need not worry about insert as subsequent data that i get from sql loader is only updated information and now new ones.
so now if i create one more table , please help me in writing update statement .
Thanks Much,
Rahul.
|
|
|
|
| Re: sql loader problem [message #145121 is a reply to message #145115] |
Mon, 31 October 2005 23:58 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Easy but slow way:
UPDATE test
SET FIRST_NM = (
SELECT FIRST_NM
FROM temp_test
WHERE temp_test.emp_id = test.emp_id);
Fast but more difficult:
UPDATE (
SELECT test.first_nm, temp_test.first_nm as new_first_nm
FROM test, temp_test
WHERE test.emp_id = temp_test.emp_id)
SET first_nm = new_first_nm
If you have more than 1000 rows to update, then you will notice that the first method is very slow.
If you want to use the second method, then you MUST have a UNIQUE or PRIMARY KEY constraint on temp_test.emp_id - a simple unique index is not enough. If you don't have the constraint, Oracle will raise an error (something like Cannot update non-key-preserved view).
If TEMP_TEST is an externally organized table then you cannot create the constraint, so you must use an undocumented workaround:
UPDATE /*+ BYPASS_UJVC*/ (
SELECT test.first_nm, temp_test.first_nm as new_first_nm
FROM test, temp_test
WHERE test.emp_id = temp_test.emp_id)
SET first_nm = new_first_nm
Since it is undocumented, I would avoid using BYPASS_UJVC unless you research it (try GOOGLE) and understand what it means.
_______________
Ross Leishman
|
|
|
|
|
|
| Re: sql loader problem [message #162478 is a reply to message #145107] |
Fri, 10 March 2006 07:49 |
Aju
Messages: 94
Registered: October 2004
|
Member |
|
|
Hi
I tried to search for the undocumented hint in bypass_ujvc but could not able to get its explanation but I could see a pattern. It gets used when Update statement is used along with Select statement.
Thanks
|
|
|
|
|
|
|
|
| Re: sql loader problem [message #162923 is a reply to message #162874] |
Tue, 14 March 2006 03:01 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
There's some stuff on AskTom, but Tom is a bit cagey about it because it's undocumented and he wants to avoid liability if Oracle change or remove the functionality. If you search AskTom for "key preserved", you'll get ample examples of the problem that bypass_ujvc solves.
My website is a bit more forthright on bypass_ujvc (look for "Updateable Join Views" in the menu frame), but read the limit of liability clause in the copyright header first 
_____________
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
