RE: SQL Loader Question

From: Jack van Zanen <jack_at_vanzanen.com>
Date: Tue, 25 Sep 2007 06:02:55 +1000
Message-ID: <003401c7fee5$e7f7c890$b7e759b0$@com>

Hi  

You mention no Oracle version but....

How about make it an external table and take it from there?    

Jack

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of GBA-DBA
Sent: Tuesday, 25 September 2007 3:23 AM 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 Mon Sep 24 2007 - 15:02:55 CDT