0

I am using Oracle's SQL*Loader to import flat files into the database. Is there an explicit NULL string in SQL*Loader, like \N in PostgreSQL, that can be used instead of an empty string? Or is there an option in the control file that can be used to set a NULL string, e.g. NULL AS ''?

Sarathi Kamaraj
  • 647
  • 3
  • 9
  • 26
Adrian
  • 1,837
  • 1
  • 20
  • 21
  • 1
    `null` and the empty string are the same thing in Oracle. If your data is comma-separated and you have a field as `...,,...` then that will be inserted as `null`. If you want something other than `null` to be inserted in that case then there are options for that. I'm not sure what you're actually trying to do though. What does your file data look like and what do you want to happen? – Alex Poole Sep 18 '13 at 10:48
  • I want to distinguish an empty field (string) in a flat file that I am going to load with SQL*Loader from a true NULL value. The empty string should then be loaded as empty string into the database as well and **not** as NULL. – Adrian Sep 18 '13 at 10:54
  • 4
    But Oracle makes no distinction between an empty string and `null`. If you `insert into table values ('')` or `insert into table values(null)` the records will be identical; both would match on `is null` (and neither on `= ''`). – Alex Poole Sep 18 '13 at 10:56

1 Answers1

0

Like Alex said, empty fields are treated as NULL in Oracle. You may need to set default values or conditions on your table itself if you want to differentiate somehow. You might be able to find a solution to your issue from here using the WHEN, NULLIF, and DEFAULTIF Clauses.

Ex from the docs:

fixed_field CHAR(2) NULLIF fixed_field=BLANKS

But ultimately, I don't know how you differentiate between empty fields and null fields in a flat fixed-width file. Either the data is present or its position filled with white space. Unless you have specific rules that you're going to apply to make that determination, I don't see a difference between the two.

Emma
  • 277
  • 1
  • 10
  • I realized now that Oracle does not distinguish between empty strings and NULL values. Other database systems use a specific character to make that distinction, e.g. `\N` in PostgreSQL - that is why I asked. – Adrian Sep 18 '13 at 15:20