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

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
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 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 MON
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?

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 has been registered in opmn.xml!
The second problem, my server has no graphic card, and no X server running.
Actually, I do not need X on this server. But to let the Grid start, I will use Frame Buffer. In AIX, you install X11.vfb base package, then mkitab “xvfb:2:once:/usr/lpp/X11/bin/X -force -vfb :1 >tmp/x.txt 2>&1″ to start it at the next reboot. Start it now as well.
Then edit your opmn.xml file and set the DISPLAY variable to localhost:1

<variable id=”DISPLAY” value=”localhost:1″/>

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 n from dual connect by level<7);

   N   N!
---- ----
   1    1
   2    2
   3    6
   4   24
   5  120
   6  720

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 java. The horrible regexp there is parsing df.

create or replace and compile
java source named “Df”
as
import java.io.*;
public class Df
{
public static int getFree(String args)
{
return Integer.parseInt(df(args).replaceAll(“[^0-9]*[ ]*[1 ][0-9 ][0-9][0-9][%-][^ ]* [^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*”,””).trim());
}
public static String getFS(String args)
{
return df(args).replaceAll(“[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[1 ][0-9 ][0-9][0-9][%-][^ ]* [^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ ]*[ ]*[^ /]*”,””).trim();
}
public static String df(String args)
{
String rc = “”;
try
{
Process p = Runtime.getRuntime().exec(“/bin/df -kt “+args);
int bufSize = 4096;
BufferedInputStream bis = new BufferedInputStream(p.getInputStream(), bufSize);
int len;
byte buffer[] = new byte[bufSize];
while ((len = bis.read(buffer, 0, bufSize)) != 1)
rc += new String(buffer, 0, len-1);
p.waitFor();
}
catch (Exception e)
{
e.printStackTrace();
}
finally
{
return rc;
}
}
}
/

Now I create two functions

create or replace
function getFree( p_cmd in varchar2) return number
as
language java
name ‘Df.getFree(java.lang.String) return int’;
/

create or replace
function getFS( p_cmd in varchar2) return varchar2
as
language java
name ‘Df.getFS(java.lang.String) return String’;
/

Ok, let’s see if my files can autoextend

select file_name, BYTES/1024 K, INCREMENT_BY*BYTES/BLOCKS/1024 INC, MAXBYTES/1024 MAXKBYTES, GETFREE(FILE_NAME) FREE, GETFS(FILE_NAME) FS
from dba_data_files

FILE_NAME K INC MAXKBYTES FREE FS
—————————————— ———- ———- ———- ———- ————–
/dbms/oracle/LSC68/data/system01LSC68.dbf 332800 25600 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/undo01LSC68.dbf 184320 2048 204800 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/sysaux01LSC68.dbf 228352 25600 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/users01LSC68.dbf 24576 2048 2097152 3579528 /dev/lsc68
/dbms/oracle/LSC68/data/sysaud01_LSC68.dbf 4096 5120 204800 3579528 /dev/lsc68
/app/oracle/product/10.1.0.3/dbs/t.dbf 1024 0 0 851784 /dev/ora10103

Sounds good! plenty of free space to let the files grow!

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);
>
> insert into cake_owners values (1,100);
> insert into cake_owners values (1,200);
> insert into cake_owners values (2,200);
> insert into cake_owners values (2,300);
> insert into cake_owners values (3,300);
> —–
> insert into cake_owners values (4,500);
> —–
> insert into cake_owners values (6,600);
> insert into cake_owners values (7,600);
> —–
> commit;
>
> So owner 1 owns cake 100 and a part of cake 200. Owner 2 owns a part of cake 200 and a part of cake 300 where the reset is owned by 3.
> Owner 4 owns cake 500 alone and cake 600 is owned by 2 persones 6 and 7.
>
> Now I want to place all owners on one table who share parts of their cake so that all cakes can be eaten compleatly without leaving the table.
> The table must be as small as possible and I want to know how many tables are needed and how big each one must be, or who is sitting at it.
> Of course a person can sit only at one table.
>
> In this much simplyfied example I need 3 tables the biggest one needs 3 chairs.

This is typically solved with PL/SQL, but with plain SQL, I need a hierachy (cake=prior cake and ownerprior owner) or (cakeprior cake and owner=prior owner). This is going to loop, with 10g I will use nocycle. With connect by root and count, I will found out the table master with the most guests.

select dense_rank() over (order by rootowner) tableno, owner#
from (
 select owner#,
  row_number() over
   (partition by owner# order by owner_c, rootowner) r,
  rootowner
 from (
  select
   rootowner,
   count(distinct owner#) over
    (partition by rootowner) owner_c,
   owner#
  from (
   select
    owner#, cake#,
    connect_by_root owner# rootowner
   from
    cake_owners
   connect by nocycle
    (owner#prior owner# and cake#=prior cake#)
    or
    (owner#=prior owner# and cake#prior cake#)
   )
  )
 ) where r=1
order by tableno, owner#;

TABLENO OWNER#
——- ——
1       1
1       2
1       3
2       4
3       6
3       7

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 pseudo column LEVEL in hierarchical queries.
One of the biggest problem in hierarchical queries is ORA-01436: CONNECT BY loop in user data.
If you are your own boss, or if you are the boss of your boss, then you have built a cycle. It is probably not wished to have this relation, but it cannot be enforced by a constraint and before 10g, it was difficult to detect. In 10g, you have a new clause, CONNECT BY NOCYCLE, which detect cycles and give flag.

Here I want to present an alternative way of using hierarchies.
Situation:
I have five boxes of five different sizes. Tiny up to 5 liters. Small up to 10 liters. Medium up to 15 liters. Big up to 20 liters. Hudge up to 25 liters.
I have 3 fluids, and I want to find the smallest boxes.
Yeah! very easy, you do select fluids.volume, min(box.capacity) from fluids, box where capacity>=volume group by volume, do not you?
Ok, but I do not want to mix the fluids! So I need 3 different containers.
This means, I will start with the first product, find the smallest box, go to the second, find the smallest free box, and so on.
Hmm… It seems an impossible task with analytics, least, min, keep, lag, lead, ???
Ok, I am going to build a hierarchy, based on volume > prior volume and capacity > prior capacity.
Then I do a min with the sys_connect_by_path function.

select max(sys_connect_by_path(volume,’/’)) volume_path,
ltrim(min(lpad(sys_connect_by_path(capacity,’/’),999))) capacity_path
from box,
(select row_number() over (order by volume) r, count(*) over () c, volume from fluids)
where volume
connect by capacity > prior capacity and r > prior r;

/7/11/14 /10/15/20

There is also one more CONNECT I would like to briefly mention, is the connect without prior.
select level from dual connect by level<11;
It is a special way of creating pivot tables, but it is fairly dangerous, and could make your session / database hang, depending on your oracle version.

Oracle analytics in basic sql queries

When I first saw analytics appearing in Oracle last century, I did not realised they were going to change my way of writting basic SQL queries.

Some (Variance, deviance) are truely mathematical and still reserved for statistical analysis.

Here I will try to describe ROW_NUMBER :

Back in Oracle 7, I remember to have written a lot of reports using in max subqueries, like in

select deptno,ename,sal from emp where (deptno,sal) in (select deptno,max(sal) from emp group by deptno);

With analytics, I can rewrite it with

select deptno,ename,sal
from (
select emp.*, rank() over (
partition by deptno
order by sal desc) r
from emp
) where r=1;

If I want to get exactly one row per deptno, I could then write something like

select deptno,ename,sal
from (
select emp.*, row_number() over (
partition by deptno
order by sal desc) r
from emp
) where r=1;

or better

select deptno,ename,sal
from (
select emp.*, row_number() over (
partition by deptno
order by sal desc, empno) r
from emp
) where r=1;

row_number will select only one row. I prefer the second, because empno is a primary key and the result will be constant over time. In the first solution, I cannot determine which row will be returned, and Oracle may choose one on the morning, and another one in the afternoon, depending on the execution plan (new index/new stats/different load/…).

Note that I can also select the 2nd biggest salary (r=2), or the top 5 (r<=5) It is also very performant, because you are doing only one full table scan instead of two