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<=n) "N!" from      (select rownum […]

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