Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Unanswered: SQL Server to Oracle Migration Question(s)

    Greetings all.
    I'm on a sql server to oracle migration project at the moment and I've got a few relatively simple "best-practice" questions.

    Firstly, a few of the tables in SQL server are of the 'datetime' data type. I'm piping the data in through SQL Loader, however when I dump the tables into text files - the date time comes out like "001-05-02 11:30:32.923000000"
    As opposed to "5/2/2001 11:30:33 AM" - which is how it looks while in SQL server - is there an easy fix for this?

    Second, since there is no boolean datatype for Oracle - what do you guys normally use? I ask because there are a number of tables in SQL server of datatype "bit" and Im curious as to what I'd be best off converting them to...

    Okay - that's all the q's I've got for now, but give me some time and I'm sure I'll have a few more - Thank you in advance.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Can't answer your first question - presumably this is something to do with specifying a format in the data dumping script?

    Regarding Boolean datatype: since Oracle offers no Boolean datatype for columns, you have to decide on a convention that suits you. Probably the most common implementation of Boolean in Oracle is VARCHAR2(1) with the constraint "CHECK (col IN ('Y','N'))"; but if the SQL Server datatype "bit" has values 0 and 1, then the nearest equivalent is NUMBER(1), with the constraint "CHECK (col IN (0,1))".

    One big "gotcha" to be aware of in Oracle, is that the empty string ('') is treated as a null (in contravention of the SQL standard!), so any query like this one will never return a record:

    select * from people where middle_name = '';

    You would have to change to:

    select * from people where middle_name is null;

  3. #3
    Join Date
    Jun 2003
    Posts
    294
    I'm gonna give you a suggestion, why don't you try to connect Oracle with SQL Server, then you will be able to read SQL server tables from oracle (SQL PLUS) and extract all the information !!!!!

  4. #4
    Join Date
    Jun 2004
    Posts
    5
    Thanks for the suggestions guys - I'm looking into connect the DB's as we speak - actually I tried it but I got an TNS no listener error so I'm confirming that service is on, no the specified box.

    I do have some questions concerning the "copy from.." command (assumnig that is what you were referring to..)
    For example, as I mentioned in my initial post there are numerous tables in the SQL Server DB with the "bit" datatype - If I copy these tables into oracle via a connected DB what happens to these datatypes? Will oracle take care of the datatypes (ie, bit and datetime datatypes in SQL Server)
    Any other concerns with taking this approach?

    Sorry for all the questions but I am a bit new to the oracle world, and once again - thank you, all advice is greatly appreciated.

  5. #5
    Join Date
    Jun 2003
    Posts
    294
    I don't really know what is gonna go on, with those data types, but what I'm really sure is that It is not so dificult, send me an e-mail and I'll send you an easy example: jortiz@avansoft.com

Posting Permissions

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