|
|
|
|
|
|
|
Re: Have to replace ? with blank space when loading data from csv to oracle [message #291020 is a reply to message #290980] |
Wed, 02 January 2008 14:11 |
|
Littlefoot
Messages: 21823 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
vanathi wrote on Wed, 02 January 2008 15:03 | blank space only.
|
What will you do when '?' comes in a place that represents a table column whose datatype is NUMBER?
See an example: this is a control file:load data
infile *
replace
into table test
( col_n integer external (10) "replace(:col_n, '?', ' ')" )
begindata
42
?
1889 Run the test:SQL> create table test (col_n number(10));
Table created.
SQL> $sqlldr scott/tiger control=test.ctl log=test.log
SQL*Loader: Release 10.2.0.1.0 - Production on Sri Sij 2 21:07:04 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 2
Commit point reached - logical record count 3
SQL> select * from test;
COL_N
----------
42
1889
SQL> An excerpt of the log file:Quote: |
SQL string for column : "replace(:col_n, '?', ' ')"
Record 2: Rejected - Error on table TEST, column COL_N.
ORA-01722: invalid number
Table TEST:
2 Rows successfully loaded.
1 Row 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.
|
So, are you sure you don't want a NULL instead?
|
|
|
|
|
|
|
|