Friday, July 13, 2007

Apex: Table and column name limits

I've been reviewing table and column name limits for Oracle Apex to come up with some internal standards.

Oracle supports 30 characters for object names, be it table, column, constraint, trigger, ... whatever.

Historically Oracle Designer used to add suffixes of 4 characters, and some of the built-in packages do also, e.g. for materialized view logs.

So generally 26 characters or less for object names is a good idea.

Oracle Apex prepends form items with a page reference e.g. P100_column_name.
Since it's easy to envisage an application having over a 100 pages, a limit of 25 characters should be imposed.

Importantly, you need to be aware that for PK columns, Apex imposes a limit of 22 characters for tabular forms.
See http://iadvise.blogspot.com/2006/12/apex-caveat-pk-column-name-length-it.html for details.

So in summing up the limits:
  • table names 26 characters or less
  • views, packages etc. can use 30 but try and stick to 26 characters or less
  • column names generally 25 characters or less
  • primary key column names 22 characters or less.
Naming conventions - well thats up to you!