Tutorial :Oracle Gotchas for An Experienced Newb



Question:

What are some Oracle gotchas for someone new to the platform, but not new to relational databases (MySQL, MS SQL Server, Postgres, etc.) in general.

Two examples of the kind of things I'm looking for

  1. Many relational database products handle creating an auto_increment key for you. Oracle does not, you must manually create the sequence, then create the trigger

  2. When INSERTING data via the SQL Developer interface, you have to manually commit the data

Bonus points for PHP related gotchas, as that's the platform I'll this hypothetical experienced newb will be using.


Solution:1

Note: I'm explaining only the gotchas here, i. e. situations when Oracle behaves not as other systems do. Oracle has numerous benefits over other RDBMS's, but they are not the topic of the post.

  • You cannot SELECT without FROM.

    SELECT  1  

    will fail, you need to:

    SELECT  1  FROM    dual  
  • Empty string and NULL are the same thing.

    SELECT  *  FROM    dual  WHERE   '' = ''  

    returns nothing.

  • There are neither TOP nor LIMIT. You limit your results in the WHERE clause:

    SELECT  *  FROM    (          SELECT  *          FROM    mytable          ORDER BY                  col          )  WHERE   rownum < 10  

    exactly this way, using a subquery, since ROWNUM is evaluated before ORDER BY.

  • You cannot nest the correlated subqueries more than one level deep. This one will fail:

    SELECT  (          SELECT  *          FROM    (                  SELECT  dummy                  FROM    dual di                  WHERE   di.dummy = do.dummy                  ORDER BY                          dummy                  )          WHERE   rownum = 1          )  FROM    dual do  

    This is a problem.

  • NULL values are not indexed. This query will not use an index for ordering:

    SELECT  *  FROM    (          SELECT  *          FROM    mytable          ORDER BY                  col          )  WHERE   rownum < 10  

    , unless col is marked as NOT NULL.

    Note than it's NULL values that are not indexed, not columns. You can create an index on a nullable column, and non-NULL values will get into the index.

    However, the index will not be used when the query condition assumes that NULL values can possibly satisfy it.

    In the example above you want all value to be returned (including NULLs). Then index doesn't know of non-NULL values, hence, cannot retrieve them.

    SELECT  *  FROM    (          SELECT  *          FROM    mytable          ORDER BY                  col          )  WHERE   rownum < 10  

    But this query will use the index:

    SELECT  *  FROM    (          SELECT  *          FROM    mytable          WHERE   col IS NOT NULL          ORDER BY                  col          )  WHERE   rownum < 10  

    , since non-NULL values cannot ever satisfy the condition.

  • By default, NULLs are sorted last, not first (like in PostgreSQL, but unlike MySQL and SQL Server)

    This query:

    SELECT  *  FROM    (          SELECT  1 AS id          FROM    dual          UNION ALL          SELECT  NULL AS id          FROM    dual          ) q  ORDER BY          id  

    will return

    id  ---  1  NULL  

    To sort like in SQL Server and MySQL, use this:

    SELECT  *  FROM    (          SELECT  1 AS id          FROM    dual          UNION ALL          SELECT  NULL AS id          FROM    dual          ) q  ORDER BY          id NULLS FIRST  

    Note that it breaks rownum order unless the latter is not used out of the subquery (like explained above)

  • "MYTABLE" and "mytable" (double quotes matter) are different objects.

    SELECT  *  FROM    mytable -- wihout quotes  

    will select from the former, not the latter. If the former does not exist, the query will fail.

    CREATE TABLE mytable  

    creates "MYTABLE", not "mytable".

  • In Oracle, all implicit locks (that result from DML operations) are row-level and are never escalated. That is no row not affected by the transaction can be implicitly locked.

    Writers never block readers (and vice versa).

    To lock the whole table, you should issue an explicit LOCK TABLE statement.

    Row locks are stored on the datapages.

  • In Oracle, there are no "CLUSTERED indexes", there are "index-organized tables". By default, tables are heap organized (unlike SQL Server and MySQL with InnoDB).

    In Oracle world, a "clustered storage" means organizing several tables so that the rows which share a common key (from several tables) also share a datapage.

    A single datapage hosts multiple rows from multiple tables which makes joins on this key super fast.


Solution:2

SELECT 1 wont work, do select 1 from dual instead.

If you work with hierarchical data, connect by is great.


Solution:3

One comment: You don't have to create a trigger in order to use sequences, unless you're adamant about replicating the behavior of the Sybase/SQL Server IDENTITY column. I find it more useful to just use the sequence directly in the actual insert statements, e.g.

INSERT    INTO MyTable       ( KeyCol       , Name       , Value       )  SELECT Seq_MyTable.NextVal       , 'some name'       , 123    FROM dual;  

You don't need to worry about the overhead of trigger execution, and you have the flexibility to deal with inserting rows into the table without having to worry about sequence values being assigned (such as when moving data from schema to another). You can also pre-select values from the sequence for inserting ranges of data and other techniques that the IDENTITY feature either makes difficult or impossible.


Solution:4

