Laurent Schneider

Radio Free Tooting is apc blog… much better written than mine! wtg apc

there is a myth of using sql.sqlcode in sqlplus

whenever sqlerror exit sql.sqlcode

this not ok. you should prefer whenever sqlerror exit failure or exit 1

Why? because unix return code is 8 bits long. so if you exit ora-600, you will get 88.

Let’s try it

$ sqlplus “/ as sysdba”
SQL> create user gaston identified by lagaffe quota 1k on users default tablespace users;

User created.

SQL> grant create table to gaston;

Grant succeeded.

SQL> whenever sqlerror exit sql.sqlcode
SQL> create table gaston.x as select * from all_objects;
create table gaston.x as select * from all_objects
*
ERROR at line 1:
ORA-01536: space quota exceeded for tablespace ‘USERS’

Disconnected from …
$ echo $?
0

1536 in binary is 11000000000, so the 8 leftmost bits gives 0, which is definitely not fine in your unix code!

well, there is also a return code from the os, but it stopped working in 10g. it is the _RC defined variable

SQL> host ls /xxx
ls: 0653-341 The file /xxx does not exist.

SQL> def _rc
DEFINE _RC = “2” (CHAR)

many users asked “how to get the returned code of a procedure”. Well, this is a faq, a procedure is returning nothing. but you could have an out variable, then exit that code.

SQL> create or replace procedure p(o out number) is begin o:=1; end;
2 /

Procedure created.

SQL> var rc number
SQL> exec p(:rc)

PL/SQL procedure successfully completed.

SQL> exit :rc
Disconnected …
$ echo $?
1

Well, next month I am going to SF for OOW 2K5. I am invited by Oracle to participate to the Meet the expert session (otn underground). I cannot stand waiting to meet my oracle forums pals alive! I am also taking part to the xtreme sessions and I expect them to be xtreme!

It almost 20 years I have not been in the states. I am very excited !

I already installed DB 10gR2 on my notebook. It has been very easy. I first installed as a virtual machine (vmware) RedHat Entreprise Linux 3 update 3. Then Oracle. My collegues did a little bit complain about the 5 giga download, because the whole network was slow. Only 1 cd is necessary for DB installation. Rest is companion, client, clusterware, grid, etc…

I hope I can get a working 10gR2 grid in SF ! Even a beta release would be great 🙂

How do I get than MONDAY=1, TUESDAY=2, WEDNESDAY=3 … ?

With to_char()

alter session set nls_territory=germany;
select to_char(sysdate,’DAY D’) from dual;
TUESDAY 2

With decode()

select decode(to_char(sysdate, ‘FMDAY’, ‘NLS_DATE_LANGUAGE=american’),’MONDAY’, ‘1’, ‘TUESDAY’, ‘2’, ‘…’)) from dual;

With mod()
As a reference, I take monday Jan 1st, 1000.
select mod(trunc(sysdate)-date ‘1000-01-01’,7)+1 from dual;
2

How do I trunc date to current monday?
trunc(date, ‘D’) or here is with my solution with 1000-01-01:
select trunc((sysdate-date ‘1000-01-01’)/7)*7+date ‘1000-01-01′ from dual;

thanks to jan-marcel idea, I found that one
trunc(date,’IW’) for current monday and date-trunc(date,’IW’)+1 for day number

if I create a function for a materialized view with query rewrite or for a function based index, I must create hte deterministic.

f(x) = x*2 is deterministic. for a give x, f(x) will always be the same, f(5) will be always 10; always.

f(y) = sysdate+y is non-deterministic. For a given y, the return values may vary.

OK, we cannot use sysdate in mviews. What else should I say?

Well, some are less evident to find out!

Some limit cases:
to_date(‘2000′,’YYYY’) is non deterministic, it returns the 1st of current month, 2000, f. ex. 2000-07-01 or 2000-08-01
to_char(date ‘2000-01-01′,’DAY’) is non deterministic, it can deliver SATURDAY or SAMSTAG
to_char(date ‘2000-01-01′,’DAY’,’NLS_DATE_LANGUAGE=american’) is deterministic
to_char(date ‘2000-01-01′,’D’) is non deterministic, it can deliver 6,7,1 or 2, depending what your territory is (in Bangladesh, the week starts on friday)
timestamp ‘2005-10-30 02:30:00 Europe/Zurich’ is per default non deterministic, if you try it in summer, you will get time offset +02:00, and in winter you will get +01:00, because the period 02:00-02:59, Oct 30th, exists in both time zones. This is called time boundaries. I can make it deterministic by setting ERROR_ON_OVERLAP_TIME in the session, in which case boundaries will be rejected.

I can always define a function as deterministic, at my own risk…

For exemple if I have t(id,gender) {1,male;2,female}, I could have a function

f(id)=select gender from t where t.id=f.id;

and I could define it as deterministic. However, if I decide to set id=2 for female later, I am prepared to have corrupted data, wrong results, inconsistencies and even ORA-600 errors.

I just come back from holiday, I am quite busy at the moment. Here is a tiny function to get the age

trunc((to_char(sysdate,’YYYYMMDD’)-to_char(birthdate,’YYYYMMDD’))/10000)

it is much safer than add_months, because add_months do some conversion at the end of the month, and I would never accept to wait until Feb 29th, 2008 (28-2-1990 + 18*12 months) to be 18 if I were born Feb 28th, 1990.

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…

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 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.

4) table not found


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)


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 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 🙂

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

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…

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

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″/>

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

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!

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

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.

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