Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL Loader Question
Hi Jack,
I'm using 10g R2. I'm loading the table using PL/SQL but the standard here is SQL Loader for these tasks and they want it that way.
Regards,
GBA
On 9/24/07, Jack van Zanen <jack_at_vanzanen.com> wrote:
>
> 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
>
-- Regards GBA -- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 24 2007 - 15:10:20 CDT