I have a dev server at home with IIS6 and MS SQL, but my new "day job" uses Oracle for their database. I've been having a heck of a time getting a lot of queries to work because of the differences between the two.

I'd like to set up Oracle at home, but I can't find anything on their site about if a "demo" or "personal" version is available. Is there such a monster?

Any recommendations on books or other references on an old MS SQL programmer moving over to Oracle?

http://www.oracle.com/technology/pro.../xe/index.html

DB size is limited to 4Gb other than that, it's the same base system.

You can also download the full (enterprise) version of their flagship from Oracle.com and use a developer or educational license. What you described is perfect for that. You aren't allowed to use it to run a business, but they make it available so more people will become versed in their product. Getting Oracle Networking to work is a bear, tho.

I think the link Chris gave you is easier to get working.

(You should give him some TP for it. )

Thanks, I got the express version up and running and it's good enough to run oracle style queries which is what I wanted. Now I just need to find some documentation on the differences between MS and Oracle queries.

Somewhere in this thread is a link to a good one. Tripy is the resident expert in Oracle here, I think.

Among some of the key (Sql) differences are:

1. The Case statement (MSSQL) is replaced by DECODE() function
2. GETDATE() in Oracle is SYSDATE.
3. Oracle has no "date-only" native data type; all DATE types are date and time.
4. You must use table aliases if you are using abstract data types in Oracle.

Those are just a few off the top of my head.

Thanks John, but I'm far of an expert...

Some things else that comes to my mind are:
1) the CONNECT BY PRIOR does not have any equivalent in sql server (at least before 2008)
2) the non standard outer join syntax like this:

select x,y,z
from users u, attr a
where u.id=a.id(+)

is not supported in ms sql server. You have to specify the complete query

select x, y,z
from users u
left outer join attr a on u.id=a.id

3) The notion of package don't exists in sql server. You have stored procedures, scalar type functions and table type functions, but they are not grouped in packages like in Oracle

Otherwise, not much comes to my mind.
Maybe some functions or data types differences.

You can use SQL 92 style joins in SQL Server, you just don't have (+) and such. This is perfectly valid

Select *
From sysObjects O, sysIndexes I
Where O.id = I.id

A really nice trick in Oracle is Join Using, where you don't have to spell out that the same column in both tables, only the column itself, is how the join should work.

Oracle has Create Or Alter syntax, where you have to pick one or the other in SQL Server.