Holidays

End of the day, start of the holiday. cu-soon

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 t66(n);

Index created.

SQL> insert into t66 values (null);

1 row created.

SQL> insert into t66 values (null);

1 row created.

The nulls are not indexed. In that post today, the user is using a two column index, and do not want to enforce that constraint when one of the column is null. No problem, we can use FBI to enforce this.

create index i on t(decode(col2,null,null,col1), decode(col1,null,null,col2));

so the index will contain only entries were both columns are not null.

Yesterday a user on forums.oracle.com wanted to have a not-different constraint, that is was only accepting entries [p;r] if [p;s] does not exist. It is quite hard to solved. I have read an interresting solution using ON-COMMIT-REFRESH materialized view with aggregates and constraints.

A long time ago, one user wanted a constraint "table should contain only one row".

create unique index i on t(col*0);
would ensure at most one row, with col NOT NULL

I think I can do better.

Imagine the user wants always exactly one row in STATUS(code number)

create table STATUS_MAXONE(code number, n number default 0 primary key);
create view STATUS as select code from STATUS_MAXONE;
insert into STATUS values (null);
create table STATUS_MINONE(n number references STATUS_MAXONE(n));
insert into STATUS_MINONE values (0);

Now the user can update the view STATUS, but neither delete nor insert...

Sometimes, you can also have circular foreign key constraints, for example, a PERSON can only marry with someone who exists in PERSON, that is a PERSON.SPOUSE => PERSON.ID relation. This is also quite special... Actually, a person can only marry his spouse, that means if I am your SPOUSE, you are my SPOUSE! But there is no way to reference a column that may be null (we must reference a primary key).

Using unique index on FBI has the limitation of FBI: function must be deterministic. Same with triggers. complex constraints needs additional tables to enforce your business rules.

Like the p;r; accepting p;r; and q;s; but not p;s;
we could simply have a table containing p;s; with p as primary key...

unexpected results !

It makes you hurl! 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 is perfectly legal to specify a non-prefixed column of the main query in the subquery. This could be like

select * from tab where 'foo' in (select 'foo' from dict);

so the "in" clause is always true

2) "not in" does not deliver result

select sysdate from dual where 400 not in (select comm from emp);

this is because 400!=null is UNKOWN. Check the 3 state booleans operations!

it could be rewritten with a not exists, or in 10g with LNNVL

select sysdate from dual where lnnvl(400 in (select comm from emp));

3) rows are not delivered in the correct order
it is a wrong assumption to think that the rows will be delivered in a specific order if you do not specify order by.

it depends mainly on the execution plan.

SQL> select ename from emp where ename<'C';
ENAME
----------
BLAKE
ALLEN
ADAMS

even group by does not help
SQL> select ename, max(job) from emp where ename<'C' group by ename;
ENAME MAX(JOB)
---------- ---------
BLAKE MANAGER
ALLEN SALESMAN
ADAMS CLERK

Why? I cheated the emp table and add an index using descending order!

If your table is partitioned, the order and the degree of parallelism depend on the load, and may deliver different results.

Even if I did not find an example for DISTINCT, I guess it is not safe to assume the way the rows are sorted does not depend on the execution plan.

4) avg does not deliver sum/count(*)
select sum(comm),count(*),avg(comm) from emp;
SUM(COMM) COUNT(*) AVG(COMM)
---------- ---------- ----------
2200 14 550

avg does ignore null. in fact it delivers sum(comm)/count(comm).

5) table does not exist

SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
GreatTable TABLE

SQL> desc GreatTable
ERROR:
ORA-04043: object GreatTable does not exist

Whoever it is, someone created a case sensitive table name.

SQL> desc "GreatTable"
Name Null? Type
----------------------- -------- ----------------
MagicColumn NUMBER(38,2)

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 and fraction of days. A common notation I use is date 'YYYY-MM-DD'. This bypass the nls settings.

I add one day to 28-Feb-1900

select date '1900-02-28' + 1 from dual;
01.03.1900

Cool it works! Try it in in excel and meet the Y1900 bug :-)

