1

I use sql loader for load data in my database. For example, I want load records only from certain range, but I get this message:

https://i.stack.imgur.com/Z37LC.png

My control file:

LOAD DATA
INFILE 'C:\sql\empl.dat'
BADFILE 'C:\sql\empl.bad'
DISCARDFILE 'C:\sql\empl.dat.dsc'
INTO TABLE LOAD_EMP_N
WHEN empno<'5600'
FIELDS TERMINATED BY "|"
(empno,ename CHAR, job CHAR,mgr,
hiredate DATE "DD-MM-YYYY", sal,comm,
deptno)

If I change when clause

WHEN empnо='5600' -- all work!!

I can't understand what is the problem?

Юлия Нечипорук
  • 13
  • 6

1 Answers1

3

Quote from the SQL*Loader manual

A field condition is a statement about a field in a logical record that evaluates as true or false. It is used in the WHEN, NULLIF, and DEFAULTIF clauses.
...
operator A comparison operator for either equal or not equal.

So in other words: the when condition in a SQL*Loader control files only supports = or <>

  • Oh...maybe exist some other way, how I can set condition? – Юлия Нечипорук Nov 02 '16 at 20:05
  • @ЮлияНечипорук - I don't even know what SQL Loader is (I do have a hypothesis), but if you can't filter the rows the way you want in the loading process, two other approaches come to mind. One is to filter the rows at the source (whatever it is). The other is to load all the rows and then to filter them in the target (presumably an Oracle database?) - although that may make the entire loading process take longer. To do it at the source, one could use AWK for example (if on a unix-like OS and the input is a text file). –  Nov 02 '16 at 22:30