RE: SQL Loader Question

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Mon, 24 Sep 2007 16:01:56 -0400
Message-ID: <D1DC33E67722D54A93F05F702C99E2A901622ACD@usahm208.amer.corp.eds.com>

Having once been a COBOL coder I offer the opinion it would be better to write a COBOL extraction program that processes each record creating each desired input row for the target tables in character format. It is almost always easier to format data as you need it when you create it rather than to try to reformat it when you need to load it.

• Mark D Powell -- Phone (313) 592-5148

  ---

        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 Mon Sep 24 2007 - 15:01:56 CDT