The first problem I am aware off are zero dates insert by OCI in 8i, which look like 0000-00-00, but evaluates to somewhen in 10101 BC!

The second one, more serious, is the Y0 bug. There is no year 0 in our Era, because the zero was not know when our Calendar was created. But Oracle has a year 0.

Proof:
select date '0001-01-01' - 1 from dual;
31.12.0000

And it is full of bugs!

SQL> select to_char(date '0000-01-01', 'DD/MM/YYYY') from dual;
00/00/0000

SQL> select to_char(date '0000-01-01', 'DD/MM/YYYYSP') from dual;
00/00/000000000000000000000000000000000000000000

SQL> select to_char(date '0000-01-01', 'DD/MM/YYYY JSP') from dual;
01/01/0000 ONE MILLION SEVEN HUNDRED TWENTY-ONE THOUSAND FIFTY-EIGHT

Ok, what about timestamp?
Basically, a timestamp is a date + a fraction of seconds + evtl a timezone offset.

Again there, I like to use the ANSI notation TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

I try to stick to timezone region, it scales better over summer time than timezone offsets.

SQL> select timestamp '2005-10-30 01:30:00.00 Europe/Zurich' + to_dsinterval('0 2:0:0') from dual;
30.10.2005 02:30:00.00 EUROPE/ZURICH

Timezone conversion is done with AT

SQL> select current_timestamp at time zone 'US/Central' from dual;
14.06.2005 09:25:11.77 US/CENTRAL

Timestamps do not accept addition of numbers. The only think you can add is interval, but take care, it is no longer a pure "addition", because the associativity and commutativity properties are not retained.

(1Mon + ts) + 1Day != 1Mon + (ts + 1Day)
ts + 1Mon + 1Day != ts + 1Day + 1Mon

The + seems to be a "group operator" there, not the mathematical addition.

Anyway, if you want to know when a baby born 2000-02-29 will have his 18th birthday, you should rather ask a lawyer than a dba :-)

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' order by to_number(value);
This works on my db, but it is bad coding, because to_number could well be executed for other parameters. There is no guarantee that oracle execute the function only when the where is true.

It is also more flexible
ex: first alpha alphabetically, then number numerically

order by translate(col,'~0123456789','~'), lpad(col,20)
a
aa
z
2
3
11

In 10g, regular expression will ease complex sorts

1<1A<1AA<1B<2C<10A:
lpad(regexp_substr(col,'^[[:digit:]]*'),20)||regexp_substr(col,'[^[:digit:]].*')

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. Be aware, it has side effects!

Ok, here it is

host if ! [ -t 0 ] || ! ( unset PERLLIB PERL5LIB; /bin/perl -e 'use POSIX qw/getpgrp tcgetpgrp/;open(TTY,"/dev/tty") or exit 1;exit(tcgetpgrp(TTY)!=getpgrp());' ); then :;else echo @login_ux_fg;fi >/tmp/loginsql.sql
host chmod 2>/dev/null 666 /tmp/loginsql.sql
@/tmp/loginsql

Surprised? Does this looks like sql?
Not really. I am actually checking that sqlplus is not piped (-t 0), and run in terminal (dev/tty), in foreground (getpgrp). Only then I am executing login_ux_fg.sql

Well here is my login_ux_fg.sql, with colors, terminal header and more...

-- login_ux_fg.sql (c) Laurent Schneider ZKB 2004-2005
def _editor=vi
set editfile /tmp/tmporacle.sql
set ver off pages 40000 lin 80 long 1000000000 longc 60000 trims on con .
col file_name format a60
col member format a41
col tablespace_name format a20
col db_link format a20
col host format a20

-- I am not interrested in that when select * from system views
col ini_trans nopri
col max_trans nopri
col INITIAL_EXTENT nopri
col NEXT_EXTENT nopri
col MAX_EXTENTS nopri
col MIN_EXTENTS nopri
col PCT_INCREASE nopri
col PCT_THRESHOLD nopri
col INCLUDE_COLUMN nopri
col FREELISTS nopri
col FREELIST_GROUPS nopri
col PCT_FREE nopri