Don't forget to use nvl(column) around any column in a rowset that might be filled entirely with null values. Otherwise, the column will be missing from the rowset.

That's right, missing entirely!

Example:

SELECT nvl(employeeName,'Archie'), nvl(employeeSpouse,'Edith') FROM Employee  

This will guarantee you get two columns in your rowset, even if all the values in both are null. You'll just see a bunch of 'Archie' and 'Edith' values. If you don't use nvl(), you could potentially get only one column or neither back. The gotcha-east part of this is that your code can run fine in your development environment, and even pass QA, but when it gets to production, values in the table may change the structure of the results!

So, in short, whenever you are selecting a nullable column, be sure to use nvl().


Solution:5

It seems I've run into more Oracle databases being case sensitive to schema objects and data than in SQL Server.


Solution:6

I wrote up a few differences here: Think ANSI Standard SQL Is Fully Portable Between Databases? Think Again.


Solution:7

Temporary tables

You create and index them like normal tables but every session/transaction only sees its own data. This is different from MS SQL.

Global variables

They are passed by reference. This means that if you pass a global variable to a procedure as a parameter and modify the global variable inside your procedure, the parameter value will also change. Not a very popular method, though.

Triggers

Until very recent versions there was no way to determine a way in which similar triggers will fire. If you really cared which "BEFORE UPDATE FOR EACH ROW" was first you put it all in one trigger.


Solution:8

There is no group concatenation like in MySQL. If you want a group concatenation aggregate function, you have to write your own. Here is my implementation:

drop type T_GROUP_CONCAT;    create or replace type GROUP_CONCAT_PARAM as object  (    val varchar2(255),    separator varchar2(10),    numToConcat NUMBER,    MAP MEMBER FUNCTION GROUP_CONCAT_PARAM_ToInt  return VARCHAR2  );    --map function needed for disctinct in select clauses  CREATE OR REPLACE TYPE BODY GROUP_CONCAT_PARAM IS      MAP MEMBER FUNCTION GROUP_CONCAT_PARAM_ToInt return VARCHAR2 is         begin           return val;         end;     end;      /    CREATE OR REPLACE TYPE T_GROUP_CONCAT   AS OBJECT (    runningConcat VARCHAR2(5000),  runningCount NUMBER,    STATIC FUNCTION ODCIAggregateInitialize    ( actx IN OUT T_GROUP_CONCAT    ) RETURN NUMBER,    MEMBER FUNCTION ODCIAggregateIterate    ( self  IN OUT T_GROUP_CONCAT,      val   IN       GROUP_CONCAT_PARAM    ) RETURN NUMBER,    MEMBER FUNCTION ODCIAggregateTerminate    ( self             IN   T_GROUP_CONCAT,      returnValue  OUT VARCHAR2,      flags           IN   NUMBER    ) RETURN NUMBER,    MEMBER FUNCTION ODCIAggregateMerge    (self  IN OUT T_GROUP_CONCAT,     ctx2 IN      T_GROUP_CONCAT    ) RETURN NUMBER    );  /    CREATE OR REPLACE TYPE BODY T_GROUP_CONCAT AS    STATIC FUNCTION ODCIAggregateInitialize    ( actx IN OUT T_GROUP_CONCAT    ) RETURN NUMBER IS     BEGIN      IF actx IS NULL THEN        actx := T_GROUP_CONCAT ('', 0);      ELSE        actx.runningConcat := '';        actx.runningCount := 0;      END IF;      RETURN ODCIConst.Success;    END;    MEMBER FUNCTION ODCIAggregateIterate    ( self  IN OUT T_GROUP_CONCAT,      val   IN     GROUP_CONCAT_PARAM    ) RETURN NUMBER IS    BEGIN      if self.runningCount = 0 then          self.runningConcat := val.val;      elsif self.runningCount < val.numToConcat then          self.runningConcat := self.runningConcat || val.separator || val.val;      end if;      self.runningCount := self.runningCount + 1;      RETURN ODCIConst.Success;    END;    MEMBER FUNCTION ODCIAggregateTerminate    ( self        IN  T_GROUP_CONCAT,      ReturnValue OUT VARCHAR2,      flags       IN  NUMBER    ) RETURN NUMBER IS    BEGIN      returnValue := self.runningConcat;      RETURN ODCIConst.Success;    END;    MEMBER FUNCTION ODCIAggregateMerge    (self IN OUT T_GROUP_CONCAT,     ctx2 IN     T_GROUP_CONCAT    ) RETURN NUMBER IS    BEGIN      self.runningConcat := self.runningConcat || ',' || ctx2.runningConcat;      self.runningCount := self.runningCount + ctx2.runningCount;      RETURN ODCIConst.Success;    END;    END;  /    CREATE OR REPLACE FUNCTION GROUP_CONCAT  ( x GROUP_CONCAT_PARAM  ) RETURN VARCHAR2  --PARALLEL_ENABLE  AGGREGATE USING T_GROUP_CONCAT;  /  

To use it:

select GROUP_CONCAT(GROUP_CONCAT_PARAM(tbl.someColumn, '|', 2)) from someTable tbl  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »