This discussion is archived
4 Replies Latest reply: Dec 7, 2012 1:34 PM by orafad RSS

Is it possible to set the database with nulls first for all its tables ?

576227 Newbie
Currently Being Moderated
Hello,

I have a schema of 117 tables and I would like Oracle to behave nulls first when doing any order by on any tables.

Is there a way to tell that respectable old database server to do such a thing ?

As it is today, I have to write some custom DAO specific to Oracle just so as to have a nulls first in the sql statements.

Kind Regards,
  • 1. Re: Is it possible to set the database with nulls first for all its tables ?
    Herald ten Dam Expert
    Currently Being Moderated
    Hi,

    you can get the nulls first only by an ordening in your SQL:
    select * from emp order by name nulls first;
    So you have to adjust your SQL to get nulls first.

    Herald ten Dam
    http://htendam.wordpress.com
  • 2. Re: Is it possible to set the database with nulls first for all its tables ?
    576227 Newbie
    Currently Being Moderated
    Thanks for the comment. I am already fixing my sql statements one after the other into some custom DAO methods only to have the Hibernate layer work against Oracle. Quite a cumbersome and dumb thing to do. How come Oracle does not offer such a global preference ? I just don't get it.
  • 3. Re: Is it possible to set the database with nulls first for all its tables ?
    576227 Newbie
    Currently Being Moderated
    I could make my Hibernate query cross database servers (Oracle, MySql, H2, HSQLDB) compatible with the following coalesce function:

         public List<NavmenuLanguage> findWithNavmenu(Navmenu navmenu) {
              Query query = getSession().createQuery("from NavmenuLanguage where navmenu.id = :navmenuId order by coalesce(languageCode, '0')");
              query.setLong("navmenuId", navmenu.getId());
              return query.list();
         }

    Note the single quotes and the zero, the two together required to work on all database servers.
  • 4. Re: Is it possible to set the database with nulls first for all its tables ?
    orafad Oracle ACE
    Currently Being Moderated
    user573224 wrote:
    I have a schema of 117 tables and I would like Oracle to behave nulls first when doing any order by on any tables.
    Basically this is an application (sql) issue. If you do not code explicitly, ascending ordering is default and also means that nulls last is the default.

    http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#i2171079

    As 'nulls first' belongs to order by clause of select statment, this seems like a question for the SQL forum. {forum:id=75} That you happen to run sql on XE does not matter.

    As it is today, I have to write some custom DAO specific to Oracle just so as to have a nulls first in the sql statements.
    Changing how a database engine behaves seems like the opposite of dbms agnostic application, if that's the purpose. Also in general, database agnostic/ignorant applications are often considered a sure way to unscalable and unmanageable systems.

    One could also consider that customers having Oracle as part of their infrastructure, would like to see that applications make good use of Oracle's strong and highly capable features and options -- not treat it as some sort data dumping bin.
    (Ok, granted, with XE this might not be true. Instead there are issues of limitations and lack of support (patches), that you might not want or can take on as an applications vendor.)


    Btw, good to see that you've found a fix in sql for your problem, and thank you for reverting back with that to the forum!


    Edited by: orafad on Dec 7, 2012 10:29 PM

    Edited by: orafad on Dec 7, 2012 10:31 PM

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points