flashback archive table

One of the problem with flashback queries in 10g and before is that you never know if it will works, especially you cannot expect to have flashback queries working for very old tables. Let’s imagine you want to export your CUSTOMER as of 30/6/2007. No chance in 10g… Well, with 11g, you can create a […]

select*from”EMP”where’SCOTT’=”ENAME”…

What is wrong with this query? select*from”EMP”where’SCOTT’=”ENAME”and”DEPTNO”=20; EMPNO ENAME JOB MGR HIREDATE ———- ———- ——— ———- ——— 7788 SCOTT ANALYST 7566 13-JUL-87 It is a zero-space query 😎 You could write it as select * from “EMP” where ‘SCOTT’=”ENAME” and “DEPTNO”=20; personnaly, I would write it as select * from emp where ename=’SCOTT’ and deptno=20; […]

positive infinity

I have read a long long time ago the following note on positive infinity http://www.ixora.com.au/notes/infinity.htm Today I finally succeeded in inserting positive infinity in a number field create table t as select STATS_F_TEST(cust_gender, 1, ‘STATISTIC’,’F’) f from ( select ‘M’ cust_gender from dual union all select ‘M’ from dual union all select ‘F’ from dual […]

on delete cascade

The use of a referential integrity constraint is to enforce that each child record has a parent. SQL> CREATE TABLE DEPT 2 (DEPTNO NUMBER PRIMARY KEY, 3 DNAME VARCHAR2(10)) ; Table created. SQL> CREATE TABLE EMP 2 (EMPNO NUMBER PRIMARY KEY, 3 ENAME VARCHAR2(10), 4 DEPTNO NUMBER 5 CONSTRAINT EMP_DEPT_FK 6 REFERENCES DEPT(deptno)); Table created. […]

errorlogging in 11g

This is a very neat feature in 11g. I have a script called foo.sql create table t(x number primary key); insert into t(x) values (1); insert into t(x) values (2); insert into t(x) values (2); insert into t(x) values (3); commit; It is eyes-popping that this script will return an error, but which one? Let’s […]

the password is not longer displayed in dba_users.password in 11g

By reading Pete Finnigan’s Oracle security weblog today, I discovered that the password is no longer displayed in DBA_USERS in 11g. select username,password from dba_users where username=’SCOTT’; USERNAME PASSWORD ——– —————————— SCOTT select name,password from sys.user$ where name=’SCOTT’; NAME PASSWORD —– —————————— SCOTT F894844C34402B67 on the one hand, it is good for the security. On […]

11g certification matrix

as of today, 24-AUG-2007, here is the certification matrix for Linux x86 OS Product Status SLES-10 11g Certified Red Hat Enterprise AS/ES 5 11g Certified Red Hat Enterprise AS/ES 4 11g Certified Oracle Enterprise Linux 5 11g Certified Oracle Enterprise Linux 4 11g Certified

keep dense_rank with multiple column

create table t( deptno number, firstname varchar2(10), lastname varchar2(10), hiredate date); insert into t values ( 10,’Jo’,’Smith’,date ‘2001-01-01′); insert into t values ( 10,’Jack’,’River’,date ‘2002-02-02’); to get the latest hiredate per department select deptno, max(hiredate) hiredate from t group by deptno; DEPTNO HIREDATE ———- ——— 10 02-FEB-02 if you want to get the name of […]

get Nth column of a table

I answered this question twice, once on otn forums and once on developpez.net Here is the latest to get the third column of emp select column_name as name, extractvalue(column_value,’/ROW/’||column_name) as value from table(xmlsequence(cursor(select * from emp))), user_tab_columns where COLUMN_ID=3 and table_name=’EMP’ ; NAME VALUE —- ———- JOB CLERK JOB SALESMAN JOB SALESMAN JOB MANAGER JOB […]

How to compare schema

If you have receive ddl statements from your developer and you want to check if it matches the current state of the development database, because the developer have done a lot of change in a quick and undocumented manner, what are your options? I found this handy feature in Toad : 1) I create my […]

how to spell 999999999 ?

begin dbms_output.put_line( to_char( timestamp ‘9999-12-31 23:59:59.999999999’, ‘FF9SP’)); end; / NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE THOUSAND NINE HUNDRED NINETY-NINE Unfortunately, I could not get this in sql/10.2.0.2 select to_char( timestamp ‘9999-12-31 23:59:59.999999999’, ‘FF9SP’) X from dual; ORA-01877: string is too long for internal buffer Well, since the string is too long, let’s try with […]