{"id":8878,"date":"2016-09-20T06:14:11","date_gmt":"2016-09-20T04:14:11","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/"},"modified":"2016-09-20T06:14:11","modified_gmt":"2016-09-20T04:14:11","slug":"oracle-12cr2-long-identifiers","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/","title":{"rendered":"Oracle 12cR2 Long Identifiers"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nThis morning during Gerald Venzl presentation of “What\u2019s New for Developers in the Next Generation of Oracle Database” at Oracle Open World, one feature has been acclaimed by a full room: 12.2 show the end of identifiers limited to 30 characters.
\n<\/p>\n

12.1<\/h3>\n

We knew it would happen because in 12.1 all data dictionary views have 128 bytes length character strings:<\/p>\n

\nConnected to:\nOracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production\n \nSQL> desc dba_objects\n Name                                      Null?    Type\n ----------------------------------------- -------- ----------------------------\n OWNER                                              VARCHAR2(128)\n OBJECT_NAME                                        VARCHAR2(128)\n SUBOBJECT_NAME                                     VARCHAR2(128)\n<\/code><\/pre>\n
but that’s only the dictionary metadata. Impossible to reach that limit:<\/p>\n
\nSQL> create table \"ThisIsAVeryLongNameThatIsAllowedInTwelveTwoC\" as select * from dual;\ncreate table \"ThisIsAVeryLongNameThatIsAllowedInTwelveTwoC\" as select * from dual\n             *\nERROR at line 1:\nORA-00972: identifier is too long\n<\/code><\/pre>\n
It is only annoying as the default column format do not fit in the screen:<\/p>\n
\nSQL> select owner,object_name from dba_objects where object_type='TABLE';\n \nOWNER\n------------------------------------------------------------------------------------------------------------------------\nOBJECT_NAME\n------------------------------------------------------------------------------------------------------------------------\n<\/code><\/pre>\n
12.2<\/h3>\n
In 12.2 you can create longer identifiers:<\/p>\n
\nSQL> create table \"ThisIsAVeryLongNameThatIsAllowedInTwelveTwoC\" as select * from dual;\nTable created.\nSQL> alter table \"ThisIsAVeryLongNameThatIsAllowedInTwelveTwoC\" add XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX number;\nTable altered.\n<\/code><\/pre>\n
But be careful, the limit is in bytes – not in characters. If we have multibytes characters, the limit can be reached earlier:<\/p>\n
\nSQL> alter table \"ThisIsAVeryLongNameThatIsAllowedInTwelveTwoC\" add X\u20acXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX number;\nalter table \"ThisIsAVeryLongNameThatIsAllowedInTwelveTwoC\" add X\u20acXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX number\n                                                                *\nERROR at line 1:\nORA-00972: identifier is too long\n<\/code><\/pre>\n
So what?<\/h3>\n
The goal is not to abuse and put the longest names possible. But allowing more that 30 characters can be easier to match table names with Java class names for example.<\/p>\n
Oracle 7<\/h3>\n
I wanted to show that those 30 characters limits was very old, so I ran a 7.3.3 oracle version that lies on my laptop and displayed the same describe of DBA_OBJECTS:
\n\"CaptureLongIdentifier\"<\/a>
\nIdentifiers were 30 characters there. But look at object name: those 128 bytes are there for more than 20 years!<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . This morning during Gerald Venzl presentation of “What\u2019s New for Developers in the Next Generation of Oracle Database” at Oracle Open World, one feature has been acclaimed by a full room: 12.2 show the end of identifiers limited to 30 characters.<\/p>\n","protected":false},"author":27,"featured_media":8880,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[656,209],"type_dbi":[],"class_list":["post-8878","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database-administration-monitoring","tag-12-2","tag-oracle-12c"],"acf":[],"yoast_head":"\nOracle 12cR2 Long Identifiers - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Oracle 12cR2 Long Identifiers\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . This morning during Gerald Venzl presentation of “What\u2019s New for Developers in the Next Generation of Oracle Database” at Oracle Open World, one feature has been acclaimed by a full room: 12.2 show the end of identifiers limited to 30 characters.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2016-09-20T04:14:11+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureLongIdentifier-1.png\" \/>\n\t<meta property=\"og:image:width\" content=\"609\" \/>\n\t<meta property=\"og:image:height\" content=\"303\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"headline\":\"Oracle 12cR2 Long Identifiers\",\"datePublished\":\"2016-09-20T04:14:11+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/\"},\"wordCount\":199,\"commentCount\":0,\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/CaptureLongIdentifier-1.png\",\"keywords\":[\"12.2\",\"Oracle 12c\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/\",\"name\":\"Oracle 12cR2 Long Identifiers - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/CaptureLongIdentifier-1.png\",\"datePublished\":\"2016-09-20T04:14:11+00:00\",\"author\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\"},\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/#primaryimage\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/CaptureLongIdentifier-1.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/sites\\\/2\\\/2022\\\/04\\\/CaptureLongIdentifier-1.png\",\"width\":609,\"height\":303},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/oracle-12cr2-long-identifiers\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Oracle 12cR2 Long Identifiers\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/66ab87129f2d357f09971bc7936a77ee\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g\",\"caption\":\"Oracle Team\"},\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Oracle 12cR2 Long Identifiers - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/","og_locale":"en_US","og_type":"article","og_title":"Oracle 12cR2 Long Identifiers","og_description":"By Franck Pachot . This morning during Gerald Venzl presentation of “What\u2019s New for Developers in the Next Generation of Oracle Database” at Oracle Open World, one feature has been acclaimed by a full room: 12.2 show the end of identifiers limited to 30 characters.","og_url":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/","og_site_name":"dbi Blog","article_published_time":"2016-09-20T04:14:11+00:00","og_image":[{"width":609,"height":303,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureLongIdentifier-1.png","type":"image\/png"}],"author":"Oracle Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"headline":"Oracle 12cR2 Long Identifiers","datePublished":"2016-09-20T04:14:11+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/"},"wordCount":199,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureLongIdentifier-1.png","keywords":["12.2","Oracle 12c"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/","url":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/","name":"Oracle 12cR2 Long Identifiers - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureLongIdentifier-1.png","datePublished":"2016-09-20T04:14:11+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureLongIdentifier-1.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureLongIdentifier-1.png","width":609,"height":303},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/oracle-12cr2-long-identifiers\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Oracle 12cR2 Long Identifiers"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/66ab87129f2d357f09971bc7936a77ee","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/f711f7cd2c9b09bf2627133755b569fb5be0694810cfd33033bdd095fedba86d?s=96&d=mm&r=g","caption":"Oracle Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8878","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=8878"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/8878\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/8880"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=8878"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=8878"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=8878"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=8878"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}