-- sql error can occurs, if db is down or when no privilege. The show must go on
set termout off echo off arrays 7
whenever sqlerror continue
whenever oserror continue

-- save the current line of the query and the query (10g)
host echo > /tmp/lastquery.sql; chmod 2>/dev/null 666 /tmp/lastquery.sql
spool /tmp/lastquerycurrentline.txt
list *
spool off
host chmod 2>/dev/null 666 /tmp/lastquerycurrentline.txt
save /tmp/lastquery.sql rep
host chmod 2>/dev/null 666 /tmp/lastquery.sql

col "_myprompt" new_value myprompt
col "_mytitle" new_value mytitle
col "_user" new_value _USER
col "_privilege" new_value _PRIVILEGE
col "_connect_identifier" new_value _CONNECT_IDENTIFIER
col "_o_release" new_value _O_RELEASE

-- default to null
select 'x' "_user", 'x' "_privilege", 'x' "_connect_identifier", 'x' "_o_release" from dual where 1=2;

select nvl('&_user', user) "_user", nvl('&_privilege', decode(user,'SYS','AS SYSDBA','PUBLIC','AS SYSOPER')) "_privilege", nvl('&_o_release','0') "_o_release" from dual;
select nvl('&_connect_identifier',name) "_connect_identifier" from v$database;
select nvl('&_connect_identifier',substr(global_name, 1, instr(global_name||'.','.'))) "_connect_identifier" from global_name;

-- check if we support colors / linesize
host if echo $TERM | grep -qsE 'xterm|dtterm' ; then echo def _SYSDBA=\"`tput colf1`\";echo def _NORMAL=\"`tput colf4`\";echo def _SYSOPER=\"`tput colf5`\";echo def _RESET=\"`tput sgr0`\";echo set lin `tput cols`;else echo def _SYSDBA=\"\";echo def _NORMAL=\"\";echo def _SYSOPER=\"\";echo def _RESET=\"\";echo set lin 80; fi > /tmp/color.sql; chmod 2>/dev/null 666 /tmp/color.sql
@/tmp/color.sql


.

select
decode('&_PRIVILEGE', 'AS SYSDBA', '&_SYSDBA', 'AS SYSOPER', '&_SYSOPER', '&_NORMAL')||
substr('&_USER'||
decode('&_CONNECT_IDENTIFIER', null, null, '@&_CONNECT_IDENTIFIER')||
decode('&_PRIVILEGE', null, null, ' &_PRIVILEGE')||
decode(&_O_RELEASE, null, null,
'/'||
trunc(&_O_RELEASE/100000000) || '.' ||
mod(trunc(&_O_RELEASE/1000000),100) || '.' ||
mod(trunc(&_O_RELEASE/10000),100) || '.' ||
mod(trunc(&_O_RELEASE/100),100)
),
1, 33)||
'&_RESET'||
chr(10)||'SQL> ' "_myprompt"
from dual;

select
chr(27)||
']2;&_USER'||
decode('&_CONNECT_IDENTIFIER', null, null, '@&_CONNECT_IDENTIFIER')||
rtrim(' &_PRIVILEGE')||
decode(&_O_RELEASE, null, null,
'/'||
trunc(&_O_RELEASE/100000000) || '.' ||
mod(trunc(&_O_RELEASE/1000000),100) || '.' ||
mod(trunc(&_O_RELEASE/10000),100) || '.' ||
mod(trunc(&_O_RELEASE/100),100)
)||
chr(7) "_mytitle"
from dual;

create
.

del 1 last
get /tmp/lastquery
.

host if echo $TERM | grep -qsE 'xterm|dtterm' ; then echo;echo '&mytitle';echo; fi
host /usr/bin/sed 1>/tmp/lastquerycurrentline.sql 2>/dev/null -n 's/*.*//p' /tmp/lastquerycurrentline.txt
host chmod 2>/dev/null 666 /tmp/lastquerycurrentline.sql
@/tmp/lastquerycurrentline

col "_myprompt" clear
col "_mytitle" clear
col "_user" clear
col "_privilege" clear
col "_connect_identifier" clear

