Addition is supposed to be associative.
a+(b+c)=(a+b)+c

This may be wrong in Oracle when dealing with months and days

with t as (select
 interval '1' month a,
 date '2007-09-26' b,
 interval '7' day c
from dual)
select a+(b+c),(a+b)+c 
from t;

A+(B+C)     (A+B)+C
----------- -----------
03-NOV-2007 02-NOV-2007

The equality is supposed to be transitive
if (a=b and b=c) then (a=c)
However, in Oracle the comparison operator equal may imply some transformation

with t as (select '.0' a, 0 b, '0.' c from dual) 
select 
  case when a=b then 'YES' else 'NO ' end "A=B",
  case when b=c then 'YES' else 'NO ' end "B=C",
  case when a=c then 'YES' else 'NO ' end "A=C"
from t;
A=B B=C A=C
--- --- ---
YES YES NO

The equality operator is also supposed to be reflexive
a=a

This is unfortunately wrong with null

with t as (select null a from dual) 
select case when a=a then 'YES' else 'NO ' end "A=A" 
from t;
A=A
---
NO

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

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;

Formatting is very important, it makes your code nice to read and indentation make the blocks visualable.

Auto-formatting is also fine, but I like to decide myself if the line is too long, or if I want to have FROM and EMP on the same line.

Have a look at the free online SQL Formatter SQLinForm

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 union all 
  select 'F' from dual)
;

I am so happy

Let’s try a few queries

SQL> desc t
 Name              Null?    Type
 ----------------- -------- ------
 F                          NUMBER

SQL> select f from t;

         F
----------
         ~

SQL> select f/2 from t;
select f/2 from t
        *
ERROR at line 1:
ORA-01426: numeric overflow

SQL> select -f from t;

        -F
----------
        -~

SQL> select cast(f as binary_double) from t;

CAST(FASBINARY_DOUBLE)
----------------------
                   Inf

SQL> select * from t
  2  where cast(f as binary_double) = binary_double_infinity;

         F
----------
         ~

Now expect a lot of bugs with your oracle clients

Toad 9 for example returns

SQL>  select f from t
select f from t
              *
Error at line 1
OCI-22065: number to text translation for the given 
format causes overflow

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 the employee at that date, you could by mistake believe the following works

select deptno, 
  max(firstname) keep (dense_rank last 
    order by hiredate) firstname,
  max(lastname) keep (dense_rank last 
    order by hiredate) lastname,
  max(hiredate) hiredate
from t
group by deptno;

    DEPTNO FIRSTNAME  LASTNAME   HIREDATE 
---------- ---------- ---------- ---------
        10 Jack       River      02-FEB-02

This will produce wrong result if hiredate is not unique

insert into t values (10,'Bob','Zhong', date '2002-02-02');
select deptno, 
  max(firstname) keep (dense_rank last 
    order by hiredate) firstname,
  max(lastname) keep (dense_rank last 
    order by hiredate) lastname,
  max(hiredate) hiredate
from t
group by deptno;

    DEPTNO FIRSTNAME  LASTNAME   HIREDATE 
---------- ---------- ---------- ---------
        10 Jack       Zhong      02-FEB-02

of course there is Jack Zhong.

To get a consistent record, it is possible to add all the columns in the order by

select deptno, 
  max(firstname) keep (dense_rank last 
    order by hiredate,firstname,lastname) firstname,
  max(lastname) keep (dense_rank last 
    order by hiredate,firstname,lastname) lastname,
  max(hiredate) hiredate
from t
group by deptno;

    DEPTNO FIRSTNAME  LASTNAME   HIREDATE 
---------- ---------- ---------- ---------
        10 Jack       River      02-FEB-02

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 a LONGER string

select 
  regexp_substr(
    to_char(
      timestamp '9999-12-31 23:59:59.999999999',
'FF9SP/FMDAY MONTH DDTHSP YYYYSP A.D. HH24SP MISP SSSP')
    ,'[^/]+')X
from
  dual;

X                                                                                          
---------------------------------------------------------
----------------------------------
NINE HUNDRED NINETY-NINE MILLION NINE HUNDRED NINETY-NINE 
THOUSAND NINE HUNDRED NINETY-NINE

I do update t set x=:a;

If I do it twice, I am doing a lot of unnecessary updates. This is true in an update, and also in the update clause of a MERGE.

I need to take care of null, I can update null with something, or something with null, but update null with null is also unnecessary.

SQL> update t set x=:a;

4977 rows updated.

Elapsed: 00:00:00.34
SQL> update t set x=:a;

4977 rows updated.

Elapsed: 00:00:00.32
SQL> update t set x=:a
  2  where x!=:a
  3  or (x is null and :a is not null)
  4  or (x is not null and :a is null);

