Skip to main content

Posts

Showing posts from October, 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 charactersalter your database and change your application to fit this new shorter namefind 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 oper…