Tuesday, July 7, 2015

Longer names coming to Oracle?

Have you ever, like me, been silently finger-counting the number of characters in a table name, column name or procedure name that you are about to create in the Oracle Database, to make sure it is short enough? It appears those days will soon (?) be at an end...

The current limit on names (identifiers such as table names, column names, procedure names, parameter names, etc) in Oracle SQL and PL/SQL is 30 characters. Here is what you see when you do a describe of the the user_tab_columns dictionary view in Oracle 11g:



But what do we see if we do the same on an Oracle 12c database? The database on apex.oracle.com was upgraded to 12c some time ago, so let's try the same there:


Interesting...! Looks like the new limit will be 128 characters. All the relevant views such as user_arguments, user_identifiers and user_objects have been likewise altered.

Mind you, as of version 12.1.0.2.0, it's still not possible to actually create objects with long names:



But it appears that the preparations have been done to allow longer names, so I'm guessing this will become possible in the next version (12.2?).

The world moves forward... it will be great to stop worrying about short names (but let's not go crazy and turn our PL/SQL into the equivalent of this silliness !).


3 comments:

Jeffrey Kemp said...

I'm not so optimistic, although it would be great to see this restriction lifted.

I believe the increased size limit in the data dictionary is due to the need to support long database link names and long VPD identifiers:

https://docs.oracle.com/database/121/SQLRF/sql_elements008.htm#SQLRF51129

http://docs.oracle.com/database/121/DBSEG/release_changes.htm#BABJIGDE

Morten Braten said...

@Jeffrey: Thanks for the links, maybe you are right. I remain cautiously optimistic that this might also apply to table, column, parameter and variable names in a future version... :-)

- Morten

Anonymous said...

@Morten: I know your post is from a year ago, but just today I ran into the following page on OTN.

https://community.oracle.com/ideas/3338

This poll was recently closed. Maybe your optimism will be rewarded after all. :)