Re: A challenging SQL*Loader question. Can you Help?

Hi :)

Well u can create three separate control files and load the records for each table one after another like this

Content of your control file 1

load data
infile "myrecords.dat"
reclen XXX <- Give the record length here into table emp append
when ((01:02)='01')
(
 Define your field mapping for the records here )

Content of your control file 2

load data
infile "myrecords.dat"
reclen XXX <- Give the record length here into table project append
when ((01:02)='02')
(
 Define your field mapping for the records here )

Content of your control file 3

load data
infile "myrecords.dat"
reclen XXX <- Give the record length here into table dept append
when ((01:02)='03')
(
 Define your field mapping for the records here )

Now load your records one after another. This way all your foreign key will be loaded first and hopefully u would not have any problem.

What 'when' clause is doing that it will only take those lines that have 01, 02, 03 respectively and will ignore rest of the lines which do not have the condition value at the specified position.

So u load first your emp record starting with 01, than project 02 and last 03 for the dept.

Hope this thaught help u out.

In article <6r1bc2$43t$1_at_nnrp1.dejanews.com>,   camposj_at_ctc.com wrote:
> 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
>

-----== 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 - 15:11:37 CDT