Home » RDBMS Server » Server Utilities » SQL Loader
| SQL Loader [message #425592] |
Fri, 09 October 2009 16:49  |
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  |
ThomasG
Messages: 3211
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.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 17:18:18 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
