If you’ve not guessed yet, I’ve done allot of work in Microsoft technologies. It’s strange but I didn’t realize how much I’d come to like Microsoft apps until I started using the competitions. Here are a few of the differences between Oracle and SQL Server that I’ve found so far. I’ll add more as I stumble on / think of them.
SQL Server: @
If you’re creating paramertized SQL statements in SQL Server the statement looks like this:
select * from table where parm = @inParm
in Oracle its:
select * from table where parm = :inParm
SQL Server: select GetDate()
Oracle: select sysdate from dual
To get the date in SQL Server it’s just a call to the GetDate() function, In Oracle you have to select things like this from ‘dual’, this applies to a number of things. In general if in SQL Server the sql leaves out the ‘from’ statement in Oracle add ‘from dual’
SQL Server: F5
Now this isn’t necessarily an Oracle ism, but that’s because Oracle doesn’t have a reasonably priced query executer like SQL Server does. In SQL Express F5 runs a SQL statement, in Oracle (or Oracle SQL Developer which is what I’m using) it’s F9.
SQL Server: null is null and ” is an empty string
Oracle: null is null and ” is also null
This is one of my biggest beefs with Oracle. Oracle treats an empty string as a null. So if you set a field in Oracle to ” and then read it back (in .Net code) ” will not equal the field. Joy. This is good fun when you’re designing databases and would like to not have to code for nulls. In SQL server you could set the field as ‘not null’ and in code just treat it as a string, avoiding all the aggravation of testing if IsNull(), or = System.DbNull.Value. This isn’t possible in Oracle, instead you have to pad all 0 length strings as ‘ ‘, which would be wrong since a single space isn’t a valid value. Ah magic numbers, you have to love them.
SQL Server: Select column1, column2, * from myTable
Oracle: Select column1 as c1, column2 as c2, myTable.* from myTable
This is a peculiar one. You really shouldn’t ever use * in production code, but when testing it’s very useful. In SQL Server if you want to get a couple of columns first and everything after all you have to do is say the columns and then *. In Oracle that generates a ambiguous column error. Oracle is treating the named column and the same column included by the * as using the same name and thus an error (ambiguous). While this is technically correct, it’s annoying.
More to come, I still need to talk about dates, varchars, stored procedures, the list is loooong.