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

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

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