set pages 50000

I have often see set pagesize 50000 or set pages 9999, just to avoid page break. I wonder how many line break will appears when the select grow over 50000… A cool solution is to use embedded with pagesize 0 SQL> set emb on pages 0 newp 0

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… Continue reading The time has gone

order by to_number ?

to_number is often subject to generate ORA-01722: invalid number. When I deal with integers, I prefer lpad. ex: sort pseudo-numeric select * from t order by lpad(col,20); 1 2 10 It performs well, and do not generate ora-01722. ora-01722 does not mean that I have bad data. select name,value from v$parameter where name like ‘%pool_size’… Continue reading order by to_number ?

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.… Continue reading login.sql

shell + sqlplus

How to send commands to sqlplus ? Use stdin $ ( echo prompt $(hostname) $(date); echo desc emp ) | sqlplus -s scott/tiger dbsrv85a Mon Jun 6 17:01:46 CEST 2005 Name Null? Typ —- —– — EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2) How… Continue reading shell + sqlplus

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… Continue reading sys_connect_by_path

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… Continue reading Grid without X

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

Get disk space

I just read today on sun.com that checking disk space in java will be platform independent in the java.io.File class before 2007, add a few years until it is integrated in Oracle. But I cannot wait that long, so I decided to write my own code with “df” on my AIX box. Ok, let’s do… Continue reading Get disk space

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); > >… Continue reading One example about hierarchies

Hierarchical queries

The typical hierarchical query is you want to select your boss, and the boss of your boss, etc. It could look like select prior ename ename, ename mgr from emp connect by prior mgr=empno start with ename=’SCOTT’; SCOTT SCOTT JONES JONES KING I start with Scott and the hierarchy is built. I can use the… Continue reading Hierarchical queries