0 rows updated.

Elapsed: 00:00:00.04

SQL> create or replace type t is 
 2  table of varchar2(12);
 3  /
Type created.

SQL> create or replace function f return t is 
 2  begin return t('foo'); end;
 3  /
Function created.

SQL> select * from table(f);

COLUMN_VALUE
------------
foo         

SQL> create or replace type o is 
 2  object(BAR varchar2(12));
 3  /
Type created.

SQL> create or replace type t is 
 2  table of o;
 3  /
Type created.

SQL> create or replace function f return t is 
 2  begin return t(o('foo')); end;
 3  /
Function created.
SQL> select * from table(f);

BAR
------------
foo         

Lazyness at the development can have dramatic costs in production and maintenance. I want to summarize why and where you should always qualify your columns.

Why? when you select or modify data from a table, you must qualify the columns you are using so if the order of the column change, or if one column is added, renamed or removed, so long this column is not related to your query your code should keep working. A typical example is when the dba add a LAST_MODIFICATION_DATE and a trigger to automatically fill that column for auditing purpose. It should never make your application fail.

0) sample table

create table t1(x number, y number);
create table t2(a number, b number);
create table t3(x number, z number);

bad:

insert into t1
select * from t2;

insert into t1(x,y)
select t2.a,t2.b from t2;

bad:

insert all
when a>0 then into t1
select * from t2;

insert all
when a>0 then into t1(x,y)
select t2.a,t2.b from t2;

2) merge
bad:

merge into t1
using t2 on (x=a)
when matched then
  update set y=b
  where (b>y)
delete
  where (b>y)
when not matched then
  insert values(a,b)
  where (b>0);

merge into t1
using t2 on (t1.x=t2.a)
when matched then
  update set y=t2.b
  where (t2.b>t1.y)
delete
  where (t2.b>t1.y)
when not matched then
  insert (x,y) values(t2.a,t2.b)
  where (t2.b>0);

Not prefixing the column here will bug as soon as a column a or b is added to table t1

2) joins

just never use natural join in production

bad:

select *
from t1 natural join t3;

select x, t1.y, t3.z
from t1 join t3 using (x);

bad:

select *
from t1,t2
where x=a;

select t1.x,t1.y,t2.a,t2.b
from t1,t2
where t1.x=t2.a;

It is not about good looking code, it is about data quality and stability

Views:

SQL> create view v1 as select * from dual;

View created.

SQL> rename v1 to v2;

Table renamed.

SQL> create table t1 as select * from dual;

Table created.

SQL> rename t1 to t2;

Table renamed.

SQL> create sequence s1;

Sequence created.

SQL> rename s1 to s2;

Table renamed.

SQL> create synonym syn1 for dual;

Synonym created.

SQL> rename syn1 to syn2;

Table renamed.

Constraints :

SQL> create table t(x number not null);

Table created.

SQL> select constraint_name
  2  from user_constraints
  3  where table_name='T';

CONSTRAINT_NAME
------------------------------
SYS_C0018686

SQL> alter table t rename constraint SYS_C0018682 to X_NOT_NULL;

Table altered.

Indexes :

SQL> create index i1 on t(x);

Index created.

SQL> alter index i1 rename to i2;

Index altered.

Columns :

SQL> alter table t rename column x to y;

Table altered.

Partitions :

SQL> create table t (x number, y number)
  2  partition by range(x)
  3    subpartition by hash(y) (
  4  partition p1 values less than(1)
  5    (subpartition sp1)
  6  ) ;

Table created.

SQL> alter table t rename partition p1 to p2;

Table altered.

Subpartitions:

SQL> alter table t rename subpartition sp1 to sp2;

Table altered.

Triggers:

SQL> create trigger tr1 after update on t 
  2  begin null; end;
  3  /

Trigger created.

SQL>
SQL> alter trigger tr1 rename to tr2;

Trigger altered.

For the dba, you can also rename datafile, logfile and even tablespaces (10g)

the following works only on 10gR2

Table PRODUCTCATEGORY:

Table TMP_PRODUCT:

Table PRODUCT:

Now I want to load product

SQL> insert into product select * from tmp_product;
insert into product select * from tmp_product
*
ERROR at line 1:
ORA-02291: integrity constraint (AUDBA.SYS_C0016368) 
violated - parent key not found

Well, there is a missing category. I will fix it later. I want to load the valid data now.

SQL> exec dbms_errlog.create_error_log('PRODUCT')

PL/SQL procedure successfully completed.

SQL> insert into product select * from tmp_product
  2  log errors reject limit unlimited;

1 row created.

Table PRODUCT:

Now I have successfully loaded my valid data. I need to fix the problem. Let’s identify the offending row :

