exotic constraints

Today I read a post on metalink where the user wanted a unique constraint for not-null values… Sounds easy, because Oracle never indexes null in btree index. If I have only one column, I simply index it, it will work. SQL> create table t66 ( n number); Table created. SQL> create unique index i66 on […]

The time has gone

Sure we know date. Oracle always store date and time in an internal format. It could be understood as a number of seconds since Jan 1st, -4712. It must be smaller than Jan 1st, 10000. Let’s say we do not care of the Y10K nowadays… It works well and it is easy to add days […]

login.sql

Hi, I am not recommending the use of glogin.sql and login.sql to do sql computation. It is degrading the performance and the maintenability of the scripts run over the database. Myself I am using a big login.sql, which is located in the “SQLPATH” variable. I tested it with 7.3, 8.1.7, 9.2 and 10.0 on AIX. […]

sys_connect_by_path

sys_connect_by_path is the only function provided by Oracle to get the the hierarchy path in one field, and it is only concatenating. I just found out a way of doing a sum of the path : let’s imagine I want the sum of the salary of all my hierarchy. select ename, sys_connect_by_path(ename,’/’) hierarchy, length(replace(sys_connect_by_path(lpad(‘ ‘,sal/10),’/’),’/’))*10 […]