Object Name size question

[KrisSimonis]

Heya,

Does anyone know if there is a way to get past
the 30 character limit for objects in Oracle? Or
if this limit will be lengthened in the future?
I'm having a serious problem generating unique
names for foreign keys based on table names and
field names in my database( mostly due to the size
of names in the original database which was in
Foxpro which does allow for very long names )

Kris Simonis
Topdesk Information Systems

 

[BilliaUK]

Java objects can have up to 128 characters but I cannot imagine Oracle extending all other object names beyond 30. Imagine the backwards-compatability issues...potentially horrendous. I can't see it myself...

Regards

Adrian

 

[KrisSimonis]

I don't really see how backward compatibility would generate a problem with extending those object name sizes.
( unless ofcourse you want the new database version to be still useable by an older version ofcourse ) Kris Simonis
Topdesk Information Systems,
Application Server Development

"You haven't seen the last of Meeaaaarrrrghh!!!"
- Several bad guys in several bad movies

 

[BilliaUK]

Kris

Re: not seeing a problem...

Say I have a table MY_TABLES that stores some information from USER_TABLES (I have seen a site that stores its own dictionary data - crazy). OK, so MY_TABLES.TABLE_NAME is VARCHAR2(30) of course.

Now, I have a dodgy piece of PL/SQL that populates MY_TABLES. In that PL/SQL, I have a variable v_table_name declared as USER_TABLES.TABLE_NAME%TYPE. Which is VARCHAR2(30) of course.

Which all works well until the size of an object_name is increased beyond 30 due to public demand. I now get an inserted value too large for column error...

That's what I mean about backwards compatability.

Regards

Adrian

 

[KrisSimonis]

Well, like you said yourself, the keyword there is dodgy.
Also, changing such code that it complies with the new standard wouldn't be that much work. (though I do admit that if it's somewhere DEEP it might take a while if the structure of your code is rather 'obfuscated').
Though 30 chars is a LOT of possible combinations, if you want your names to be readable, it can get a little short.
(Which is exactly the problem I'm running into)


Kris Simonis
Topdesk Information Systems,
Application Server Development

"You haven't seen the last of Meeaaaarrrrghh!!!"
- Several bad guys in several bad movies

 

[BilliaUK]

Kris

It is "accepted" good practice to anchor your variables to their underlying data sources in PL/SQL. I have been on the receiving end of supporting someone's particularly obfuscated code whereby a sequence had increased to allow up to nine didgits where the variable buried away in the code to store it was coded as a number(8). So when the sequence grew... So in this case anchored declarations are a good idea.

It is very difficult to justify re-writes on any existing code that works, unless for serious performance gains. In an in-house team, you might just get away with it (I wouldn't in the bank I work for). But you also have to consider that for a lot of systems, a database upgrade is a major task at the best of times, without having to consider all of your existing code. But what about the numerous tools that are out there that have their PL/SQL embedded or stored on the database. Software vendors cannot re-ship an upgrade to every single user they have because Oracle pulled the rug from under their feet.

I understand the frustration you are having, but 30 is much better than the 8 for filenames imposed on us by early windows, more recently NT, and I think current AS400s.

Why don't you raise an enhancement request at oracle.com?

Regards

Adrian

 

[sem]

In such situations Oracle recommends to use a short 3-letter alias instead of table_name. It should be unique across an application. So your foreign_key name may be as
<alias name>_<parent table alias name>_FK. These aliases are also used in SQL statements and in foreign key column names:

If your PARENT table with alias PAR contains PK column named ID, the corresponding column in CHILD table with alias CHL should be PAR_ID. Besides this the primary key of PARENT is PAR_PK and foreign key for CHILD is CHL_PAR_ID.

 

[KrisSimonis]

I did something like that, I build a numeric translator that turned the name into a digital code which should produce unique numbers based on that name.(technicly I believe it's still possible to generate non-unique names but the chance is very slim)
The algorith I used is based on position and ascii code.
( ie. ABC would translate into 65x1 + 66x2 + 67 * 3 = 398 )
Kris Simonis
Topdesk Information Systems,
Application Server Development

&quot;You haven't seen the last of Meeaaaarrrrghh!!!&quot;
- Several bad guys in several bad movies