RE: SQL Loader Question
- From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
- To: <gba.oraclel@xxxxxxxxx>, "Oracle Discussion List" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 25 Sep 2007 08:06:16 -0400
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
Other related posts:
