Just updated my photo ! Thanks to pion.ch photograph:-)

select column only if it exists

i need to display tablespace attributes

SQL> select tablespace_name, status, contents, logging, extent_management, allocation_type, segment_space_management, retention, bigfile from dba_tablespaces;
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG
-------------------- --------- --------- --------- ---------- --------- ------ ----------- ---
SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO

looks fine. but what if I try that on my oracle7 database? I will get ORA-00904: invalid column name

ok, I will then select only the columns that exist! than select from dba_tablespaces

t.sql:
set termout off
def logging=""
def extent_management=""
def allocation_type=""
def segment_space_management=""
def retention=""
def bigfile=""
col logging new_v logging
col extent_management new_v extent_management
col allocation_type new_v allocation_type
col segment_space_management new_v segment_space_management
col retention new_v retention
col bigfile new_v bigfile
select ',logging' logging from dba_tab_columns where owner='SYS' and table_name='DBA_TABLESPACES' and COLUMN_NAME='LOGGING';
select ',extent_management' extent_management from dba_tab_columns where owner='SYS' and table_name='DBA_TABLESPACES' and COLUMN_NAME='EXTENT_MANAGEMENT';
select ',allocation_type' allocation_type from dba_tab_columns where owner='SYS' and table_name='DBA_TABLESPACES' and COLUMN_NAME='ALLOCATION_TYPE';
select ',segment_space_management' segment_space_management from dba_tab_columns where owner='SYS' and table_name='DBA_TABLESPACES' and COLUMN_NAME='SEGMENT_SPACE_MANAGEMENT';
select ',retention' retention from dba_tab_columns where owner='SYS' and table_name='DBA_TABLESPACES' and COLUMN_NAME='RETENTION';
select ',bigfile' bigfile from dba_tab_columns where owner='SYS' and table_name='DBA_TABLESPACES' and COLUMN_NAME='BIGFILE';
set termout on ver off
select tablespace_name, status, contents &logging &extent_management &allocation_type &segment_space_management &retention &bigfile from dba_tablespaces;


let's try

SYS@LSC69 AS SYSDBA/7.3.4.5
SQL> @t
TABLESPACE_NAME STATUS CONTENTS
-------------------- --------- ---------
SYSTEM ONLINE PERMANENT

SYS@LSC65 AS SYSDBA/8.1.7.4
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO
-------------------- --------- --------- --------- ---------- ---------
SYSTEM ONLINE PERMANENT LOGGING DICTIONARY USER

SYS@LSC67 AS SYSDBA/9.2.0.6
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN
-------------------- --------- --------- --------- ---------- --------- ------
SYSTEM ONLINE PERMANENT LOGGING DICTIONARY USER MANUAL

SYS@LSC63 AS SYSDBA/10.2.0.1
SQL> @t
TABLESPACE_NAME STATUS CONTENTS LOGGING EXTENT_MAN ALLOCATIO SEGMEN RETENTION BIG
-------------------- --------- --------- --------- ---------- --------- ------ ----------- ---
SYSTEM ONLINE PERMANENT LOGGING LOCAL SYSTEM MANUAL NOT APPLY NO

one script for any version

dynamic number of columns

probably one of the most frequently asked question, you want to have a table like


            ACCOUNTING OPERATIONS RESEARCH SALES
ANALYST              0          0        2     0
CLERK                1          0        2     1
MANAGER              0          0        1     1
PRESIDENT            1          0        0     0
SALESMAN             0          0        0     4

but the number and name of columns must be dynamic.

typically, I answer : this is not possible in plain sql. you need to use plsql.

well. It is not too much beautifoul, but I have a suggestion

step one:
generate the select

spool crosstab.sql
select text from (
  select 1 i, 'select job' text from dual
  union all
  select 2+rownum, ', count(decode(deptno,'||deptno||',deptno)) '||dname from
    (select deptno,dname from dept order by dname)
  union all
  select 1e125, ' from emp group by job order by job;' from dual
)
order by i
/
spool off

step two:
execute the generated query
@crosstab
JOB       ACCOUNTING OPERATIONS   RESEARCH      SALES
--------- ---------- ---------- ---------- ----------
ANALYST            0          0          2          0
CLERK              1          0          2          1
MANAGER            1          0          1          1
PRESIDENT          1          0          0          0
SALESMAN           0          0          0          4


done!

10gR2

Well, the virus scanner did not let me download it 10gR2 Aix on monday. I called the IT-support. Finally, they allowed me to bypass the virus scanner and I managed to download the software + the doc within 10 minutes. Not bad!

Ok, the installer complained my maintenance level to be 5200-03. I upgraded it to 5200-06.

