Oracle 12.2 New Feature: Longer object/column names

    Feb 7, 2017 10:51:16 AM by Dan Hotka

    Hi,

    Maybe you have wanted to use longer object names to be more descriptive. The Oracle RDBMS now supports up to 128 positions for both object and column names! The current naming length convention is 30. The other naming rules still apply such as:

    1. Starts with a character
    2. No spaces
    3. Underbars are acceptable
    4. Special characters are too…just be careful out there!

    Please don’t get carried away. I know using Toad’s schema browser, I can filter objects by short acronyms perhaps.

    We don’t need index names like ‘Index_on_EMP_table_drop_after_1_use_as_this_was_just_a_test’…and the index never gets renamed or dropped. I can see this happening.

    So Oracle12.2 on, you can give your tables/indexes/materialized views/columns/PLSQL packages/procedures/functions much longer and more meaningful names.

    Oracle Employee Chris Saxon shared this code with us at OOW16…to display indexes associated with tables and crop the names shorter to fit on your display.

    Select TABLE_NAME,
               Listagg(INDEX_NAME, ‘,’ on overflow truncate)
                within group
               (order by INDEX_NAME) INDS
    From USER_INDEXES
    Group by TABLE_NAME;

    Oracle employee Connor McDonald expanded on the topic with this code that would prevent longer object names if you do not have DBA privileges. Perhaps you can use this code to modify/monitor/enforce your company naming conventions.

    create or replace trigger ddl_trigger
    before create or alter on demo.SCHEMA
    declare
       l_obj   varchar2(128);
       l_dba   int;
    begin
       l_obj := ora_dict_obj_name;
     
       select count(*)
       into l_dba
       from dba_role_privs
       where grantee = USER
       and granted_role = 'DBA';
     
       if l_dba = 0 and length(l_obj) > 30 then
          raise_application_error(-20000,'Identifier "'||l_obj||'" is too long');
       end if;
    end;

     

    Tags: Oracle

    Dan Hotka

    Written by Dan Hotka

    Dan Hotka is an Author/Instructor/Expert/Oracle ACE Director Alumni who uses TOAD regularly in his web-based and on-line courses. He is well published with many books still available on Amazon or www.DanHotka.com. Dan is available for user groups and company educational events. He regularly speaks at user groups around the world. Check his website for course listings, references, and discount codes for his online courses based in Safari. Dan's most recent book is Toad for Oracle Unleashed