RE: SQL Loader Question

I have to agree with Mark on this.    I always had trouble loading
fields with assumed decimal points.  Creating character formatted output
always worked the best for me. See if you can just load the first two
numeric fields before tackling the rest.  
 
The SQL*Loader manual has examples of how to load data into multiple
tables.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/ldr_contr

ol_file.htm#sthref871
 
The tricky part will be parsing out the records from the variable length
array (1-50).  Since it is the end of the record, you may want to load
it as one large column (size [109+8+1+3]*50 = 6050) into a temporary
table, then parse it from there with PL/SQL.
 
Jay

________________________________

From: oracle-l-bounce@freelists.org
[mailto:oracle-l-bounce@freelists.org] On Behalf Of Powell, Mark D
Sent: Monday, September 24, 2007 4:02 PM
To: Oracle Discussion List
Subject: RE: SQL Loader Question


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@freelists.org
[mailto:oracle-l-bounce@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 



**DISCLAIMER
This e-mail message and any files transmitted with it are intended for the use of the individual or entity to which they are addressed and may contain information that is privileged, proprietary and confidential. If you are not the intended recipient, you may not use, copy or disclose to anyone the message or any information contained in the message. If you have received this communication in error, please notify the sender and delete this e-mail message. The contents do not represent the opinion of D&E except to the extent that it relates to their official business.

--
http://www.freelists.org/webpage/oracle-l