Just updated my photo ! Thanks to pion.ch photograph:-)
Month: August 2005
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 […]
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 […]
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 […]
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) […]
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. […]
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 […]
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 […]