The oracle installer is not my best friend. I have many installations per servers, 7.3.4, 8.0.6, 8.1.7 32bits, 8.1.7 64bits (AIX4), 9.2, 10.1, 10.2... with different patch levels, and I consider installing and deinstalling with runinstaller to be a nightmare.

I did not find the "remove software" functionnality on the 10gR2 installer. Strange!

I created a new db. Got a warning that SQL_TRACE is deprecated, so I removed that parameter and my db is up and running.

pivot table

big mission today : explain the mess with pivot table.

let's look the following query
SQL> select extract(year from hiredate) year, count(*) from emp group by extract(year from hiredate) order by year
YEAR COU
----- ---
1980   1
1981  10
1982   1
1987   2


how do we get the years without hiredate with 0?

1) with a table of years
it is fast, and easy to undestand. But it require to create a table

create table year(year number);
insert into year values (1980);
insert into year values (1981);
insert into year values (1982);
insert into year values (1983);
insert into year values (1984);
insert into year values (1985);
insert into year values (1986);
insert into year values (1987);
insert into year values (1988);
insert into year values (1989);

SQL> select year, count(hiredate) COU from emp right join year on (extract(year from hiredate)=year) group by year order by year
YEAR COU
----- ---
1980   1
1981  10
1982   1
1983   0
1984   0
1985   0
1986   0
1987   2
1988   0
1989   0


2) I have a few artifice to select from dual
A. union
SQL> select year, count(hiredate) COU from emp right join
(select 1980 year from dual
union all select 1981 from dual
union all select 1982 from dual
union all select 1983 from dual
union all select 1984 from dual
union all select 1985 from dual
union all select 1986 from dual
union all select 1987 from dual
union all select 1988 from dual
union all select 1989 from dual)
on (extract(year from hiredate)=year) group by year order by year;


ok for 10 rows, but not for 1000!

B. cube
a trick, which may not work in all versions
SQL> select year, count(hiredate) COU from emp right join
(select 1980+rownum year from
(select null from dual group by cube (null,null,null,null))
where rownum<11)
on (extract(year from hiredate)=year) group by year order by year;


not ok if the number of rows to be generated is volatile, sometimes 1, sometimes 1000000

C. connect by
even more dirty tricky, no guarantee that it will work

SQL> select year, count(hiredate) COU from emp right join
(select 1980+level year from dual connect by level<11)
on (extract(year from hiredate)=year) group by year order by year
/


3) use rownum and all_objects
SQL> select year, count(hiredate) COU from emp right join
(select 1980+rownum year from all_objects where rownum<11)
on (extract(year from hiredate)=year) group by year order by year
/


not scalable, you do not know how many rows are in all_objects (500,5000,50000?), and not performant at all, because all_objects is a complex view

4) PL/SQL pipelined table
perfectly scalable, quite fast, requires to create a few objects

SQL> create type t_year as TABLE OF number;
/

Type created.

SQL> create or replace function f_year(n1 number, n2 number) return t_year pipelined is
begin for i in n1..n2 loop pipe row(i); end loop; return; end;
/

SQL> select COLUMN_VALUE year, count(hiredate)
from emp right join
table(f_year((select min(extract(year from hiredate)) from emp), (select max(extract(year from hiredate)) from emp)))
on ( COLUMN_VALUE = extract(year from hiredate) )
group by COLUMN_VALUE
YEAR COU
----- ---
1980   1
1981  10
1982   1
1983   0
1984   0
1985   0
1986   0
1987   2


I like the last one, because you can return 1 or 1000000000 rows, the query will not change. It will be fast for 1 row, and slow but will still work for 1000000000 rows.


pivot table part 2
pivot table part 3

group by does not sort

An user just posted an interresting question today. Why the Group By is crazy?

I summarize his example

SQL> SELECT NUM FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;
    NUM
-------
    400
 220000
    310


Well, group by is "sorting", but how? this seems crazy. Oracle use the sort algorythm he wants. He can ascending-sort, descending-sort, hash-sort, or any other internal algorythm.

Here, 220000 is before 310 because it is smaller in bytes.

Have a look

SELECT num,dump(num) FROM (select 400 num from dual union select 310 from dual union select 220000 from dual) group by num,null;
       NUM DUMP(NUM)
---------- -------------------------
       400 Typ=2 Len=2: 194,5
    220000 Typ=2 Len=2: 195,23
       310 Typ=2 Len=3: 194,4,11


Well, if you need to sort, use order by and read tom blog

Radio Free Tooting

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

return code

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

OpenWorld SanFrancisco

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 :-)