Home » RDBMS Server » Server Utilities » SQL Loader
SQL Loader [message #425592] Fri, 09 October 2009 16:49 Go to next message
RyanDev
Messages: 1
Registered: October 2009
Junior Member
Hello, I am having trouble with a problem, I was hoping to get some help here.

My problem is, we are trying to assign one value to two attributes across two tables. The primary key for "customer" is customer_id, and customer_id is a foreign key in the "account" table. The customer_id is generated using SEQUENCE(1,3) when the customer info (name, address, phone, etc) is read in. But how do you take one sequence number and assign it to two places? We need to make sure the account for that customer is tied to the customer (the entire purpose of PKs and FKs). If I was programming I could do this, (assign it to a variable), but SQL*Loader doesn't make the answer obvious.

On one line, 3 customers are read in (but only 1 of those 3 is the primary account holder). So, think of the data like this:

DATA: customer1, customer2, customer3, accountinfo

The DB looks like this:

customer:
-------------------------
customer_id (Integer, PK)
-------------------------
customer_lastname (varchar)
customer_firstname (varchar)
...

account:
-------------------------
account_id (Integer, PK)
-------------------------
customer_id (Integer, FK)
account_balance (float)
...

I am new to Oracle and SQL*Loader. We are training at my job, and we are required to do this project with certain limitations (no temporary procedures, no temporary DB tables, and we can only use PL/SQL to complete our assignment).

The data we are trying to read in has been described to us using COBOL Copybooks, meaning the records are delimited by newlines, and the raw data is difficult to read.

The current control file looks like this... (note, I marked the important parts with a lot of asterisks.)

LOAD DATA
REPLACE INTO TABLE CUSTOMER
WHEN customer_ssn != ' '
(
customer_id SEQUENCE(1,3), ---- <--- Whatever goes into this customer_id .... ************************************
customer_lastname POSITION(65:89) char,
customer_firstname POSITION(40:64) char,
customer_middleinit POSITION(90) char,
customer_suffix POSITION(91:93) char,
customer_primaryphone POSITION(177:190) char,
customer_alternatephone POSITION(191:204) char,
customer_primary CONSTANT 1,
customer_address1 POSITION(94:118) char,
customer_address2 POSITION(119:143) char,
customer_city POSITION(144:158) char,
customer_state POSITION(169:170) char,
cusomter_zipcode POSITION(171:176) char,
customer_ssn POSITION(535:545) char
)

INTO TABLE ACCOUNT_TYPE
(
accounttype_id POSITION(25:34) char,
accounttype_name POSITION(15:24) char
)
INTO TABLE ACCOUNT
(
account_id POSITION(11:14) integer external,
customer_id ---- <--- ... Also needs to go into this customer_id ************************************
accounttype_id POSITION(25:34) char,
account_desc POSITION(1:10) char,
account_code POSITION(35:39) integer external,
account_balance POSITION(618:630) decimal external
)

INTO TABLE CUSTOMER
WHEN customer_ssn !=' '
(
customer_id SEQUENCE(2,3), -- secondary customer
customer_lastname POSITION(258:282) char,
customer_firstname POSITION(233:257) char,
customer_middleinit POSITION(283:283) char,
customer_suffix POSITION(284:286) char,
customer_primaryphone POSITION(370:383) char,
customer_primary CONSTANT 2,
customer_address1 POSITION(287:311) char,
customer_address2 POSITION(312:336) char,
customer_city POSITION(337:351) char,
customer_state POSITION(362:363) char,
cusomter_zipcode POSITION(364:369) char,
customer_ssn POSITION(546:556) char
)

INTO TABLE CUSTOMER
WHEN customer_ssn !=' '
(
customer_id SEQUENCE(3,3), -- another secondary customer
customer_lastname POSITION(409:433) char,
customer_firstname POSITION(384:408) char,
customer_middleinit POSITION(434:434) char,
customer_suffix POSITION(435:437) char,
customer_primaryphone POSITION(521:534) char,
customer_primary CONSTANT 2,
customer_address1 POSITION(438:462) char,
customer_address2 POSITION(463:487) char,
customer_city POSITION(488:502) char,
customer_state POSITION(513:514) char,
cusomter_zipcode POSITION(515:520) char,
customer_ssn POSITION(557:567) char
)


Thanks in advance for the help. I hope I was clear.
Re: SQL Loader [message #425617 is a reply to message #425592] Sat, 10 October 2009 04:42 Go to previous message
ThomasG
Messages: 3185
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Hello,

Well, if would have been clearer if you had formatted the code with CODE tags, like explained in the Forum Guide.

Anyway, the sequence is generated per input line. You tell oracle which number to start with, and by how much to increment after one input line is loaded. So you can just use :

....
customer_id SEQUENCE(1,3)
....
customer_id SEQUENCE(1,3)
....
customer_id SEQUENCE(2,3)
....
customer_id SEQUENCE(3,3)


In your INTO TABLE sections, then the first two are the same.
Previous Topic: loading null values in date column using sql loader (merged)
Next Topic: EXPDP errors
Goto Forum:
  


Current Time: Tue Sep 27 12:48:59 CDT 2016

Total time taken to generate the page: 0.09232 seconds