Home » RDBMS Server » Server Utilities » SQL Loader
SQL Loader [message #425592] Fri, 09 October 2009 16:49 Go to next message
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_id (Integer, PK)
customer_lastname (varchar)
customer_firstname (varchar)

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.)

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

accounttype_id POSITION(25:34) char,
accounttype_name POSITION(15:24) char
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

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

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
Messages: 3201
Registered: April 2005
Location: Heilbronn, Germany
Senior Member

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: Thu Jun 29 11:07:27 CDT 2017

Total time taken to generate the page: 0.14527 seconds