RE: SQL Loader Question

  • From: "Jack van Zanen" <jack@xxxxxxxxxxxx>
  • To: <gba.oraclel@xxxxxxxxx>, "'Oracle Discussion List'" <oracle-l@xxxxxxxxxxxxx>
  • Date: Tue, 25 Sep 2007 06:02:55 +1000

Hi

 

You mention no Oracle version but....

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

 

 

Jack

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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 

Other related posts: