Oracle (PL/SQL) Equivalents for MS SQL Server (T-SQL) Constructs

For the last several months, I have been working primarily with Oracle 8i and 10g databases.  Over the last 10 years, 95% of my work has been with MS SQL server.  The other 5% was with Oracle, but it was limited to simple SELECT statements or calling stored procedures that other people wrote.  You don’t realize how much basic knowledge you take for granted until you get outside of your comfort zone and have to stop and figure out how to do every little thing.  Here’s a list of the top 10 things I wish I had known about Oracle – PL/SQL coming from an MS SQL Server – T-SQL background.

1. Bind variables versus PL/SQL variables instead of just variables.  Oversimplifying, use “:” in place of “@” when you want a parameterized query.  This is the most awkward and annoying aspect of Oracle compared to MS SQL Server, IMO.  The bottom line is that you can’t very easily copy code from a stored procedure and run it in Toad or SQL Developer.  Nor can you directly assign values to bind variables — you get prompted for them.
2. You can’t do a SELECT without an INTO inside of a PL/SQL block.  This is really puzzling.  Especially since you can’t have anything but bind variables outside of a PL/SQL block and you can’t programmatically assign values to bind variables.  All of this adds up to being forced to be prompted for bind variable values every time you run a parameterized query in the SQL Worksheet or in Toad.
3. There’s no built-in Query Analyzer/SSMS Query tool.  There’s an extremely crippled but free version of Toad.  This what I use if I have to work with a pre-10g instance.  There’s Oracle SQL Developer which is what I use if I am working with a 10g instance.  They both are lacking compared to Query Analyzer/SSMS Query window.  You will press ctrl-E and ctrl-R repeatedly with no effect so many times it will make you sick.
4. DATEADD doesn’t exist.  You have to add fractional days to dates instead.  DATEADD(hour, 1, D) would be D + 1/24.
5. INT can’t be used for columns.  You use NUMBER instead.  This can be a real pain in ADO.NET because you have to explicitly convert all those NUMBERs to ints unless you want to treat them as decimals.
6. No IDENTITY columns.  Instead of typing that one word you have to create a completely separate object called a SEQUENCE and you also have to write a trigger.
7. You can’t have multiple databases on an Oracle instance.  There are just multiple schemas which are really owners.  Public synonyms are used to make objects from different schemas accessible without explicit schema specification. 
8. The default date format does not include the time part.  You have to “alter session set NLS_DATE_FORMAT=’MM/DD/YYYY HH24:MI’;” to see the time part.
9. You concatenate strings with the oh-so-intuitive || instead of the obscure + operation.  Alternatively you can use CONCAT.
10. You can’t PRINT.  There’s a print-like statement, but it only works in SQL*PLUS.  You don’t ever really want to use SQL*PLUS unless you are a massochistic, command-line-loving freak from the past that still uses VI or EMACS.  Actually, there’s a web-based version of SQL*PLUS that’s usable but I like to stay in the SQL Worksheet.

Those are the top 10 — the things I really struggled to understand coming from the MS SQL side of things.  Here are some more that I found useful:

• SP_TABLES = SELECT * FROM ALL_TABLES
• sp_help = DESCRIBE
• You can’t just SELECT without a FROM clause.  You always have to include FROM DUAL.  I still don’t know what “DUAL” is.
• GETDATE() = SYSDATE
• SUSER_SNAME() = USER
• Getting just the date part of a date value: CONVERT(datetime, CONVERT(varchar(10), GETDATE(), 101)) = trunc(SYSDATE)
• Just use VARCHAR2 and don’t ask about VARCHAR.
• You can’t return values from procedures using the RETURN statement.  Use an OUT param instead.
• In version 8i and earlier, there’s a “rule-based” optimizer.  The rule-based optimizer can suck badly and you may need to force the join order on some of your queries.  You do that with “SELECT /*+ ordered */” (yes, it really is in the comment).
• The Oracle compiler doesn’t do a good job of telling what’s wrong with your statement.  It can give obscure messages for missing commas and semi-colons.  Don’t forget that you must terminate each statement with a semi-colon.
• There’s no built-in support for UUIDs.
• Oracle does not distingish between an empty string and null (which really sucks)

Here are a couple of non-obvious things I learned about Oracle and .NET 2.0:

• You can’t send multiple SQL statements in a batch using ADO.NET like you can with MSSQL Server
• You can’t use the ODP.NET with the Enterprise Library
• You can’t use non-tnsnames.ora connection strings with System.Data.Oracle but you can with ODP