TO_CHAR(…, ‘D’)

How do I get than MONDAY=1, TUESDAY=2, WEDNESDAY=3 … ? With to_char() alter session set nls_territory=germany; select to_char(sysdate,’DAY D’) from dual; TUESDAY 2 With decode() select decode(to_char(sysdate, ‘FMDAY’, ‘NLS_DATE_LANGUAGE=american’),’MONDAY’, ‘1’, ‘TUESDAY’, ‘2’, ‘…’)) from dual; With mod() As a reference, I take monday Jan 1st, 1000. select mod(trunc(sysdate)-date ‘1000-01-01’,7)+1 from dual; 2 How do I […]

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 […]

Grid without X

We just received new PCs. Brand new with XP. Nice? Let’s see! As usual, I started my Exceed and logged on my AIX server. I tried to start the Grid Control Engine (opmn). Hard luck. Failed to start OC4J instance 🙁 The first problem is, when I installed the Grid, my old workstation DISPLAY name […]

Recursive SQL

One of the most common school exercice about recursion is the factorial. Guess what, I am going to do it in sql with hierarchies! I use the following ln property : x1*…*xn = exp(ln(x1)+..+ln(xn))) Ok, here it is SQL> select n, (select exp(sum(ln(level))) from dual connect by level

One example about hierarchies

Today morning I just received a question from a friend where I used hierarchies : > Let’s assume a couple of persons have bought some cakes togeher and they want to eat it: > > Create table cake_owners > (owner# number, > cake# number, > constraint cake_pk primary key (owner#,cake#) > using index); > > […]