Getting error in the Control File Format !!

Hi,

I am having a datafile like this.

T1Col1`T1Col2`T1Col3`T2Col1`T2Col2`T2Col3`T2Col4`T2Col5`T3Col1`T3Col2`T3Col3`T3Col4
```T2Col1`T2Col2`T2Col3`T2Col4`T2Col5`T3Col1`T3Col2`T3Col3`T3Col4
````````T3Col1`T3Col2`T3Col3`T4Col4

and so on.

I have just taken a single table and tried to write control file like this.

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------

OPTIONS (SKIP=1)
Load Data
INFILE 'C:\DataFiles\DataFile.dat'
BADFILE 'C:\BadFiles\BadRecord.bad'
APPEND
INTO TABLE TABLE1
WHEN T1Col1 !=' '
FIELDS TERMINATED BY "`"
TRAILING NULLCOLS
(
T1Col1
,T1Col2
,T1Col3
)

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
I am removing first line from the data file as it is of no use. The data is arranged like Table1 data followed by Table2Data Followed by Table3 Data and if table3 is having many rows then in the next line all other columns of all othe tables will be null and only delimiter will be there. I have shown till table3 here for simplicity, i am having more tables like that in the original file.

And to get all the table's data I have written INTO TABLE clause for other tables like this.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------
OPTIONS (SKIP=1)
Load Data
INFILE 'C:\DataFiles\DataFile.dat'
BADFILE 'C:\BadFiles\BadRecord.bad'
APPEND
INTO TABLE TABLE1
WHEN T1Col1 !=' '
FIELDS TERMINATED BY "`"
TRAILING NULLCOLS
(
T1Col1
,T1Col2
,T1Col3
)
INTO TABLE TABLE2
WHEN T2Col1 !=' '
FIELDS TERMINATED BY "`"
TRAILING NULLCOLS
(
T2Col1
,T2Col2
,T2Col3
,T2Col4
,T2Col5
)
INTO TABLE TABLE3
WHEN T3Col1 !=' '
FIELDS TERMINATED BY "`"
TRAILING NULLCOLS
(
T3Col1
,T3Col2
,T3Col3
,T4Col4
)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------

I am getting WHEN clause failed error. I didn't put WHEN clause in one of my test and still i got error like this

" 0 Rows not loaded due to data errors.
0 Rows not loaded because all WHEN clauses were failed.
0 Rows not loaded because all fields were null.
"

Can someone please tell me where i am making mistake ?

My second question is if the names of the columsn in the datbase are not in the order as given in the Control file will that causes error. I am not having my columns in the order described above. So is that causing error ?

Any help and hint to solve this problem would be highly appreciated.

Thanks in the anticipation that I will get some reply.

Regards,
Dipesh

About the syntax in the control file:
APPEND INTO TABLE TABLE1
WHEN T1Col1 !=' '
FIELDS TERMINATED BY "`"

T1Col1 should probably be :T1Col1
And about FIELDS TERMINATED BY "`". I always use single quotes as in FIELDS TERMINATED BY '`'

To answer your second question: The order of the columns in the control file is independent of the order of the columns in the table.

Good luck,
Dan

Hi Dan,

Thanks for replying and informing me about the syntax usage.

I am able to load the data properly now. But getting error in loading data into the database.
I am getting date in this format.

MM/DD/YYYY HH:MM.

What should i write as my datatype for such column in the control file so that i will get properly loaded into the oracle database. In the database the datatype for that column is described as Date of length 7.

So what should i mention in the control file ?
Any help would be highly appreciated.

Eagerly waiting for reply.

Regards,

Let's say T3Col1 is the date field. Then the following should work:

TRAILING NULLCOLS
(
T3Col1 DATE "MM/DD/YYYY HH:MM" ,
....


Cheers,
Dan

Hi,

Thanks it worked well. I gave something like this

DATE "MM/DD/YYYY HH:MI:SS AM"

Thanks for your help.

Regards,