sqlldr problem (invalid number)

From: Guido Buerger <guido.buerger_at_gmx.de>
Date: 26 Feb 2003 07:20:46 -0800
Message-ID: <10eb3a31.0302260720.14a9e90d_at_posting.google.com>



Hi, everybody!

I have a SQL*Loader problem: I try to read in from a fixed-formated input file, which has the following form and content:

7782 CLARK      MANAGER   7839  2572.50           10 
7839 KING       PRESIDENT       5500.00           10 
7934 MILLER     CLERK     7782   920.00           10 
7566 JONES      MANAGER   7839  3123.75           20 
7499 ALLEN      SALESMAN  7698  1600.00   300.00- 30 
7654 MARTIN     SALESMAN  7698  1312.50  1400.00  30 
7658 CHAN       ANALYST   7566  3450.00           20 
7654 MARTIN     SALESMAN  7698  1312.50  1400.00  30

The control file looks like this:

LOAD DATA
INFILE 'staff.dat'
INTO TABLE emp

(empno         POSITION(01:04)   INTEGER EXTERNAL,
ename          POSITION(06:15)   CHAR,
job            POSITION(17:25)   CHAR,
mgr            POSITION(27:30)   INTEGER EXTERNAL,
sal            POSITION(32:39)   DECIMAL EXTERNAL,
comm           POSITION(41:49)   DECIMAL EXTERNAL,
deptno         POSITION(51:52)   INTEGER EXTERNAL)

Everything works fine but on record 5 I get the following message:

Record 5: Rejected - Error on table STAFF, column COMM. ORA-01722: invalid number

Clearly, the SQL*loader is bothered by the minus sign behind the number. How it is possible to read in such formated values? I would be much obliged for any hints!

Greetings, Guido. Received on Wed Feb 26 2003 - 16:20:46 CET

Original text of this message