Re: sqlload control file

From: Donna Parker <dsparker_at_worldnet.att.net>
Date: 1996/11/01
Message-ID: <01bbc7ce$b2e03890$f70774cf_at_270137128>#1/1

LOAD DATA
INFILE *
REPLACE
INTO TABLE Request
(

	Request_Number	POSITION(1)	CHAR(8),
	CRS_Number		POSITION(*)	CHAR(16),
	Priority			POSITION(*)	CHAR(4),
	Status_Code		POSITION(*)	CHAR(1),
	Entry_Date		POSITION(*)	DATE "MM/DD/YYYY" NULLIF Entry_Date=BLANKS,
	Desire_Date		POSITION(*)	DATE "MM/DD/YYYY" NULLIF Desire_Date=BLANKS,
	Critical_Date		POSITION(*)	DATE "MM/DD/YYYY" NULLIF Critical_Date=BLANKS,
	Target_Date		POSITION(*)	DATE "MM/DD/YYYY" NULLIF Target_Date=BLANKS,
	Request_Type		POSITION(*)	CHAR(1),
	Short_Desc		POSITION(*)	CHAR(40),
	Entry_Requester	POSITION(*)	CHAR(16),
	Entry_Req_Date		POSITION(*)	DATE "MM/DD/YYYY" NULLIF
Entry_Req_Date=BLANKS,
	Responsible_Person	POSITION(*)	CHAR(16),
	Responsible_Dept	POSITION(*)	CHAR(6),
	Cst_Approve_Flag	POSITION(*)	CHAR(1),
	Cst_Approve_By	POSITION(*)	CHAR(16),
	Cst_Approve_Date	POSITION(*)	DATE "MM/DD/YYYY" NULLIF
Cst_Approve_Date=BLANKS,
	IS_Approve_Flag	POSITION(*)	CHAR(1),
	IS_Approve_By		POSITION(*)	CHAR(16),
	IS_Approve_Date	POSITION(*)	DATE "MM/DD/YYYY" NULLIF
IS_Approve_Date=BLANKS,
	Client_CPCS_Number	POSITION(233)	CHAR(8),
	Last_Update_Date	POSITION(257)	DATE "MM/DD/YYYY" NULLIF
Last_Update_Date=BLANKS,
	Stat_Change_Date	POSITION(283)	DATE "MM/DD/YYYY" NULLIF

Stat_Change_Date=BLANKS
)

INTO TABLE 	Request_Application
WHEN		Appl_Code != BLANKS
(
	Request_Number	POSITION(1)	CHAR(8),
	Appl_Code		POSITION(213)	CHAR(4)

)

INTO TABLE 	Request_Application
WHEN		Appl_Code != BLANKS
(
	Request_Number	POSITION(1)	CHAR(8),
	Appl_Code		POSITION(217)	CHAR(4)

)

INTO TABLE 	Request_Application
WHEN		Appl_Code != BLANKS
(
	Request_Number	POSITION(1)	CHAR(8),
	Appl_Code		POSITION(221)	CHAR(4)

)

INTO TABLE 	Request_Application
WHEN		Appl_Code != BLANKS
(
	Request_Number	POSITION(1)	CHAR(8),
	Appl_Code		POSITION(225)	CHAR(4)

)

INTO TABLE 	Request_Application
WHEN		Appl_Code != BLANKS
(
	Request_Number	POSITION(1)	CHAR(8),
	Appl_Code		POSITION(229)	CHAR(4)

)

This particular control file loads a table and a child table from the same datafile. It replaces the data in the ORACLE tables. Change the keyword REPLACE to APPEND to append data. The asterisk following DATAFILE means the datafile name will be passed on the command line. The asterisks in the POSITIONs mean the next field immedialtely follows the previous field. The numbers in POSITION indicate a column number in the data file. The NULLIF commands load nulls into blank date fields. THe date formats should match the format of your dates in the datafile. The WHEN clauses keep child records from being created if the fields are blank.

Hope this helps a little.

Marc Mangus <marc.mangus_at_wink.com> wrote in article <32792FDC.F20_at_wink.com>...
> Hi folks,
>
> I'm trying to load some text files into my OES 7.3.2 server running on
> Solaris 2.5.1. I've been beating my head against the desk trying to get
> the control file just right. The syntax is insane. Does anyone have
> any sample control files they can post? I've dealing with
> fixed-position ascii files. Thanks!
>
Received on Fri Nov 01 1996 - 00:00:00 CET