OraFAQ Forum: Server Utilities » SQLLDR Issue with NUMBER data type

Home » RDBMS Server » Server Utilities » SQLLDR Issue with NUMBER data type

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQLLDR Issue with NUMBER data type [message #614324]

Tue, 20 May 2014 20:28

aimy
Messages: 225
Registered: June 2006

Senior Member

Hi all.

I'm really stuck with this basic issue.

My table:

CREATE TABLE S13018_SQLLDR_DATA (
  CATEGORY    CHAR(3)      NULL,
  REGION      VARCHAR2(36) NULL,
  STATE       VARCHAR2(36) NULL,
  DISTRICT    VARCHAR2(36) NULL,
  ZONE_NAME   VARCHAR2(36) NULL,
  BUILDING_ID VARCHAR2(36) NULL,
  TOTAL_COUNT NUMBER(6,0)  NULL
)
/

The data

TEL|EASTERN REGION|PAHANG|PH|ZONE TERUNTUM|AAR|5206
TEL|SOUTHERN REGION|JOHOR|JS|ZONE SKUDAI / PONTIAN|AB|395
TEL|SOUTHERN REGION|JOHOR|JS|ZONE SENAI|ABN|229
TEL|SOUTHERN REGION|MELAKA|MK|ZONE MELAKA UTARA|ABU|635
TEL|SOUTHERN REGION|MELAKA|MK|ZONE MELAKA UTARA|AG|2986
TEL|SOUTHERN REGION|JOHOR|JU|ZONE KLUANG/MERSING|AH|1016

My control file is

load data
  append
  into table s13018_SQLLDR_DATA
  fields terminated by "|"
  trailing nullcols
  (
      CATEGORY       CHAR(3),
      REGION         CHAR(36),
      STATE          CHAR(36),
      DISTRICT       CHAR(36),
      ZONE_NAME      CHAR(36),
      BUILDING_ID    CHAR(36),
      TOTAL_COUNT   INTEGER EXTERNAL
  )

But when I execute the loading, all records were rejected with error:
[/code]Record 1: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number

Record 2: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number

Record 3: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number

Record 4: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number

Record 5: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number[/code]

Thank you.

Report message to a moderator

Re: SQLLDR Issue with NUMBER data type [message #614325 is a reply to message #614324]

Tue, 20 May 2014 21:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

> TOTAL_COUNT INTEGER EXTERNAL
not as above, but as below
TOTAL_COUNT NUMBER

Report message to a moderator

Re: SQLLDR Issue with NUMBER data type [message #614326 is a reply to message #614325]

Tue, 20 May 2014 21:29

aimy
Messages: 225
Registered: June 2006

Senior Member

Thanks.

But I got this error:

SQL*Loader: Release 9.2.0.7.0 - Production on Wed May 21 10:28:07 2014

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL*Loader-350: Syntax error at line 13.
Expecting "," or ")", found "NUMBER".
      TOTAL_COUNT   NUMBER

Report message to a moderator

Re: SQLLDR Issue with NUMBER data type [message #614327 is a reply to message #614326]

Tue, 20 May 2014 21:40

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

[oracle@localhost ~]$ sqlldr user1/user1 control=raw1.dat

SQL*Loader: Release 11.2.0.2.0 - Production on Tue May 20 19:29:22 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 14
[oracle@localhost ~]$ cat raw1.dat
load data
  append
  into table s13018_SQLLDR_DATA
  fields terminated by "|"
  trailing nullcols
  (
      CATEGORY       CHAR(3),
      REGION         CHAR(36),
      STATE          CHAR(36),
      DISTRICT       CHAR(36),
      ZONE_NAME      CHAR(36),
      BUILDING_ID    CHAR(36),
      TOTAL_COUNT   
  )
[oracle@localhost ~]$

Report message to a moderator

Re: SQLLDR Issue with NUMBER data type [message #614328 is a reply to message #614327]

Tue, 20 May 2014 21:53

aimy
Messages: 225
Registered: June 2006

Senior Member

Thanks.

But I still got the invalid number error:

SQL*Loader: Release 9.2.0.7.0 - Production on Wed May 21 10:52:01 2014

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Control File:   /EDWH-DMT02/script/MISC/S13018_SQLLDR_DATA.ctl
Data File:      TEL_COUNT.csv
  Bad File:     TEL_COUNT.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 123456789
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table S13018_SQLLDR_DATA, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CATEGORY                            FIRST     3   |       CHARACTER
REGION                               NEXT    36   |       CHARACTER
STATE                                NEXT    36   |       CHARACTER
DISTRICT                             NEXT    36   |       CHARACTER
ZONE_NAME                            NEXT    36   |       CHARACTER
BUILDING_ID                          NEXT    36   |       CHARACTER
TOTAL_COUNT                          NEXT     *   |       CHARACTER

Record 1: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number

Record 2: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number

Record 3: Rejected - Error on table S13018_SQLLDR_DATA, column TOTAL_COUNT.
ORA-01722: invalid number

Report message to a moderator

Re: SQLLDR Issue with NUMBER data type [message #614331 is a reply to message #614328]

Tue, 20 May 2014 22:30

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

I don't know what to tell you.
It works for me.
Perhaps a bug that was fix after V9

[oracle@localhost ~]$ cat raw1.log

SQL*Loader: Release 11.2.0.2.0 - Production on Tue May 20 20:16:26 2014

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Control File:   raw1.ctl
Data File:      raw1.dat
  Bad File:     raw1.bad
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table S13018_SQLLDR_DATA, loaded from every logical record.
Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
CATEGORY                            FIRST     3   |       CHARACTER            
REGION                               NEXT    36   |       CHARACTER            
STATE                                NEXT    36   |       CHARACTER            
DISTRICT                             NEXT    36   |       CHARACTER            
ZONE_NAME                            NEXT    36   |       CHARACTER            
BUILDING_ID                          NEXT    36   |       CHARACTER            
TOTAL_COUNT                          NEXT     *   |       CHARACTER            


Table S13018_SQLLDR_DATA:
  6 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                  29056 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             6
Total logical records rejected:         0
Total logical records discarded:        0

Run began on Tue May 20 20:16:26 2014
Run ended on Tue May 20 20:16:27 2014

Elapsed time was:     00:00:01.10
CPU time was:         00:00:00.17
[oracle@localhost ~]$

Report message to a moderator

Re: SQLLDR Issue with NUMBER data type [message #614334 is a reply to message #614324]

Tue, 20 May 2014 23:20

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

Your original control file with your posted data and table works for me as well. However, I have seen similar problems before. Typically, there is some sort of invisible control character appended to the number, so that it is not recognized as a number. This frequently happens at the end of a line, due to differences in end of line characters between different operating systems. One method of checking this is to change your data type in your table to varchar2 and in your control file to char, confirm that it loads that way, then use dump to see what was loaded and look for the extra characters. Once you have confirmed that, then you can add code in your control file to trim the characters and put your data types back to number and integer external. This may or may not be the problem, but I strongly suspect it is.

Report message to a moderator

Re: SQLLDR Issue with NUMBER data type [message #614335 is a reply to message #614334]

Tue, 20 May 2014 23:30

aimy
Messages: 225
Registered: June 2006

Senior Member

You are totally right!

I save the file again using UNIX format and now only it works even without have to specify the data type format in the control file!

No wonder the problem seems so weird. Luckily I asked in this forum. If not, I'm going crazy to think about this.

Thanks a lot.

Report message to a moderator