woensdag, juni 29, 2011

'long' object names

Today it happened again. A developer made a database independent program and gave me the ddl to install it in a - in this case - Oracle database. Given the fact that the application is developed as database independent, this should give no problems.
During the installation I got many ORA-00972 identifier too long error messages. This surprized me a little. Checking the code, it appeared that table names had horrible long names and as we all know, Oracle has always had a limit on the identifier lengths.
So before throwing it all back I tried a workaround, trying to use synonyms with the long name pointing to the tables for which I adjusted the name to keep within the current limits.

lets try:
create table z as select * from dual;
create synonym verylongnamethatnoonewantstohaveoruse for z;
This runs without any issues.
select * from VERYLONGNAMETHATNOONEWANTSTOHAVEORUSE;
gives ORA-00972 identifier too long :-(

That is a pity, my trick is not going to work.
What synonym is created?
select synonym_name from user_synonyms where table_name = 'Z';
SYNONYM_NAME
------------------------------
/5bba812b_VERYLONGNAMETHATNOON

A little different than expected ...
The leading '/' makes it unusable, unless quoted:

select * from "/5bba812b_VERYLONGNAMETHATNOON";
DUMMY
-----
X

Check the documentation: Oracle® Database SQL Language Reference - CREATE SYNONYM

Leaving me no other option than to return the code to the developer. Question that remains is: is this 30 characters limit still valid? If appearantly many others allow longer names, why does Oracle still stick to the 30 characters limit. Now we have to persuade developers to adjust for Oracle, where the same code runs without problems om other databases. For this project the choice was easy: deploy on sqlserver. This is not what we want, is it?

2 reacties:

Noons zei

"why does Oracle still stick to the 30 characters limit"?

Because SQL is a standardized language and the ANSI standard says identifiers are allowed to be a maximum of 30 characters long.

Of course, one would expect a developer worth that name to be aware of the industry standards.

But apparently clouds is the only thing they know about nowadays...

Ronald Rood zei

Hi Noons,
thanks for your reaction.

I am not so good at standards and finding them so correct me if I am wrong.
Oracle says:
Oracle Support for Optional Features of SQL/Foundation:2008

F391, Long identifiers
Oracle supports identifiers up to 30 characters in length.

As far as I could find, the - optional features - of SQL/Foundation:2008 specify a max identifier length of 128 characters.

In the way this specific app is developed, any length limit will eventually become a problem, that is true.

I don't expect this to be adjusted, because it is an optional specification. Many developers work in a windows centric environment, where sqlserver is the norm.... This way of working easily makes their code incompatible with Oracle. It is not me who decides what is good or wrong, I just see this happening and in many shops it is the way how apps are moved from Oracle to others. With current database independent applications, it does not matter on which database it runs....
For developers that are trained in an Oracle environment, this won't be a problem. Many others won't even know this limitation, and their trainers won't tell....