Loooooong Names: 12 Things Developers Will Love About Oracle Database 12c Release 2 Part 2

Oracle Database handles cache invalidation for you. So as a developer you don’t have to worry about this.

But when it comes to naming things, we’ve made it harder than it ought to be.

Why?

Take the following example:

alter table customer_addresses add constraint 
  customer_addresses_customer_id_fk foreign key ( customer_id ) 
  references customers ( customer_id );

Looks like a standard foreign key creation, right?

But there’s a problem. Run it and you’ll get:

SQL Error: ORA-00972: identifier is too long

Aaarrghh! The constraint name is just a tiny bit too long :(.

Staying within the 30 byte limit can be tricky. Particularly if you have naming standards you have to follow. As a result, many people have asked for us to allow longer names.

Starting in 12.2 we’ve increased this limit. The maximum is now 128 bytes. So now you can create objects like:

create table with_a_really_really_really_really_really_long_name (
  and_lots_and_lots_and_lots_and_lots_and_lots_of int,
  really_really_really_really_really_long_columns int 
);

Remember: the limit is 128 bytes. Not characters. So if you’re using a multi-byte character set, you’ll find you can’t create:

create table tablééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééééé
( is_67_chars_but_130_bytes int );

This is because é uses two bytes in character sets such as UTF8. So even though the string above is only 67 characters, it needs 130 bytes!

Full article originally published at blogs.oracle.com on November 10, 2016.