Data Type Conversion from SQL Server to Oracle
I am currently moving a product from SQL Server to Oracle. I'm a little familiar with SQL Server and don't know anything about Oracle, so I apologize if the mere presence of this question offends anyone.
Output from this page http://download.oracle.com/docs/cd/E12151_01/doc.150/e12156/ss_oracle_compared.htm it looks like the datatype conversion from SQL Server to Oracle should be:
REAL = FLOAT (24) → FLOAT (63)
FLOAT (p) → FLOAT (p)
TIMESTAMP → NUMBER
NVARCHAR (n) → VARCHAR (n * 2)
NCHAR (n) → CHAR (n * 2)
Here are my questions regarding them:
For FLOAT, given that FLOAT (p) → FLOAT (p), doesn't that also mean FLOAT → FLOAT (24)?
For TIMESTAMP, since Oracle also has its own version, isn't TIMESTAMP -> TIMESTAMP better?
Finally, for NVARCHAR (n) and NCHAR (n), I thought the problem would be Unicode related. Then, again, since Oracle provides its own version of both, doesn't it make sense that NVARCHAR (n) -> NVARCHAR (n) and NCHAR (n) -> NCHAR (n)?
It would be very grateful if someone would elaborate on the previous three questions.
Thanks in advance.
a source to share
It looks like Oracle CHAR and VARCHAR2 (always use VARCHAR2 instead of VARCHAR) already support Unicode - the document you linked to advises converting it to SQL Server's NCHAR and NVARCHAR data types.
SQLEST TIMESTAMP is not really a timestamp - it is some kind of time-based identifier that is simply used to indicate that the string has changed - it cannot be converted back to any kind of DATETIME (at least so , as I know).
For FLOAT, the use of 126 bytes would be huge - since the developer tools will automatically map SQL Server FLOAT to Oracle FLOAT (53), why not use that amount?
a source to share
This is more FYI than an answer to your question, but you will potentially run into a particularly painful difference between SQL Server and Oracle. In SQL Server, you can define a column of strings (of any taste) to not allow null values, and then insert zero-length strings (aka "empty" strings) into that string because SQL Server does not treat an empty string as NULL.
Oracle considers an empty string to be the same as NULL, so Oracle will not let you insert empty values into NOT NULL columns. This obviously causes problems when copying data from a table in SQL Server to its parallel table in Oracle. You can solve the following problems:
- Set the offending row column in Oracle to allow NULL values (so not a good idea).
- When copying data, replace blank lines with something else (I have no idea what you should be using here)
- Skip the offensive lines and pretend you haven't seen them.
I would like to think that Oracle's choice for empty strings to be NULL (where they were one of the main DBs) was to block clients on their platform, but this one actually works the other way around. You can move the database from Oracle to something else no matter the empty field = NULL causing any problems.
See this earlier question: Oracle thinks empty strings should be NULL while SQL Server should not - how is this best handled?
a source to share
Below is not a direct answer to your question; but it's good to take a look at sqlteam blog
sqlteam - Broadcasting Data Between Oracle and SQL Server Part 2: Number
It details how to handle numbers
, etc.
a source to share