Friday, 2 January 2015

Oracle 12c column upgrades

While playing with 12c I tried the upgrade to the DEFAULT column syntax that now allows sequences.

I came across a basic error, but it's just a small trap for new players.

CREATE TABLE seq_test(a NUMBER)
/

ALTER TABLE seq_test MODIFY (a NUMBER DEFAULT sage_seq.NEXTVAL)
/

SQL Error: ORA-02262: ORA-2289 occurs while type-checking column default value expression

*Cause:    New column datatype causes type-checking error for existing column
           default value expression.
*Action:   Remove the default value expression or don't alter the column
           datatype.

Investigating ORA-02262 returns next to nothing. It's a red herring anyway because I didn't realise the ORA-2289 staring me in the face before I checked if my database had that sequence yet.
ORA-02289: sequence does not exist

Typical APEX table definitions will never be the same again, though an even more elegant solution is to use IDENTITY columns. See the performance benefits at oracle-base.com.

No comments: