Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    4

    Post Unanswered: Migrating from SQL Server to Oracle

    Hi All,

    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'?

    Regards
    Vinod

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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.

  3. #3
    Join Date
    Aug 2003
    Posts
    4
    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.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    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, '').

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •