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

    Unanswered: Data migration 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

    (I had posted it under SQL Server thread but thought it may help if I post it here as well)

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    One way of handling the '\0' is to run your BCP file through a utility like awk or sed to replace all occurences of '\0' with ''.

    The other thing to point out is that Oracle trats nulls differently from SQL Server (or Sybase) in that you cant say fld='' or fld=null as null is an unknown value (so you cant test equality). What you have to say is fld is null instead.

    Alan

    P.S. Have a look at this excellent article by Tom Kyte for more info on some of the other significant differences between Oracle and other databases.

    http://www.databasejournal.com/featu...le.php/1403591

    There is also a Pt 2 on the site

  3. #3
    Join Date
    Aug 2003
    Posts
    4
    Originally posted by AlanP
    One way of handling the '\0' is to run your BCP file through a utility like awk or sed to replace all occurences of '\0' with ''.

    The other thing to point out is that Oracle trats nulls differently from SQL Server (or Sybase) in that you cant say fld='' or fld=null as null is an unknown value (so you cant test equality). What you have to say is fld is null instead.

    Alan

    P.S. Have a look at this excellent article by Tom Kyte for more info on some of the other significant differences between Oracle and other databases.

    http://www.databasejournal.com/featu...le.php/1403591

    There is also a Pt 2 on the site
    Thanks Alan for the effective soln. AWK/SED option won't take much time..

Posting Permissions

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