Skip to content

Top 10 Confusing Things in Oracle

September 3, 2012

Every mature language, platform or system has little quirks, eccentricities, and anachronisms that afficionados just accept as “that’s the way it is” and that look weird, or outlandishly strange to newbies and outsiders. The more mature, and more widely used is the product, the more resistance to change there will be – causing friction that helps to ensure these misfeatures survive.

Oracle, due to the priority placed on backwards compatibility, and its wide adoption, is not immune to this phenomenon. Unless a feature is actively causing things to break, as long as there are a significant number of sites using it, it’s not going to change. In some cases, the feature might be replaced and the original deprecated and eventually removed; but for core features such as SQL and PL/SQL syntax, especially the semantics of the basic data types, it is highly unlikely these will ever change.

So here I’d like to list what I believe are the things in Oracle that most frequently confuse people. These are not necessarily intrinsically complicated – just merely unintuitive, especially to a child of the 90’s or 00’s who was not around when these things were first implemented, when the idea of “best practice” had barely been invented; or to someone more experienced in other technologies like SQL Server or Java. These are things I see questions about over and over again – both online and in real life. Oh, and before I get flamed – another disclaimer: some of these are not unique to Oracle – some of them are more to do with the SQL standard; some of them are caused by a lack of understanding of the relational model of data.

Once you know them, they’re easy – you come to understand the reasons (often historical) behind them; eventually, the knowledge becomes so ingrained, it’s difficult to remember what it was like beforehand.

Top 10 Confusing Things in Oracle

  1. Empty strings are NULL

  2. DATEs are not dates

  3. Nothing equals NULL

  4. ROWNUM … ’nuff said?

  5. Use VARCHAR2, not CHAR or VARCHAR

  6. Double-quotes delimit identifiersSingle quotes delimit strings

  7. No intrinsic sort orderYou can’t insert a record at the “top”, “middle” or “end” of a table

  8. You don’t add a column in the “middle” of a table

  9. Don’t create temporary tables at runtime

  10. Every User is/has a Schema, every Schema is/has a User

Got something to add to the list? Drop me a note below.

More resources:

About these ads

From → Oracle, PL/SQL, SQL

9 Comments
  1. Probably not TOP 10, but SKIP LOCKED doesn’t behave as expected, when used with ROWNUM: http://markjbobak.wordpress.com/2010/04/06/unintended-consequences/

  2. Narendra permalink

    Maybe this…”Uncommitted data can get written to datafiles”

  3. Narendra permalink

    Sorry could not help but post this…”sequences can contain gaps”

    • Yes, I see a lot of questions about sequences and how to make them gapless.

  4. I don’t know if I’d include it in a “most confusing” list, but – You can grant “CREATE ANY”, but you can’t limit it to a single schema. Say I have a schema, “APP_ONE” that owns objects for a single application. I don’t want to give my developers the password to “APP_ONE”, but I want them to be able to create objects in it, but not in “APP_TWO”. This is related to your #10.

  5. Not all ALTER SESSION commands require the ALTER SESSION privilege.

    • Interesting! I didn’t know that – I think the “ALTER SESSION” privilege seems to be a misnomer. It should perhaps have been “ALTER SESSION SQL_TRACE” or something like that.

  6. Connor permalink

    A global temporary table is
    – not global, its private to a session
    – not temporary, its defined permanently in the catalog

    :-)

Trackbacks & Pingbacks

  1. PeopleSoft Weekly Stats « PeopleSoft Tipster

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 196 other followers

%d bloggers like this: