A challenging SQL*Loader question. Can you Help?

From: <camposj_at_ctc.com>
Date: Fri, 14 Aug 1998 12:42:42 GMT
Message-ID: <6r1bc2$43t$1@nnrp1.dejanews.com>

I have an ascii file whose data I need to insert into several oracle tables. How can I use SQL*Loader to solve this problem. The tricky part is the unknown number of logical records that may exist in each physical record for record type (02). Any help would be greatly appreciated. If you need more information, don't hesitate to ask.

Thanks, John Campos (Johnstown, PA)

---

Example file:

1234567890123456789012345678901234567890123456789012345678

..........................................................

01169662865CAMPOS         JOHN           E
0203ABC123    WICAP     XYZ789    ITCAT     DEF345    FLIS

030001BUILDINGA 8432865
030002BUILDINGB 7728888

01222666953SMITH          DAVE           g
0203ABC123    WICAP     XYZ789    ITCAT     DEF345    FLIS

030001BUILDINGA 8432865
030002BUILDINGB 7728888
030008BUILDINGC 7727777

---

Tailored Data Extract Record Layout:
"The record layout depends on the first two characters of each row." "Row types 01 through 03 are all related to the same ssn and each other."

---

EMP IDENTIFICATION DATA Record Type (01) 1-2
Social Security Number 3-11

Last Name                 12-26
First Name                27-41
Middle Initial            42
___________________________________________________________________________

PROJECT DATA

Record Type (02)          1-2
Repeat Counter            3-4

Data in positions 5-24 will repeat accordingly (60 logical record maximum).

Project Number            5-14
Project Name              15-24
_____________________________________________________________________________

DEPT DATA Record Type (03) 1-2

Note: Multiple 03 Records (maximum of 20).

Department Number         3-6
Location                  7-16
Phone                     17-23
______________________________________________________________________________

Example database tables:

Emp table
(ssn varchar2(9),

 lname     varchar2(15),
 fname     varchar2(15),
 middle    varchar2(1));

Project table


(ssn       varchar2(9),    -- Foreign key to Emp table.


 projectno varchar2(10),   -- (ssn, projectno) is the composite Primary Key.
 name      varchar2(10))

Dept table


(ssn       varchar2(9),    -- Foreign key to Emp table.


 deptno    varchar2(4),    -- (ssn, deptno) is the composite Primary Key.
 location  varchar2(10),
 phone     varchar2(7))

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Fri Aug 14 1998 - 07:42:42 CDT