Top 5 Oracle Nuances I learned Today

I don’t do much with Oracle – at all.  Once in a blue moon, I find a little project to do that might involve Oracle.  I have never put a lot of thought to the differences between SQL and Oracle.  On the pet project I am doing right now, I put a little more thought into those differences and finally decided to write a little something about five things I am working with in the Oracle world and how those translate (or at least how I translated them) to the SQL world.

Let’s start with some very similar commands.

1. substr().  In SQL, this translates to substring.  Easy enough right?  There is one more difference between the two than just the name.  The parameters are ordered differently in substr() than they are in substring().   Pay careful attention to your parameter sequence when converting this function from Oracle to SQL Server.
2. instr().  This one is less obvious.  I have used PatIndex() and CharIndex() for this one – depends on needed functionality.  If you understand that instr is searching for a value within a string – it makes it a little easier to understand.  Also knowing that PatIndex searches for “Patterns” and Charindex() searches for a character is helpful.  If you need to supply the optional parameter used by instr(), then you should use Charindex.  Though not entirely the same – similar functionality is available in SQL for the instr() function.
3. trunc().  This is a function used in Oracle to convert date and numbers to a shorter format (either different date format or fewer decimal places).  This is achieved through different means in SQL.  Two common methods are cast() and convert().
4. dual.  This is not a function.  This is an internal table containing a single row.  There are many uses for this internal table.  One common use is equivalent to the Numbers/Tally table in SQL server.  Pick your favorite numbers/tally table method in these types of cases.
5. connect by.  This is actually a pretty cool piece of functionality unique to Oracle.  I have seen this used in recursive CTEs to help control the hierarchy.  In these cases, it limits the result set to rows meeting the criteria of the connect by statement.  Similar functionality can be achieved through use of Joins and the Where clause.  This is a command that would be really cool in SQL.  It is true that you can build the hierarchy without this command in SQL.  I think it would help make that task easier and give it more flexibility.  It would also make it a little easier to read/understand.

This is all pretty cool.  It should be pretty straight forward stuff for most DBAs.  Some day, maybe we’ll explore a post dedicated to connect by and how some of the features of that command can be translated into SQL.  For now, just know that there is some commonality between the two RDBMSs – just a little translation may be necessary.