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

You may be better off writing a PL/SQL procedure that uses the UTL_FILE package to read and process your extract file.

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
Received on Fri Aug 14 1998 - 14:54:40 CDT