Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

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.

Comments

Posted by Bruce W Cassidy on 20 August 2011

In no particular order...

You generally don't need a tally/numbers table to emulate SYS.DUAL.  For example, the common:

select SYSDATE from SYS.DUAL;

...  can be replaced with:

select getdate();

SQL Server will quite happily return a function or result as if there's an underlying single-row table.

TRUNC() is a little harder, or rather, it was.  The introduction of the DATE type in SQL Server makes it a lot easier.  So for taking just the date portion of a date-time:

In Oracle:

select TRUNC(SYSDATE) from SYS.DUAL;

In SQL Server 2005 and earlier:

select dateadd(day, datediff(day, 0, getdate()), 0);

In SQL Server 2008:

select cast(getdate() as date);

However, for numeric values, it pays to check out the floor() and round() functions.

On, one thing to be aware of with Oracle's substr() is that it can be used similarly to SQL Server's right() function by using negative numbers.

Posted by nick.mcdermaid on 20 August 2011

What I found confusing for a while was the heirarchy of elements in Oracle

An Oracle "database" is analogous to a SQL Server Instance

An Oracle "schema" is analogous to a SQL Server database as many schemas exist inside an Oracle database. However functionality they are more like SQL Server schemas.

An Oracle "block" is analogous to a SQL Server page (although Oracle block size is configurable)

An Oracle "tablespace" is analogous to a SQL Server filegroup, however any object in any Oracle schema can be held in any tablespace.

An oracle schema is linked to a the user that logs in, similarly to the way SQL Server used to work.

So you could say that Oracle actually doesn't have the piece that is similar to a SQL Server "database"

Connection methods and definitions... I still don't quite follow this so I won't attempt to explain instances, TNS strings, EZConnect etc.

Posted by Peter Maloof on 21 August 2011

Nick:

I think a SQL Server database is analogous to an Oracle tablespace, and I agree with you that an Oracle tablespace is analogous to a SQL Server filegroup.

Posted by Eric Russell on 22 August 2011

From my experience, the biggest pitfall one can encounter, when context switching between writing SQL for SQL Server versus Oracle, has nothing to do with minor variations in SQL syntax or proprietary functions, but rather we must keep in mind that in Oracle an empty string is equivalent to NULL.

For example, I ran the following queries on Oracle. A SQL Server developer not familiar with Oracle would look at the SQL and expect the opposite result.

select case when 'Smith' > '' then 'True' else 'False' end

  as smith_greater_than_emptystr from dual;

select case when '' = '' then 'True' else 'False' end

  as emptystr_equals_emptystr from dual;

select case when '' is null then 'True' else 'False' end

  as emptystr_is_null from dual;

select coalesce( '', 'Smith' )

  as first_non_null_string from dual;

select count(*) cust_count

 from (select '' as cust_name from dual) x where cust_name is not null;[/code]

[code="plain"]

SMITH_GREATER_THAN_EMPTYSTR

---------------------------

False                      

EMPTYSTR_EQUALS_EMPTYSTR

------------------------

False                    

EMPTYSTR_IS_NULL

----------------

True            

FIRST_NON_NULL_STRING

---------------------

Smith                

CUST_COUNT            

----------------------

0

Posted by Jason Brimhall on 20 September 2011

@Bruce - sorry for the late reply but excellent info.

Leave a Comment

Please register or log in to leave a comment.