Oracle 11gR2 feature of the Day!


I did this on a CentOS 5 box with Oracle 11.2.0.1 and it was to show the latest new hint that you can use when transporting lines from one table to another, and there are colisions due to unique/primary keys.

The example is hilarious!

SQL> create table exemplo (
 2  col1 number primary key,
 3  col2 varchar2(100));

Table created.

SQL> insert into exemplo values (1,'Primeira Linha');
1 row created.

SQL> insert into exemplo values (2,'Segunda Linha');
1 row created.

SQL> commit;
Commit complete.

SQL> — NOW HERE IT COMES THE 11gR2 TWEAK
SQL> — Create a new table based on EXEMPLO

SQL> create table exemplo2 as select * from exemplo;
Table created.

SQL> — Insert an extra line in this new table

SQL> insert into exemplo2 values (3,'Terceira Linha');
1 row created.

SQL> commit;
Commit complete.

SQL> — And NOW if we insert all the lines from the 2nd table into the first

SQL> — The line 1 and 2 will colide

SQL> insert into exemplo select * from exemplo2;
insert into exemplo select * from exemplo2
*
ERROR at line 1:
ORA-00001: unique constraint (LMC.SYS_C0014102) violated

SQL> — Buahhhhh But I wanted Oracle to be smart without me having the need to learn PL/SQL…
SQL> — What? It is possible now?
SQL> — How?
SQL> — Like this:

SQL> insert --+IGNORE_ROW_ON_DUPKEY_INDEX
 2  into exemplo select * from exemplo2;
insert --+IGNORE_ROW_ON_DUPKEY_INDEX
*
ERROR at line 1:
ORA-00001: unique constraint (LMC.SYS_C0014102) violated

SQL> — What did I did wrong?
SQL> — See …

SQL> insert --+IGNORE_ROW_ON_DUPKEY_INDEX(exemplo(col1))
 2  into exemplo select * from exemplo2;
1 row created.

SQL> commit;
Commit complete.

SQL> select * from exemplo;

 COL1 COL2
---------- ------------------------------
 1 Primeira Linha
 2 Segunda Linha
 3 Terceira Linha

SQL> — The 3rd line just went in and the colisions ignored!!
SQL> — Damn I’m good!

About these ads

One thought on “Oracle 11gR2 feature of the Day!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s