Home » Infrastructure » Other Operating Systems » Sql*loader : loading a MVS format
Sql*loader : loading a MVS format [message #71889] Wed, 29 January 2003 07:15 Go to next message
Messages: 23
Registered: August 2002
Junior Member
I have a file with an extended number zone of type S9(8)V9(7). I want to load it in a column of type NUMBER(15,7).

The ctl is build like this :
field position(163:179) DECIMAL EXTERNAL,

The problem is that the numbers who are signed (for example (- 16.0000000) are in error when the load is executed (ORA-01722: invalid number)

How can i adapt the control file ? Thanks
Re: Sql*loader : loading a MVS format [message #71895 is a reply to message #71889] Wed, 29 January 2003 11:58 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
CAN you post your controlfile and table description?
Re: Sql*loader : loading a MVS format [message #71900 is a reply to message #71895] Thu, 30 January 2003 06:34 Go to previous message
Messages: 23
Registered: August 2002
Junior Member
This is the controlfile :

INTO TABLE table_name
field1 position(1:2) CHAR,
field2 position(3:12) CHAR,
field3 position(13:20) CHAR,
field4 position(21:27) CHAR,
field5 position(28:32) CHAR,
field6 position(33:39) CHAR,
field7 position(40:42) CHAR,
field8 position(43:46) CHAR,
field9 position(47:50) CHAR,
field10 position(51:54) CHAR,
field11 position(55:67) DECIMAL EXTERNAL,
field12 position(68:80) DECIMAL EXTERNAL,
field13 position(81:97) DECIMAL EXTERNAL,
field14 position(98:112) DECIMAL EXTERNAL,
field15 position(113:123) DECIMAL EXTERNAL,
field16 position(124:134) DECIMAL EXTERNAL,
field17 position(135:141) CHAR,
field18 position(142:148) CHAR,
field19 position(149:155) CHAR,
field20 position(156:162) CHAR,
field21 position(163:179) DECIMAL EXTERNAL,
field22 position(180:188) DECIMAL EXTERNAL,
field23 position(189:199) DECIMAL EXTERNAL,
field24 position(200:210) DECIMAL EXTERNAL,
field25 position(211:223) DECIMAL EXTERNAL,
field26 position(224:236) DECIMAL EXTERNAL,
field27 position(237:253) DECIMAL EXTERNAL,
field28 position(254:270) DECIMAL EXTERNAL,
field29 position(271:287) DECIMAL EXTERNAL,
field30 position(288:288) CHAR,
field31 position(289:296) DECIMAL EXTERNAL,

and the table description :
CREATE TABLE table_name
field1 VARCHAR2(2),
field2 VARCHAR2(10),
field3 VARCHAR2(8),
field4 VARCHAR2(7),
field5 VARCHAR2(5),
field6 VARCHAR2(7),
field7 VARCHAR2(3),
field8 VARCHAR2(4),
field9 VARCHAR2(4),
field10 VARCHAR2(4),
field11 NUMBER(11,2),
field12 NUMBER(11,2),
field13 NUMBER(15,2),
field14 NUMBER(13,2),
field15 NUMBER(9,2),
field16 NUMBER(9,2),
field17 VARCHAR2(7),
field18 VARCHAR2(7),
field19 VARCHAR2(7),
field20 VARCHAR2(7),
field21 NUMBER(15,7),
field22 NUMBER(7,2),
field23 NUMBER(9,2),
field24 NUMBER(9,2),
field25 NUMBER(11,2),
field26 NUMBER(11,2),
field27 NUMBER(15,2),
field28 NUMBER(15,2),
field29 NUMBER(15,2),
field30 VARCHAR2(1),
field31 NUMBER(6,3)

The field21 has a format S9(8)V9(7) with 6 blanks between the sign and then first number (-16.0000000)
Thanks for your help
Previous Topic: Re: Problems in Developer 2000 with Oracle 8.
Next Topic: Getting NET SEND to work on WinXP PRO
Goto Forum:

Current Time: Mon Sep 20 21:39:34 CDT 2021