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

    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 
    CHARACTERSET UTF8
    append          	  
    	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.

    Thanks

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

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    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
    Code:
    INTO TABLE csv_languages
    WHEN speaking_level = 1
      (person_no,
       language_name, ...
      )
    
    INTO TABLE csv_languages
    WHEN speaking_level = 2
      (person_no,
       language_name, ...
      )
    etc.
    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
    Posts
    2
    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 table.how can i do that?

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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 10:17.
    Bill
    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
  •