OraFAQ Forum: Server Utilities

Home » RDBMS Server » Server Utilities » SQL loader (oracle 10g)

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

SQL loader [message #593618]

Mon, 19 August 2013 05:06

satheeshsharma
Messages: 11
Registered: August 2013
Location: Chennai

Junior Member

This is my dat.file

200100 	1000.00 600.00 	08-JAN-08 	C00015 	A003 	SOD
200110 	3000.00 500.00 	15-APR-08 	C00019 	A010 	SOD
200107 	4500.00 900.00 	30-AUG-08 	C00007 	A010 	SOD
200112 	2000.00 400.00 	30-MAY-08 	C00016 	A007 	SOD
200113 	4000.00 600.00 	10-JUN-08 	C00022 	A002 	SOD
200102 	2000.00 300.00 	25-MAY-08 	C00012 	A012 	SOD
200114 	3500.00 1500.00 15-AUG-08 	C00002 	A008 	SOD
200122 	2500.00 400.00 	16-SEP-08 	C00003 	A004 	SOD
200118 	500.00 	100.00 	20-JUL-08 	C00023 	A006 	SOD
200119 	4000.00 700.00 	16-SEP-08 	C00007 	A010 	SOD
200121 	1500.00 600.00 	23-SEP-08 	C00008 	A004 	SOD
200130 	2500.00 400.00 	30-JUL-08 	C00025 	A011 	SOD
200134 	4200.00 1800.00 25-SEP-08 	C00004 	A005 	SOD
200115 	2000.00 1200.00 08-FEB-08 	C00013 	A013 	SOD
200108 	4000.00 600.00 	15-FEB-08 	C00008 	A004 	SOD
200103 	1500.00 700.00 	15-MAY-08 	C00021 	A005 	SOD
200105 	2500.00 500.00 	18-JUL-08 	C00025 	A011 	SOD
200109 	3500.00 800.00 	30-JUL-08 	C00011 	A010 	SOD
200101 	3000.00 1000.00 15-JUL-08 	C00001 	A008 	SOD
200111 	1000.00 300.00 	10-JUL-08 	C00020 	A008 	SOD
200104 	1500.00 500.00 	15-MAR-08 	C00006 	A004 	SOD
200106 	2500.00 700.00 	20-APR-08 	C00005 	A002 	SOD
200125 	2000.00 600.00 	16-OCT-08 	C00018 	A005 	SOD
200117 	800.00 	200.00 	20-OCT-08 	C00014 	A001 	SOD
200123 	500.00 	100.00 	16-SEP-08 	C00022 	A002 	SOD
200120 	500.00 	100.00 	20-JUL-08 	C00009 	A002 	SOD
200116 	500.00 	100.00 	13-JUL-08 	C00010 	A009 	SOD
200124 	500.00 	100.00 	20-JUN-08 	C00017 	A007 	SOD
200126 	500.00 	100.00 	24-JUN-08 	C00022 	A002 	SOD
200129 	2500.00 500.00 	20-JUL-08 	C00024 	A006 	SOD
200127 	2500.00 400.00 	20-JUL-08 	C00015 	A003 	SOD
200128 	3500.00 1500.00 20-JUL-08 	C00009 	A002 	SOD
200135 	2000.00 800.00 	16-SEP-08 	C00007 	A010 	SOD
200131 	900.00 	150.00 	26-AUG-08 	C00012 	A012 	SOD
200133 	1200.00 400.00 	29-JUN-08 	C00009 	A002 	SOD
200132 	4000.00 2000.00 15-AUG-08 	C00013 	A013 	SOD

This is my control file

load data 
infile 'F:\sat.csv'
into table orders
fields terminated by ' '
(ord_number,ord_amount,advanced_amount,ord_date,cust_code,agent_code,ord_description)

When i am loading data i m getting error like this


C:\Documents and Settings\Administrator>sqlplus one/two

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 19 15:31:03 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc orders
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORD_NUMBER                                         NUMBER
 ORD_AMOUNT                                         NUMBER(8,2)
 ADVANCED_AMOUNT                                    NUMBER(8,2)
 ORD_DATE                                           DATE
 CUST_CODE                                          VARCHAR2(10)
 AGENT_CODE                                         VARCHAR2(10)
 ORD_DESCRIPTION                                    VARCHAR2(10)

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>notepad me.ctl

C:\Documents and Settings\Administrator>sqlldr one/two me.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 19 15:32:00 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 35
Commit point reached - logical record count 36

C:\Documents and Settings\Administrator>exit

SQL> select * from orders;

no rows selected

This is my log file

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 19 15:32:00 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   me.ctl
Data File:      F:\sat.csv
  Bad File:     sat.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 ORDERS, loaded from every logical record.
Insert option in effect for this table: INSERT

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ORD_NUMBER                          FIRST     *  WHT      CHARACTER            
ORD_AMOUNT                           NEXT     *  WHT      CHARACTER            
ADVANCED_AMOUNT                      NEXT     *  WHT      CHARACTER            
ORD_DATE                             NEXT     *  WHT      CHARACTER            
CUST_CODE                            NEXT     *  WHT      CHARACTER            
AGENT_CODE                           NEXT     *  WHT      CHARACTER            
ORD_DESCRIPTION                      NEXT     *  WHT      CHARACTER            

Record 1: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 2: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 3: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 4: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 5: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 6: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 7: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 8: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 9: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 10: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 11: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 12: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 13: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 14: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 15: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 16: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 17: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 18: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 19: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 20: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 21: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 22: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 23: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 24: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 25: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 26: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 27: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 28: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 29: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 30: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 31: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 32: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 33: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 34: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 35: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 36: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number


Table ORDERS:
  0 Rows successfully loaded.
  36 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:                 115584 bytes(64 rows)
Read   buffer bytes: 1048576

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

Run began on Mon Aug 19 15:32:00 2013
Run ended on Mon Aug 19 15:32:01 2013

Elapsed time was:     00:00:00.31
CPU time was:         00:00:00.11

I dont know why i m getting datas not loaded... can anyone point my mistake.

Report message to a moderator

Re: SQL loader [message #593620 is a reply to message #593618]

Mon, 19 August 2013 05:24

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What about posting your control file?
And what is your "nls_numeric_characters" parameter value?

Regards
Michel

[Updated on: Mon, 19 August 2013 05:26]

Report message to a moderator

Re: SQL loader [message #593625 is a reply to message #593620]

Mon, 19 August 2013 06:18

satheeshsharma
Messages: 11
Registered: August 2013
Location: Chennai

Junior Member

This is the value:

SQL> select * from nls_database_parameters where PARAMETER='NLS_NUMERIC_CHARACTERS';

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_NUMERIC_CHARACTERS         .,

Report message to a moderator

Re: SQL loader [message #593627 is a reply to message #593625]

Mon, 19 August 2013 06:24

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

fields terminated by ' '

200132 	4000.00

A space and a tab between the 2 values, so the amount value starts by a tab which is not allowed in a number.

==> You data file does not match your control file.

Regards
Michel

[Updated on: Mon, 19 August 2013 06:25]

Report message to a moderator

Re: SQL loader [message #593629 is a reply to message #593627]

Mon, 19 August 2013 06:32

satheeshsharma
Messages: 11
Registered: August 2013
Location: Chennai

Junior Member

Then can you show me the correct control file...
I dont know how to write correct control file for this...
Once if u show the correct control file,i will learn from it and it will be more useful for me.

[Updated on: Mon, 19 August 2013 06:34]

Report message to a moderator

Re: SQL loader [message #593630 is a reply to message #593629]

Mon, 19 August 2013 06:51

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

What is the character between fields space or tab?
It seems to be sometimes a space, sometimes a space AND a tab.
If this is the case, I advise your preprocess the data file to remove all tabs.

Regards
Michel

Report message to a moderator

Re: SQL loader [message #593644 is a reply to message #593630]

Mon, 19 August 2013 12:21

satheeshsharma
Messages: 11
Registered: August 2013
Location: Chennai

Junior Member

If the fields are terminated by tab, show me how to write the control file...
Or if the fields are terminated by space thn how to write the control file for this....

Report message to a moderator

Re: SQL loader [message #593645 is a reply to message #593644]

Mon, 19 August 2013 12:49

satheeshsharma
Messages: 11
Registered: August 2013
Location: Chennai

Junior Member

I formatted the datas using tab....and this is my dat file

200100	1000.00	600.00	08-JAN-08	C00015	A003	SOD
200110	3000.00	500.00 	15-APR-08 	C00019 	A010 	SOD
200107 	4500.00 900.00 	30-AUG-08 	C00007 	A010 	SOD
200112 	2000.00 400.00 	30-MAY-08 	C00016 	A007 	SOD
200113 	4000.00 600.00 	10-JUN-08	C00022 	A002 	SOD
200102 	2000.00 300.00 	25-MAY-08	C00012 	A012 	SOD
200114 	3500.00 1500.00 15-AUG-08 	C00002 	A008 	SOD
200122 	2500.00 400.00 	16-SEP-08 	C00003 	A004 	SOD
200118 	500.00	100.00 	20-JUL-08 	C00023 	A006 	SOD
200119 	4000.00 700.00 	16-SEP-08 	C00007 	A010 	SOD
200121 	1500.00 600.00 	23-SEP-08 	C00008 	A004 	SOD
200130 	2500.00 400.00 	30-JUL-08 	C00025 	A011 	SOD
200134 	4200.00 1800.00 25-SEP-08 	C00004 	A005 	SOD
200115 	2000.00 1200.00 08-FEB-08 	C00013 	A013 	SOD
200108 	4000.00 600.00 	15-FEB-08 	C00008 	A004 	SOD
200103 	1500.00 700.00 	15-MAY-08 	C00021 	A005 	SOD
200105 	2500.00 500.00 	18-JUL-08 	C00025 	A011 	SOD
200109 	3500.00 800.00 	30-JUL-08 	C00011 	A010 	SOD
200101 	3000.00 1000.00 15-JUL-08 	C00001 	A008 	SOD
200111 	1000.00 300.00 	10-JUL-08 	C00020 	A008 	SOD
200104 	1500.00 500.00 	15-MAR-08 	C00006 	A004 	SOD
200106 	2500.00 700.00 	20-APR-08 	C00005 	A002 	SOD
200125 	2000.00 600.00 	16-OCT-08 	C00018 	A005 	SOD
200117 	800.00 	200.00 	20-OCT-08 	C00014 	A001 	SOD
200123 	500.00 	100.00 	16-SEP-08 	C00022 	A002 	SOD
200120 	500.00 	100.00 	20-JUL-08 	C00009 	A002 	SOD
200116 	500.00 	100.00 	13-JUL-08 	C00010 	A009 	SOD
200124 	500.00 	100.00 	20-JUN-08 	C00017 	A007 	SOD
200126 	500.00 	100.00 	24-JUN-08 	C00022 	A002 	SOD
200129 	2500.00 500.00 	20-JUL-08 	C00024 	A006 	SOD
200127 	2500.00 400.00 	20-JUL-08 	C00015 	A003 	SOD
200128 	3500.00 1500.00 20-JUL-08 	C00009 	A002 	SOD
200135 	2000.00 800.00 	16-SEP-08 	C00007 	A010 	SOD
200131 	900.00 	150.00 	26-AUG-08 	C00012 	A012 	SOD
200133 	1200.00 400.00 	29-JUN-08 	C00009 	A002 	SOD
200132 	4000.00 2000.00 15-AUG-08 	C00013 	A013 	SOD

And this is my control file....

load data
infile 'F:\sat.csv'
into table orders
fields terminated by x'09'
trailing nullcols
(ord_number,ord_amount,advanced_amount,ord_date,cust_code,agent_code,ord_description)

when i m loading datas i got only 9 records loaded into the table and the remaining were not loaded..
see my process

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>sqlplus one/two

SQL*Plus: Release 10.2.0.1.0 - Production on Mon Aug 19 23:04:23 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> desc orders
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ORD_NUMBER                                         NUMBER
 ORD_AMOUNT                                         NUMBER(8,2)
 ADVANCED_AMOUNT                                    NUMBER(8,2)
 ORD_DATE                                           DATE
 CUST_CODE                                          VARCHAR2(10)
 AGENT_CODE                                         VARCHAR2(10)
 ORD_DESCRIPTION                                    VARCHAR2(10)

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>notepad me.ctl

C:\Documents and Settings\Administrator>sqlldr one/two me.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 19 23:05:12 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL*Loader-350: Syntax error at line 4.
Expecting "(", found "delimited".
fields delimited by x'09'
       ^

C:\Documents and Settings\Administrator>notepad me.ctl

C:\Documents and Settings\Administrator>sqlldr one/two me.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 19 23:05:34 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 35

C:\Documents and Settings\Administrator>exit

SQL> select * from orders;

ORD_NUMBER ORD_AMOUNT ADVANCED_AMOUNT ORD_DATE  CUST_CODE  AGENT_CODE ORD_DESCRI
---------- ---------- --------------- --------- ---------- ---------- ----------
    200100       1000             600 08-JAN-08 C00015     A003       SOD
    200118        500             100 20-JUL-08 C00023     A006       SOD
    200117        800             200 20-OCT-08 C00014     A001       SOD
    200123        500             100 16-SEP-08 C00022     A002       SOD
    200120        500             100 20-JUL-08 C00009     A002       SOD
    200116        500             100 13-JUL-08 C00010     A009       SOD
    200124        500             100 20-JUN-08 C00017     A007       SOD
    200126        500             100 24-JUN-08 C00022     A002       SOD
    200131        900             150 26-AUG-08 C00012     A012       SOD

9 rows selected.

SQL> truncate table orders;

Table truncated.

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>notepad me.ctl

C:\Documents and Settings\Administrator>sqlldr one/two me.ctl

SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 19 23:09:16 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Commit point reached - logical record count 35
Commit point reached - logical record count 36

C:\Documents and Settings\Administrator>exit

SQL> select * from orders;

ORD_NUMBER ORD_AMOUNT ADVANCED_AMOUNT ORD_DATE  CUST_CODE  AGENT_CODE ORD_DESCRI
---------- ---------- --------------- --------- ---------- ---------- ----------
    200100       1000             600 08-JAN-08 C00015     A003       SOD
    200118        500             100 20-JUL-08 C00023     A006       SOD
    200117        800             200 20-OCT-08 C00014     A001       SOD
    200123        500             100 16-SEP-08 C00022     A002       SOD
    200120        500             100 20-JUL-08 C00009     A002       SOD
    200116        500             100 13-JUL-08 C00010     A009       SOD
    200124        500             100 20-JUN-08 C00017     A007       SOD
    200126        500             100 24-JUN-08 C00022     A002       SOD
    200131        900             150 26-AUG-08 C00012     A012       SOD

9 rows selected.

log file is:


SQL*Loader: Release 10.2.0.1.0 - Production on Mon Aug 19 23:09:16 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   me.ctl
Data File:      F:\sat.csv
  Bad File:     sat.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 ORDERS, loaded from every logical record.
Insert option in effect for this table: INSERT
TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ORD_NUMBER                          FIRST     *  WHT      CHARACTER            
ORD_AMOUNT                           NEXT     *  WHT      CHARACTER            
ADVANCED_AMOUNT                      NEXT     *  WHT      CHARACTER            
ORD_DATE                             NEXT     *  WHT      CHARACTER            
CUST_CODE                            NEXT     *  WHT      CHARACTER            
AGENT_CODE                           NEXT     *  WHT      CHARACTER            
ORD_DESCRIPTION                      NEXT     *  WHT      CHARACTER            

Record 2: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 3: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 4: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 5: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 6: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 7: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 8: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 10: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 11: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 12: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 13: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 14: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 15: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 16: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 17: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 18: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 19: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 20: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 21: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 22: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 23: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 30: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 31: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 32: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 33: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 35: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number

Record 36: Rejected - Error on table ORDERS, column ORD_AMOUNT.
ORA-01722: invalid number


Table ORDERS:
  9 Rows successfully loaded.
  27 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:                 115584 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            36
Total logical records rejected:        27
Total logical records discarded:        0

Run began on Mon Aug 19 23:09:16 2013
Run ended on Mon Aug 19 23:09:16 2013

Elapsed time was:     00:00:00.44
CPU time was:         00:00:00.03

Having doubts while loading datas using tab,whitespace,tab and whitespace

Report message to a moderator

Re: SQL loader [message #593647 is a reply to message #593645]

Mon, 19 August 2013 13:04

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

As I said remove the tab from your data file BEFORE loading it and all will be OK.
And it is useless (and even rude towards me) to start a new topic with the same question.

Regards
Michel

Report message to a moderator

Re: SQL loader [message #593662 is a reply to message #593647]

Mon, 19 August 2013 18:34

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

Senior Member

Just use:

fields terminated by whitespace

That will recognize a space or tab or a combination of space and tab.

Report message to a moderator

Re: SQL loader [message #593679 is a reply to message #593662]

Tue, 20 August 2013 00:21

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The problem is that sometimes is it 1 whitespace (a space) and sometimes 2 whitespaces (a tab and a space).

Regards
Michel

Report message to a moderator

Re: SQL loader [message #593693 is a reply to message #593679]

Tue, 20 August 2013 02:03

satheeshsharma
Messages: 11
Registered: August 2013
Location: Chennai

Junior Member

Dear Michel, anyway sorry for starting a new topic for the same problem and it is not something like rude on u....I just thought if start a new topic, i may get more replies from many users...I will avoid try to avoid these things in future...

Thanks for all the repliers...

And more thing i want to know from me..
Consider that i am having 3 columns e.g (ID,Name,salary)
If i am havind NULL value for the second column(Name) i.e something like this [ 101 null 10000]
thn is it correct to use (FILEDS TERMINATED BY WHITESPACE) here in control file...

dat file


101 null 10000
102      20000

Here 2nd column for the both record is null..What is the control file for this...

Report message to a moderator

Re: SQL loader [message #593763 is a reply to message #593679]

Tue, 20 August 2013 12:46

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

Senior Member

Michel Cadot wrote on Mon, 19 August 2013 22:21

The problem is that sometimes is it 1 whitespace (a space) and sometimes 2 whitespaces (a tab and a space).

Regards
Michel

Michel,

I believe you have misunderstood the definition of whitespace. A tab and a space constitutes one whitespace. The documentation explains it this way:

TERMINATED BY WHITESPACE

... SQL*Loader scans past all whitespace at the end of the previous field until it finds a nonblank, nontab character.

Report message to a moderator

Re: SQL loader [message #593765 is a reply to message #593693]

Tue, 20 August 2013 12:59

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

Senior Member

satheeshsharma wrote on Tue, 20 August 2013 00:03

Dear Michel, anyway sorry for starting a new topic for the same problem and it is not something like rude on u....I just thought if start a new topic, i may get more replies from many users...I will avoid try to avoid these things in future...

Thanks for all the repliers...

And more thing i want to know from me..
Consider that i am having 3 columns e.g (ID,Name,salary)
If i am havind NULL value for the second column(Name) i.e something like this [ 101 null 10000]
thn is it correct to use (FILEDS TERMINATED BY WHITESPACE) here in control file...

dat file
101 null 10000
102      20000
Here 2nd column for the both record is null..What is the control file for this...

In the example that you have given, you could not use terminated by whitespace, because it would see all of the spaces between 102 and 20000 as one whitespace and try to load the 20000 into the name column instead of the salary column. In a situation like this you can specify individual terminators for each field, such as space or tab. However, you need to make sure that those terminators are not used in the data. If your example is accurate, then your terminator is a space, but you also have 4 spaces within the second column in the last row, so that won't work. If your example is not exact and that second column is actually a tab or nothing, then it will load fine with the space as a delimiter for that field and the field before, like:

(id terminated by ' ',
name terminated by ' ',
salary terminated by whitespace)

[Updated on: Tue, 20 August 2013 13:01]

Report message to a moderator

Re: SQL loader [message #593768 is a reply to message #593763]

Tue, 20 August 2013 13:14

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I believe you have misunderstood the definition of whitespace.

Sure, I did, thanks to correct me.

Regards
Michel

Report message to a moderator

Previous Topic:	SQLLOADER value too large for column
Next Topic:	sqlloader detect invisible characters

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Mar 03 16:34:41 CST 2025