Unanswered: SQL Server to Oracle Migration Question(s)
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.
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:
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.