| Stuck on SQL*LOADER problem [message #316714] |
Sun, 27 April 2008 23:59  |
Stuck08
Messages: 2
Registered: April 2008
|
Junior Member |
 
|
|
Hello all,
For a class project I'm attempting to create a database and use SQL*Loader to load Comma delimited value records. The problem I'm running into is I have foreign tables with a foreign key to the main table. For example I have an Occupation Table that holds Valid Occupation Values (text), and in the main table I simply hold a FK to the occ_id value that corresponds to it.
CREATE TABLE occupation_tbl(
occ_id NUMBER(2) PRIMARY KEY,
occ_desc VARCHAR2(20)
);
My main table has an Occupation field which references occupation_tbl(occ_id).
I'm trying to load my data, and I have the 'Occupation' String Value, What I'm wondering is how can I do a select inside of SQL*LOADER to get the FK numerical value of the text?
Something like
...
FIELDS TERMINATED BY ","
(
person_id SEQUENCE(50000,1),
age,
(select occ_id from occupation_table where occupation_desc='Machine-op-inspct'),
...
Example of CDV record
25,Private,226802,11th,7,Never-married,Machine-op-inspct,Own-child,Black,Male,0,0,40,United-States,<=50K
|
|
|
|
|
|
| Re: Stuck on SQL*LOADER problem [message #316741 is a reply to message #316715] |
Mon, 28 April 2008 02:48  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Frank's suggestion of a function is perfectly valid, however it does not scale well. Since SQL*Loader is commonly used with large data volumes, Functions usually turn out to be too limiting.
The usual approach is to load into a staging table and then transform into the target table using an INSERT statement.
This can all be done in a single step using Externally Organized Tables.
Ross Leishman
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
