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.
   

ORA-03113 and Bulk Collect

It can be quite frustrating when you are working on a database package and your session is terminated time and time again and leaves you clueless as too why this is happening.

The Case for the Case Statement

Oracle 9i introduced the CASE Statement and CASE Expressions.
Some say that the CASE statement is a drop-in replacement of the IF statement, but there is a subtle difference between these two.
In this post I will explain the difference between the Case statement and the IF statement. Sometimes it's "better" to change the IF statement to a CASE statement.

Value Error and Invalid Number

Currently I'm in the process on reviewing some chapters on an upcoming book... more on that in a later blog, 'cause I am not sure if I can talk about it or not.

One of the chapters is on "Exceptions" and I noticed a line from the Oracle documentation which I didn't notice before. The VALUE_ERROR (ORA-6502) and INVALID_NUMBER (ORA-1722) are predefined exception, which can be handled by name in your exception handler.
Quote from the docs:
"...In procedural statements, VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements, INVALID_NUMBER is raised.)"

Let's first check the latter part, about the SQL statements:

SQL> select to_number ('a')
  2    from dual
  3  /
select to_number ('a')
                  *
ERROR at line 1:
ORA-01722: invalid number

Now let's check the procedural part, in PL/SQL.
Based on the description, this statement would cause a VALUE_ERROR exception

SQL> declare
  2     n number;
  3  begin
  4     n := 'a';
  5  exception
  6    when value_error
  7    then
  8      dbms_output.put_line ('Value Error');
  9  end;
 10  /
Value Error

PL/SQL procedure successfully completed.

...and it does.

But what if you would use a SQL statement in PL/SQL?
Would this also raise a VALUE_ERROR?

SQL> declare
  2    n number;
  3  begin
  4     select 'a'
  5       into n
  6       from dual
  7     ;
  8  exception
  9    when value_error
 10    then
 11      dbms_output.put_line ('Value Error');
 12  end;
 13  /
Value Error

PL/SQL procedure successfully completed.

Yes, it does. In accordance with the documentation, doing a conversion in procedural statement will raise the VALUE_ERROR exception,...
One last option, let's use the first SQL statement from this blog and use it in PL/SQL:

SQL> declare
  2    n number;
  3  begin
  4    select to_number('a')
  5      into n
  6      from dual
  7    ;
  8  exception
  9    when value_error
 10    then
 11      dbms_output.put_line ('Value Error');
 12    when invalid_number
 13    then
 14      dbms_output.put_line ('Invalid Number');
 15  end;
 16  /
Invalid Number

PL/SQL procedure successfully completed.

As you can see, it raises the INVALID_NUMBER exception. I expected that it would raise the VALUE_ERROR as well.

Tracing the statements reveals the explanation. With the implicit datatype conversion, this is the statement that is executed by the SQL engine (I added some text to the statement to identify the actual statement):

SELECT /*+ implicit_conversion */'a' 
FROM
 DUAL 

and with the explicit datatype conversion, this SQL statement gets executed

SELECT /*+ explicit_conversion */ TO_NUMBER('a') 
FROM
 DUAL 

Sometimes it's too easy to ignore these little things. I learned something about exceptions again.
Link:
Oracle Documentation on Predefined PL/SQL Exceptions