RE: SQL Loader Question

From: Mercadante, Thomas F (LABOR) <Thomas.Mercadante_at_labor.state.ny.us>
Date: Tue, 25 Sep 2007 08:06:16 -0400
Message-ID: <ABB9D76E187C5146AB5683F5A07336FFE08FC2@EXCNYSM0A1AJ.nysemail.nyenet>

GBA, You might look at doing a simple load of this data into a temporary table and then post-processing it via a PL/SQL package. I know that SqlLoader can load records into multiple tables from a single file (look at the sample sqlloader scripts provided with the product. These scripts used to be in the rdbms/demo directory but have been moved in version 10. If you have a version 9 installation, you can find them.

I agree with Mark Powell that formatting the data is probably the best way to go just to make it simpler. But you should be able to load the data and process it as I suggest above.

Hope this helps.

Tom

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of GBA-DBA Sent: Monday, September 24, 2007 1:23 PM To: Oracle Discussion List
Subject: SQL Loader Question

Hi List,

I need to load a cobol file with a record structure (a portion of it) like this:

10 MV-TPR-BILL-AMT-REC-TO-DATE PIC S9(07)V99.
10 MV-TPR-BILL-NH-DIAG-DESC PIC X(30). 
10 MV-TPR-BILL-DRUG-CODE-DESC REDEFINES 
MV-TPR-BILL-NH-DIAG-DESC PIC X(30). 
10 MV-TPR-BILL-NH-TOTAL-COST PIC S9(5)V99.
10 MV-TPR-BILL-2ND-NOTICE-DATE PIC X(8).
10 MV-TPR-BILL-WRITE-OFF-DATE PIC X(8). 
10 MV-TPR-BILL-RESPONSE-INFO OCCURS 3 TIMES. 
20 MV-TPR-DATE-OF-RESPONSE PIC X(8).

20 MV-TPR-RESP-ACTION-CODE PIC X(02).
20 MV-TPR-RESP-EXPLN-CODE.
30 RESPONSE-OVERIDE-CODE PIC X.
30 FILLER PIC X.
20 MV-TPR-RESP-AMT-PAID PIC S9(5)V99.
20 MV-TPR-COST-CNT-ACCT-CODE PIC X(5).
20 FILLER PIC X(09).
10 MV-TPR-BILL-RCD-CNTR PIC 99.
10 MV-TPR-BILL-PAYT-DATA OCCURS 1 TO 50 TIMES DEPENDING ON MV-TPR-BILL-RCD-CNTR.
20 FILLER PIC X(109).
20 MV-TPR-BILL-HIST-REC-INDCATOR PIC X. 20 FILLER PIC X(08).
20 MV-TPR-BILL-SVC-CD PIC X(01).
20 MV-TPR-BILL-BILL-CD PIC X(03). The fields outside the occurs sections should go to table 1, the fields inside the occurs 3 section should go to table 2 and the fields inside the occurs 1 to 50 section should go to table 3.

Once the data is loaded the data should be like this: For each record in table 1 I should have 1 to 3 records in table 2 and 1 to 50 records in table 3.

Is there any way to do this with SQL Loader??

-- 
Regards
GBA 



--
http://www.freelists.org/webpage/oracle-l

Received on Tue Sep 25 2007 - 07:06:16 CDT