set sqlp "&myprompt"
undef myprompt mytitle _SYSDBA _SYSOPER _NORMAL _RESET

undef _RC
set arraysize 15
set termout on

Only in sqlplus 10g, this script is run at each connection. It is basically good, because the prompt will be recalculated. What less good is, is that I will have to set whenever error to continue. I found no way to reset it to its original value after connect. Same for termout

so if I have a script

whenever sqlerror exit
connect /
create table ...

It will not work as expected. You can search on asktom.oracle.com or on forums.oracle.com, you will find no solution to that problem, but many posts of mines...

shell + sqlplus

How to send commands to sqlplus ?
Use stdin

$ ( echo prompt $(hostname) $(date); echo desc emp ) | sqlplus -s scott/tiger
dbsrv85a.ex.ch 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 do you retrieve data from your database with the shell and sqlplus ?

USERS=$(echo "select '@',username from dba_users;" | # this is my stdin
sqlplus -s "/ as sysdba" | # sqlplus as a filter
awk '/^ORA-/ /^SP2-/ { print|"cat >&2";exit 1 }
/^@/ { print $2 }' # search for @
)
if [ $? != 0 ] # awk returned error
then
echo error >&2
# exit 1
fi
for u in $USERS # do something
do
echo hello $u
done

hello SYSTEM
hello SYS
hello PERFSTAT
hello SCOTT
hello DIP
hello U01
hello EXFSYS
hello ZKBMON
hello DBSNMP
hello OUTLN

How do I get OS output from sqlplus ?
The high end solution is to use Java. However, this implies you have Java in the Database (cost memory), you have the necessary privileges (which can be easily misused to destroy your system), and you use plsql. Have a look at my blog about Disk Free space.
Ok, let's imagine I do not want to install all that stuff.

SQL> host uptime
17:09pm up 84 days, 23:32, 14 users, load average: 1.13, 1.23, 1.28

How do I get the return code?
SQL> prompt return code is &_RC
return code is 0

But this does not work in 10g :-(

How do I use the os user, hostname, system date, ip address?
Use SQL!

SQL> select sys_context('USERENV','OS_USER'), host_name, sysdate, UTL_INADDR.GET_HOST_ADDRESS(host_name) from v$instance;

oracle dbsrv85a 06.06.2005 17:14:46 147.50.59.167

Ok, I want to know the OS and store it in a variable!
Hmm, hmm... I like to do those kind of things by sourcing temp files
SQL> def os=""
SQL> host echo def os="$(uname -s)" > /tmp/tmpos.sql
SQL> start /tmp/tmpos.sql
SQL> select '&os' from dual;
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 sal
from emp
connect by mgr=prior empno
start with mgr is null ;

ENAME HIERARCHY SAL
---------- ------------------------------ -------
KING /KING 5000
JONES /KING/JONES 7970
SCOTT /KING/JONES/SCOTT 10970
ADAMS /KING/JONES/SCOTT/ADAMS 12070
FORD /KING/JONES/FORD 10970
SMITH /KING/JONES/FORD/SMITH 11770
BLAKE /KING/BLAKE 7850
ALLEN /KING/BLAKE/ALLEN 9450
WARD /KING/BLAKE/WARD 9100
MARTIN /KING/BLAKE/MARTIN 9100
TURNER /KING/BLAKE/TURNER 9350
JAMES /KING/BLAKE/JAMES 8800
CLARK /KING/CLARK 7450
MILLER /KING/CLARK/MILLER 8750

The LPAD is transforming a number in a string, then length will calculate the length of the calculated string. It is limited to 4000 char. That's why I divided it by 10.

Let's do it more flexible with PLSQL

create or replace function eval(expr varchar2) return number is retval number;
begin execute immediate 'select '||expr||' from dual' into retval; return retval; end;
/

select ename, sys_connect_by_path(ename,'/') hierarchy, eval(sys_connect_by_path(sal,'+')) sal
from emp
connect by mgr=prior empno
start with mgr is null
/


Just using + to add, simple, is not it?