/* BeejBløg */

Nov 13, 2018 - 2 minute read - Comments

Oracle vs SQL Server

Intro

I am admitedly much longer time spent on SQL Server and just recently having a couple years on Oracle 11g… All that T-SQL syntax experience, especially after SQL Server 2005 really gave us a lot of power, sets basic expectations on what can be readily accomplished.

Oracle Negatives

  • Common syntax error messages are much harder to deciper
    • E.g. for a stored procedure inside a package, one must declare the proc with all parms and then essentially declare it again for the implementation in the body… if those 2 are different you get a generic error “Cannot navigate to error. Check if object exists.”

  • Can’t use CTE’s with updates

  • Table valued parameters can’t be joined…
  • … this is due to the significant global limitation that Oracle PL/SQL and Oracle DML SQL are separated by a context switch driving several corresponding limitations…
    • calling a stored proc from network client tier and passing a LIST can only be done via associative arrays… assoc.arrays can’t be joined in SQL… you must use a FORALL or FOR LOOP PL/SQL construct
    • you can join a nested table but that data structure can’t be passed in from the client tier… everybody asks the same questions over and over… it’s just the way it’s been for years

  • non ansi null concatenation: select ‘test’ || null from dual; –> yields ‘test’ versus null… prevents using the handy t-sql pattern of: select isnull(field + ‘, ‘, “) + newVal
  • can’t define the output length of varchar UDF’s !?!
  • temp tables as we know them in t-sql don’t even exist – oracle temp tables are “global” i.e. permanent structures… they do have automatic support for session isolation though… so one sessions inserts are hidden from anothers

Oracle Positives

  • the ability to define temp tables & array/collection fields with generic table%field references such that adding columns to the source tables doesn’t require maintenance in the temp table logic… this is longstanding gripe in t-sql… kinda amazing they haven’t filled this gap yet

Tags: Database

Embed Github Content In Post

comments powered by Disqus