Make Oracle limit larger than 30

Question

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.

Answer 1

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.