2

I am working on moving data from MySQL to Oracle. The MySQL input datasets have been provided via a MySQL data dump. Null values in the MySQL database were written as "\N" (without the quotes) in the output file.

I am using sqlldr to get the data into Oracle and "\N" values are problematic in columns mapped to NUMBER data type because Oracle thinks they are strings.

How do I tell sqlldr that any \N values in the input dataset should be mapped to Nulls in Oracle?

Thanks.

user9074332
  • 2,336
  • 2
  • 23
  • 39

2 Answers2

0

You can use NULLIF in the control file. It will assign null if finds \N in that column. See syntax below.

<COLUMN_NUMBER> NULLIF <COLUMN_NUMBER> = '\\N'
jose_bacoy
  • 12,227
  • 1
  • 20
  • 38
  • Thanks for the feedback. This helped get me started in the right direction but didnt work exactly as described. I did get it working with some slight modifications. – user9074332 Apr 06 '18 at 04:18
  • you can post what worked for you then accept that answer. if you like my answer you can also upvote it. – jose_bacoy Apr 06 '18 at 11:06
0

This is what worked for me. Note that if you are on unix-based systems, the \N will need to be escaped as follows:

...
COLUMN_NM CHAR(4000) NULLIF COLUMN_NM='\\N',
...
user9074332
  • 2,336
  • 2
  • 23
  • 39