Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2007

    Unanswered: Data Validation in ctl file for SQL Loader

    Hello all,

    I am writing a ctl file for SQL loader in oracle to load data from a csv file.
    My ctl file is as follows
    HTML Code:
    load data 
    	into table csv_languages
              fields terminated by ";" optionally enclosed by '"'  trailing nullcols		  
    		person_no	     ,
    		languagename         ,
    		speaking_level       ,
    		LComment             ,		
    		time_stamp	"to_date(:time_stamp, 'YYYY-MM-DD')"
    I want to validate the speaking language input field it has to be between 1,2,3 and 4 any other value then it should reject the entire record. One check i have done is for interger value by making the table field datatype to Integer. but my problem is the values i don't want to accept values greater then 4.

    is there a way to do this validation in the ctl file, can this be done.


  2. #2
    Join Date
    Aug 2003
    Put a constraint on the table to reject data not 1,2,3,4.

  3. #3
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 4
    Loading records based on condition might be interesting to read.

    It would be great if we could use OR operator within the WHEN clause; unfortunately, we can not. So, if you want to mimic such a behaviour, you'd use 4 INSERT INTO statements in a control file, each loading its own "speaking level". Something like
    INTO TABLE csv_languages
    WHEN speaking_level = 1
       language_name, ...
    INTO TABLE csv_languages
    WHEN speaking_level = 2
       language_name, ...
    Another option, is to use external tables feature. Its advantage is that you'd, actually, use SQL to load records you want (specifying them in WHERE clause, just as you'd do it in an ordinary query); its drawback is that source file has to be stored on a server (as opposed to SQL*Loader, where file can be used locally) and you have to have access to it.

    OK; now you have three options (Ida's and mine). Pick one.

  4. #4
    Join Date
    Jul 2012
    i want to do validations before inserting data to interface table from temp table like if the no of records for the same batch is more than 15 in temp table ,nothing after the 15th record shud b inserted in interface can i do that?

  5. #5
    Join Date
    Jun 2004
    Liverpool, NY USA
    Dont use sql loader, use external tables then you can validate your data using the where clause.

    Sorry littlefoot, I just read the same suggestion from you.
    Last edited by beilstwh; 07-17-12 at 09:17.
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts