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 :
Humm... F***! So, three solutions to solve this issue :
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!