Month: August 2007

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 […]

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; […]

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 […]

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. […]

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 […]

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 […]

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

Shay Shmeltzer posted the link to the Oracle Openworld 2007 sessions : http://www.cplan.com/oracleopenworld2007/sanfrancisco/cc

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 […]

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 […]

Congratulation to Eddie Awad for becoming an Oracle ACE Director !

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 […]

I will go to Oracle OpenWorld in San Francisco this year again

http://www.oracle.com/technology/software/products/database/index.html

REMINDER: Premier Support for Oracle Database 9.2 ends in July 2007 The 9iR2 premier support just ended. Now the 9i is under Extended Support. The good news, it is free until July 2008! First year Extended Support fee waived for Oracle9i Database Release 9.2

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 […]