5

We all know that Oracle has limit of 30 bytes for object names table names columns and bla-bla, I have been searching the net for hours for a solution but I couldn't find anything and eventually I gave up.

We are developing an application that uses both MySQL and Oracle, everything was working fine until we starting implementing Oracle, we encountered problems regarding to the table and stored procedures names.

I can't change the names of the tables because the application is already running on client servers.

Any solution? Maybe some property to tell oracle to make the limit larger than 30.

Shahe
  • 964
  • 2
  • 13
  • 34
  • 2
    Have a meta table yourself and map each object with a name more than 30 CHARS. But remember that you need live with this limitation in Oracle – Srini V Mar 17 '14 at 11:53
  • 1
    **This is not a duplicate.** In this specific case you may be able to workaround the 30 byte limit by using [SQL Translator Profiles](https://blogs.oracle.com/dominicgiles/entry/sql_translator_profiles_in_oracle) in 12c. You would need to intercept SQL that uses large object names and convert them to the short Oracle names. I voted to reopen but questions almost never reopen. To avoid closing, you may need to ask another question and explicitly reference the "duplicate" question and explain why this situation is different. – Jon Heller Mar 17 '14 at 18:19
  • It's simply not possible. –  Mar 17 '14 at 19:49

1 Answers1

4

SQL Translator Profiles in Oracle 12c may help the application pretend that Oracle supports object names of a decent length. This could allow you to change the database without modifying the application.

Below is a trivial example of translating a greater-than-30-byte name into a short name:

SQL> create table short_table_name(a varchar2(100));

Table created.

SQL> insert into short_table_name values ('Success');

1 row created.

SQL> begin
  2     dbms_sql_translator.create_profile('LONG_OBJECT_NAMES');
  3     dbms_sql_translator.register_sql_translation(
  4             profile_name    => 'LONG_OBJECT_NAMES',
  5             sql_text        => 'select * from because_30_bytes_just_isnt_enough_sometimes',
  6             translated_text => 'select * from short_table_name');
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> alter session set sql_translation_profile = LONG_OBJECT_NAMES;

Session altered.

SQL> alter session set events = '10601 trace name context forever, level 32';

Session altered.

SQL> select * from because_30_bytes_just_isnt_enough_sometimes;

A
----------------------------------------------------------------------------------------------------
Success

This may work but I can think of a dozen reasons why it is a bad idea. Only consider this as a last resort.

Jon Heller
  • 34,999
  • 6
  • 74
  • 132