We are working on data migration from SQL Server to Oracle. I'm getting a dump from SQL Server using BCP and loading it into Oracle using SQLLDR.
SQL Server distinguishes between null and blank string so BCP puts "\0' character as substitute for blank string in the outfile. SQLLDR takes this char and loads it into Oracle. Oracle treats blank string and null as same. So if I fire a sql to fetch the record wiht empty string (fld = ''), I don't get that row at all.
Is there anyway I can ask BCP to put null if its empty string OR SQLLDR puts NULL if it encounters '\0'?
Originally posted by rdjabarov
SQLLDR loads all empty strings as NULL. When you query the resulting table with WHERE FLD = '', you should not get any rows back. Instead, just like in MSSQL, you need to use WHERE FLD IS NULL.
No it doesn't. Empty string representation by BCP is a character having ASCII value 0. SQLLDR loads it as a string having one character instead of NULL. So sQLLDR doesn't recognize it as EMPTY string. Thats the issue.
Well, as I said, SQLLDR loads empty strings as NULL. Your BCP produced \0 for NULL and SQLLDR thinks it's a string. If you want a null there, create a view off of the table you're trying to BCP out, and put isnull(field_that_can_have_null_values, '').