Month: June 2005

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

It makes you cry! It makes you claim you have found a bug! but it is working as specified! 1) subquery refers to a column of the main query select * from emp where ename in (select ename from dept where deptno=10); the query does not complain that column does not exist in dept. It […]

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

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

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

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

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

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