sql loader for inserting the data from multiple fields from unix

Shell Programming and Scripting
1

Hi ,
I have my log file something like this

(07/29/2009 00:02:24.467) 367518 (07/29/2009 00:02:26.214) 949384011
(07/29/2009 00:02:26.236) 367524 (07/29/2009 00:02:28.207) 949395117
(07/29/2009 00:02:28.240) 337710 (07/29/2009 00:02:30.621) 949400864

I am trying to insert the data into oracle data base as follows.

column1 : (07/29/2009 00:02:24.467)
column2 : 367518
column3 : (07/29/2009 00:02:26.214) 
column4 : 949384011

Can anyone help me with the control file format?

2

To be frank it's a oracle question :

How ever some thing like this you can try :

LOAD DATA
INFILE 'D:\work\load.txt'

INTO TABLE sample 

(col1         POSITION(02:24)   char,
col2         POSITION(27:32)   INTEGER EXTERNAL,
col3          POSITION(35:57)   CHAR,
col4          POSITION(60:68)   INTEGER EXTERNAL
)

Here i assumed that , the format of your log file remains same.

3

Panyam, i tried with that but some of the data is not fixed in length....

4

You can search in this forum , where you can raise a post if you have any doubt.

http://www.orafaq.com
/wiki/SQL*Loader_FAQ#Can_one_load_variable_and_fixed_length_data_records.3F