16 October 2015

Updating Identity Columns

During my presentation "Oracle 12c for Developers" at the Sloveninan and Croatian User Groups I got the same question twice about Identity Columns:

Is it possible to update an Identity Column?
During the presentation I show how it is not possible to insert a value for a "Generated Always Identity" column.
Let's take a look at an example:
SQL> create table t
  2  (id number  generated as identity
  3  ,name varchar2(35)
  4  );

Table created.

SQL> insert into t(name) values ('hello')
  2  /

1 row created.
   
In the first section the table is created with a "Generated Always Identity" column.
The second part shows that you can insert into the table, as long as the identity column isn't used in the insert statement. Trying to do so will lead to an exception.
SQL> insert into t values (1, 'World')
  2  /
insert into t values (1, 'World')
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
   
The same is true when you attempt to insert a record type:
SQL> declare
  2     r t%rowtype;
  3  begin
  4     r.id := 42;
  5     r.name := 'world';
  6     insert into t values r;
  7  end;
  8  /
declare
*
ERROR at line 1:
ORA-32795: cannot insert into a generated always identity column
ORA-06512: at line 6
   
Now back to the question, what about an update? Let's try some different methods:
SQL> update t
  2     set id = id
  3  /
   set id = id
       *
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
SQL> update t
  2     set id = id + 1
  3  /
   set id = id + 1
       *
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
SQL> update t
  2     set id = id + 0
  3  /
   set id = id + 0
       *
ERROR at line 2:
ORA-32796: cannot update a generated always identity column
   
In short: updating a "Generated Always Identity" column is not allowed either.
One final remark: an identity column is not the same as a primary key. If you want to use a "Generated Always Identity" column as your primary key, then you will have to specify that explicitly.
SQL> create table t
  2  (id number  generated as identity primary key
  3  ,name varchar2(35)
  4  );

Table created.
   

12 October 2015

Busy October

October is going to be a busy month, and it already started.
The First one is the Slovenian User Group immediately followed by the Kroatian User Group.

For the Sloveninan User Group (SIOUG) I will do the following presentations:

    • Oracle 12c for Developers
      Analytic Functions: Unleash the Power
      APEX, Node.js and HTML5: Magic!
  • On top of that I was asked to join the "Ask the Experts" panel on APEX.

    In Kroatia (for the HrOUG) these presentations are on the agenda:

    • Oracle 12c for Developers
      Analytic Functions: Unleash the Power
  • Next week wednesday Oracle Open World kicks off with the ACE Director Briefing and of course OOW itself.
    On the Usergroup day (Sunday) I will participate in two presentations. First "APEX, Node.js and HTML5: Magic!" and later that day I have a 7-minute session during the EOUC "12 more things about Oracle 12c".
    The last session is going to be a fun one. As the title claims there are going to be more than 12 topics in one session with a lot of excellent speakers.

    Jonathan Lewis Less well-known enhancements of the 12c Optimizer
    Julian Dontcheff Oracle Database 12c In-Memory Advisor
    Gurcan Orhan Adapting DB 12C In-Memory to ODI 12c
    Osama Mustafa How to plugin a non-CDB database to a Container Database (CDB)
    Bjoern Rost How ASM has reached maturity in 12c
    Alex Nuijten JSON in the database
    Brendan Tierney Running R in the Database using Oracle R Enterprise
    Douwe Pieter van den Bos Maximum security architecture
    Christian Antognini Real-time Monitoring of Composite Database Operations
    Martin Widlake 12C - Clustered Data aware TABLE_CACHED_BLOCKS
    Heli Helskyaho Design your 12c Databases using Oracle SQL Dev Data Modeler
    Oded Raz Oracle 12c Privileges Analysis
    Tim Hall
    Alex Zaballa
    Maria Colgan
    Lucas Jemella

    I believe you can still register for this session.
    More Than Another 12 on Oracle Database 12c [UGF3190] Moscone South Room 306 time 13:30 - 15:15

    By the time OOW is done, so is October.