Monday, October 30, 2017

Oracle 12c column alias length limit workaround

Oracle, in older version, has some limitations on column alias name length. You cannot exceed 30 characters. On my project, I use Hibernate to access database and everythings work when Hibernate plays in SQL. But, my colleague who tried to grab some data from a PL/SQL script had the following error :

ORA-00972 : Identifier is too long

Humm...  F***! So, three solutions to solve this issue :

  • ask your DBA to migrate to Oracle 12cR2  which allows column alias names up to 128 characters
  • alter your database and change your application to fit this new shorter name
  • find a working workaround !!!!

Here is this workaround : the idea is to make a virtual column which contains the same data but with a shorter alias name.  Example : 

alter table MYTABLE add NEW_COL date generated always AS (MY_COL_WITH_VERY_LONG_NAME - 0) virtual;

The value in the virtual column must be calculated. The trick is to make a dummy operation (like value-0). 

Whooo... that's cool. So cool that I wanted to make other operation. I added a generated column which returns the delta in days between to dates : 

alter table MYTABLE add DELTA int generated always AS (START_DATE - END_DATE) virtual;

I love it!

No comments:

Download Oracle JDK from command line

Found on the web. You can adapt the url with one grabbed from Oracle JDK download page. What I love here is the fact is send Oracle licence ...