sqlldr: selected field insert from data file

From: Godwin vincent <godwin.ror_at_gmail.com>
Date: Mon, 14 Jan 2008 16:45:03 -0600
Message-ID: <f153edf80801141445u1ae43275i9d49e4b3b72c0b1c@mail.gmail.com>


Hi all,
I have a question with Oracle SQLLDR. I have a table, which has the following columns
SQL> desc HARRIS.CUST;
Name Null? Type

----------------  ------------------------- --------
NAME                        VARCHAR2(50 CHAR)
APP_NAME                  VARCHAR2(50 CHAR)
MASTER_SVR            VARCHAR2(10 CHAR)

I have a DATA FILE, which has more than 5 columns of data in it and all i want is just the above 3 columns to be loaded in to the table STB_TYPE from the DATA FILE. Following is the format of DATA FILE (data.txt) that i have
(the first row is just the heading as a reference to the columns below)

NAME|LOCATION|SERIAL_NUMBER|APP_NAME|MASTER_SVR|ADDRESS|ORGANIZATION hr|chicago|1234|ps|secaucus|nj|comm
billing|chicago|234|paystubs|secaucus|nj|paychex recruit|sacramento|ps|newark|nj|resource holidays|sacramento|ps|washington|va|hr

As you can there, there are 7 fields in the data file, which are delimited by "|". All i would need from this data file are columns, NAME, APP_NAME,MASTER_SVR, which are at position 1,4,5 fields respectively. My question here is how can i represent this in the control file? so that i can only get the corresponding columns get loaded in to the table.

LOAD DATA
INFILE '/export/home/oracle/data.txt'
TRUNCATE INTO TABLE HARRIS.CUST
(NAME .......,

APP_NAME ......,
MASTER_SVR .......) What should i put in the control file so that only the 1,4,5 fields of the data file gets loaded in to the table?

The POSITION(x,y) does not work in this case as the column width is not fixed.

I know the opposite could be done, where we use the FILLER keyword to filter out. Is there anyway to do this or manipulating the datafile (using AWK to extract 1,4,5 columns) the only way?

I am working with Oracle 9.2.0.8 on Solaris 10 server.

Any help would be greatly appreciated.

Thanks,
Godwin.

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 14 2008 - 16:45:03 CST

Original text of this message