how can i validate the data in the control file?

Consider that the SQLLoader reads the Input data File from the path and it loads the data into the tables based up on the descrition specified in the control File. First, the table to be filled is created:

create table sql_loader_1 ( load_time date, field_1 Numeric, field_2 varchar2(10) 

Sample Control File : load_1.ctl

load data 
infile 'load_1.dat' "str '\r\n'" 
insert into table sql_loader_1 
( 
 load_time sysdate, 
 field_2 position( 1:10),
 field_1 position(11:20)
) 

Note that the positions 11 through 20 are loaded into field_1 and positions 1 through 10 into field_2. The field load_time is filled with the current time (sysdate) of the load.

Here's the data. The name of the file (load_1.dat) had been specified with the infile statement in th开发者_StackOverflow中文版e control file.

load_1.dat

0123456789abcdefghij
**********##########
foo         bar
here comes a very long line 
and the next is 
short 

Here I want to validate the field_1 (Numeric Datatype) since the data file contains the character value (i.e)abcdefghij

You say you want to validate field_1, but it's not clear what action are you expecting if the validation fails?

Two other thoughts:

1. Have you considered using the BADFILE option in your control file for rejected rows?

2. Instead of dealing with the number conversion in SQL-Loader, load the data as text and convert it to a number/handle it once in the database. This may be easier.