Home » RDBMS Server » Server Utilities » Stuck on SQL*LOADER problem (10g Windows XP)
Stuck on SQL*LOADER problem [message #316714] Sun, 27 April 2008 23:59 Go to next message
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 #316715 is a reply to message #316714] Mon, 28 April 2008 00:16 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You can write a function to do the lookup. See "Can one modify data as the database gets loaded?" in the SQL*Loader FAQ.
Re: Stuck on SQL*LOADER problem [message #316741 is a reply to message #316715] Mon, 28 April 2008 02:48 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Stuck on SQL*LOADER problem [message #316869 is a reply to message #316714] Mon, 28 April 2008 11:47 Go to previous message
Stuck08
Messages: 2
Registered: April 2008
Junior Member

Thank you both for your time and suggestions, I'll look into both solutions to try to find which one works. I have to load about 50k records, so it's not a tremendous amount of data.
Previous Topic: SQLLDR
Next Topic: Export from SQL Server 2005 and Import to Oracle 10g
Goto Forum:
  


Current Time: Sat Dec 10 22:52:36 CST 2016

Total time taken to generate the page: 0.08660 seconds