We are bringing a new project in house and whereas previously all our work was on SQL Server the new product uses an oracle back end.
Can anyone advise any crib sheets or such like that gives an SQL Server person like me a rundown of what the major differences are - Would like to be able to get up and running as soon as possible.
Watch out for the difference in the way the empty string is treated.
INSERT INTO atable (a_varchar_column) VALUES (''); is the same as
INSERT INTO atable (a_varchar_column) VALUES (NULL);
(I have no sqlserver experience, but I understand that it differentiates between the two)
The main difference I noticed in moving from SQL Server to Oracle was that in Oracle you need to use cursors in the SELECT statements. Also, temporary tables are used differently. In SQL Server you can create one in a procedure and then DROP it at the end, but in Oracle you're supposed to already have a temporary table created before the procedure is executed.
I'd look at datatypes too since they're quite different.
If you need to you can create and drop temporary tables in procedures using the Execute Immediate command.
@hamishmcn
Generally that's a bad idea.. Temporary tables in oracle should just be created and left (unless its a once off/very rarely used). The contents of the temporary table is local to each session and truncated when the session is closed. There is little point in paying the cost of creating/dropping the temporary table, might even result in clashes if two processes try to create the table at the same time and unexpected commits from performing DDL.
@hamishcmcn
Your assertion that '' == Null is simply not true. In the relational world Null should only ever be read to mean "I don't know". The only result you will get from Oracle (and most other decent databases) when you compare a value to Null is 'False'.
Off the top of my head the major differences between SQL Server and Oracle are:
Everyone else, please help me out and add more.
What you have asked here is a huge topic, especially since you haven't really said what you are using the database for (eg, are you going to be going from TSQL -> PL/SQL or just changing the backend database your java application is connected to?)
If you are serious about using your database choice to its potiential, then I suggest you dig a bit deeper and read something like Expert Oracle Database Architecture: 9i and 10g Programming Techniques and Solutions by Tom Kyte.
String concatenation:
Oracle: || or concat()
Sql Server: +
These links could be interesting:
http://www.dba-oracle.com/oracle_news/2005_12_16_sql_syntax_differences.htm
http://www.mssqlcity.com/Articles/Compare/sql_server_vs_oracle.htm (old one: Ora9 vs Sql 2000)
to andy47, I did not mean that you can use the empty string in a comparison, but oracle treats it like null if you use it in an insert. Re-read my entry, then try the following SQL:
CREATE TABLE atable (acol VARCHAR(10));
INsERT INTO atable VALUES( '' );
SELECT * FROM atable WHERE acol IS NULL;
And to avoid a "yes it is, no it isn't" situation, here is an external link
This link provides a useful summary of lots of the main differences: Differences between oracle and ms sql server