SQL> select ORA_ERR_OPTYP$,ORA_ERR_MESG$,CATEGORY
  2  from err$_product;

ORA_ERR_OPTYP$ ORA_ERR_MESG$                    CATEGORY
-------------- -------------------------------- --------
I              ORA-02291: integrity constraint  Meat
               (AUDBA.SYS_C0016368) violated -
               parent key not found

1 row selected.

Let’s create the missing category manually

SQL> insert into productcategory(category) values ('Meat');

1 row created.

SQL> insert into product 
  2  select * from tmp_product 
  3  where category='Meat';

1 row created.

Table PRODUCTCATEGORY:

Table PRODUCT:

With merge you can insert, update or both

I want to import changes from one table in another table.

Table T1:

Table T2:

To insert the missing row from T2 into T1, I could need the following subquery with INSERT.

insert into t1 
select * from t2 
where not exists (
  select * from t1 
  where t1.id=t2.id);

merge into t1 using t2 
on (t1.id=t2.id) 
when not matched then 
  insert values(t2.id,t2.name);

And what about updating the T1 table with the T2 values

update t1 
set (name)=(select name from t2 where id=t1.id)
where id in (select id from t2);

merge into t1 using t2 on (t1.id=t2.id) 
when matched then 
  update set name=t2.name;

Quite nice, is not it! I find this syntax very intuitive.

Apart from syntax looking, Merge can also combine insert and update in once, which make it more stable (only one statement) and more performant (table t2 is read only once).

merge into t1 using t2 on (t1.id=t2.id) 
when not matched then 
  insert values(t2.id,t2.name)
when matched then 
  update set name=t2.name;

In part 1, I tried with pure SQL+XML, but empty columns were missing. In Part 2, I had a mix between PL/SQL and XML functions. Now I would like to publish the solution Vadim Tropashko posted in the OTN Forums. It is a NO XML pipelined function. I did a bit formatting in it. And I pipe clobs.

Update: I added the column header

create or replace type strings as table of clob;
/
 
create or replace function CSV(txt varchar2)
return strings pipelined is 
  tmp clob;
  curid NUMBER; 
  desctab DBMS_SQL.DESC_TAB; 
  colcnt NUMBER; 
  namevar VARCHAR2(4000); 
begin
  curid := dbms_sql.open_cursor;
  dbms_sql.parse(curid, txt, dbms_sql.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
  FOR i IN 1 .. colcnt LOOP
    DBMS_SQL.DEFINE_COLUMN(curid, i, namevar,4000);
  END LOOP;
  if DBMS_SQL.execute(curid) = 0 THEN
    dbms_lob.createtemporary(tmp, true);
  FOR i IN 1 .. colcnt LOOP
    if (i>1) then
        dbms_lob.writeappend(tmp, 1, ';');
      end if;
      dbms_lob.writeappend(tmp, 
        length(desctab(i).col_name), 
        desctab(i).col_name);
    END LOOP;
    pipe row(tmp);
  dbms_lob.freetemporary(tmp);
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
    dbms_lob.createtemporary(tmp, true);
    FOR i IN 1 .. colcnt LOOP
      if (i>1) then
      dbms_lob.writeappend(tmp, 1, ';');
        end if;
      DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
      if (namevar is not null) 
    then
      dbms_lob.writeappend(tmp, length(namevar), 
                    namevar);
    end if;
    END LOOP;
    pipe row(tmp);
    dbms_lob.freetemporary(tmp);
  END LOOP;
  END IF;
  DBMS_SQL.CLOSE_CURSOR(curid);
end;
/

select * from table(csv('select * from emp'));

EMPNO;ENAME;JOB;MGR;HIREDATE;SAL;COMM;DEPTNO
7369;SMITH;CLERK;7902;17-DEC-80;800;;20
7499;ALLEN;SALESMAN;7698;20-FEB-81;1600;300;30
7521;WARD;SALESMAN;7698;22-FEB-81;1250;500;30
7566;JONES;MANAGER;7839;02-APR-81;2975;;20
7654;MARTIN;SALESMAN;7698;28-SEP-81;1250;1400;30
7698;BLAKE;MANAGER;7839;01-MAY-81;2850;;30
7782;CLARK;MANAGER;7839;09-JUN-81;2450;;10
7788;SCOTT;ANALYST;7566;19-APR-87;3000;;20
7839;KING;PRESIDENT;;17-NOV-81;5000;;10
7844;TURNER;SALESMAN;7698;08-SEP-81;1500;0;30
7876;ADAMS;CLERK;7788;23-MAY-87;1100;;20
7900;JAMES;CLERK;7698;03-DEC-81;950;;30
7902;FORD;ANALYST;7566;03-DEC-81;3000;;20
7934;MILLER;CLERK;7782;23-JAN-82;1300;;10