{"id":77373,"date":"2018-02-20T11:21:55","date_gmt":"2018-02-20T11:21:55","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=77373"},"modified":"2021-07-14T13:06:54","modified_gmt":"2021-07-14T13:06:54","slug":"validating-naming-conventions-oracle","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/validating-naming-conventions-oracle\/","title":{"rendered":"Validating naming conventions in Oracle"},"content":{"rendered":"<div class=\"note\">\n<p class=\"note\"><em>This article was originally published on Nick&#8217;s site, <a href=\"https:\/\/apexplained.wordpress.com\/\">APEXPLAINED<\/a>. We are republishing it here with his permission.<\/em><\/p>\n<\/div>\n<p>Most development teams have agreed upon some sort of naming convention as it typically increases the readability and comprehension of the source code. Defining these rules is one thing, but correctly and consistently applying them is another. Wouldn&#8217;t it be great if we were able to actually validate whether our team-specific naming rules have been applied the right way? Well, I&#8217;ve created a package that makes it possible to list all incorrectly named elements in your database schema.<\/p>\n<h4>How does it work?<\/h4>\n<p>I make a distinction between two types of naming violations:<\/p>\n<ul>\n<li><strong>Object name violations<\/strong>: Teams often pre- or suffix database object names to indicate the project or domain the object belongs to. Another common practice in Oracle development is to assign database objects a pre- or suffix from which you can immediately determine the object type. For example: a table for employees is named <em>TBL_EMP<\/em>, a sequence to fill up the primary key column is named <em>SEQ_EMP<\/em>, etc.<\/li>\n<li><strong>Code identifier name violations<\/strong>: PL\/SQL code is made up of different types of identifiers: variables, constants, types, parameters, etc. Applying naming rules to these identifiers results in more readable and understandable code. Local variables are for example often prefixed with <em>l_<\/em>, while global variables start with the <em>g_<\/em> prefix. For this part I use <a href=\"http:\/\/docs.oracle.com\/cd\/B28359_01\/appdev.111\/b28424\/adfns_plscope.htm#g1010526\" target=\"_blank\" rel=\"noopener\">PL\/Scope<\/a> to verify the correctness of the identifier names in your PL\/SQL code.<\/li>\n<\/ul>\n<div class=\"note\">\n<p class=\"note\">PL\/Scope is a compiler-driven tool that collects data about identifiers in PL\/SQL source code at program-unit compilation time and makes it available in static data dictionary views. The collected data includes information about identifier types, usages (declaration, definition, reference, call, assignment) and the location of each usage in the source code.<\/p>\n<\/div>\n<p>It&#8217;s important to keep two things in mind here. First, PL\/Scope has been introduced in Oracle 11g Release 1, which means that any lower database version is not supported. Secondly, the PL\/Scope data dictionary views are populated only when the <em>PLSCOPE_SETTINGS<\/em> parameter is set to <em>IDENTIFIERS:ALL<\/em> for your current session.<\/p>\n<pre class=\"theme:classic lang:plsql\">alter session set plscope_settings='identifiers:all';<\/pre>\n<p>The default parameter value is <em>IDENTIFIERS:NONE<\/em>, which means that identifier gathering is disabled by default. Not setting the appropriate parameter value leads to inaccurate results. So make sure you set the <em>PLSCOPE_SETTINGS<\/em> parameter to <em>IDENTIFIERS:ALL<\/em> before compiling your database objects.<\/p>\n<h4>Installation and configuration<\/h4>\n<p>We first need to take care of the installation. <a href=\"https:\/\/github.com\/nbuytaert1\/orcl-naming-convention\/releases\" target=\"_blank\" rel=\"noopener\">Download<\/a> the installation script (<em>install.sql<\/em>) and execute it on the database schema you wish to validate naming conventions. The installation script compiles no more than three objects:<\/p>\n<ul>\n<li>A package <em>API_NAMING_CONVENTION<\/em><\/li>\n<li>An object type <em>OT_NAMING_VIOLATION<\/em><\/li>\n<li>A table type <em>TT_NAMING_VIOLATIONS<\/em><\/li>\n<\/ul>\n<p>After a successful execution of the installation script, open the <em>API_NAMING_CONVENTION<\/em> package body. The first thing you&#8217;ll notice is the configuration section, which is nothing more than a collection of global constants. Every constant will be used inside the package to verify whether valid names have been given to a specific database object type or code identifier type. For example, the first constant you&#8217;ll encounter is <em>GCO_TABLE_NAME_PATTERN<\/em> and can be assigned a regular expression pattern that includes all rules to which a table name in your schema must comply. For those not into regular expressions, don&#8217;t worry, we&#8217;re not going to write complicated or lengthy regex patterns. I&#8217;ll give some examples later on, so there&#8217;s no reason to panic ;]<\/p>\n<h4>Example usage<\/h4>\n<p>Let&#8217;s see what the <em>API_NAMING_CONVENTION<\/em> package has to offer. In this example, I want to validate whether my table names start with the prefix <em>TBL_<\/em> and I want my sequence names to end with <em>_SEQ<\/em>. We&#8217;ll first need to configure the <em>API_NAMING_CONVENTION<\/em> package body to take into account these naming rules. Assign the following regular expression patterns to the appropriate constants:<\/p>\n<pre class=\"theme:classic lang:plsql\">gco_table_name_pattern constant gt_string := '(^TBL_).+';\r\n...\r\ngco_sequence_name_pattern constant gt_string := '.+(_SEQ$)';<\/pre>\n<p>You&#8217;ll probably use these two regex patterns a lot as most naming rules include nothing more than a prefix or suffix.<\/p>\n<p>I currently have no tables or sequences in my schema, so let&#8217;s fix that.<\/p>\n<pre class=\"theme:classic lang:plsql\">create table tbl_valid(\r\n column1 varchar2(255)\r\n);\r\n \r\ncreate sequence seq_invalid;<\/pre>\n<p>We&#8217;re now going to write a query that gives us back all improperly named objects according to our naming convention.<\/p>\n<pre class=\"theme:classic lang:plsql\">select *\r\nfrom table(api_naming_convention.all_object_name_violations);<\/pre>\n<p>That query returns one row.<\/p>\n<p style=\"text-align: justify;\"><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/02\/p29_invalid_objects.jpg\" \/><\/p>\n<p>The name of the sequence is invalid because I expect the <em>SEQ<\/em> part at the end of the name. The table I created is not included since its name conforms to our naming convention. Okay, I have to admit that this wasn&#8217;t mind blowing so far. But wait. There&#8217;s more to come. Let me demonstrate the true powers of the <em>API_NAMING_CONVENTION<\/em> package.<\/p>\n<h4>Validating code identifiers<\/h4>\n<p>Now the fun part starts. We&#8217;re going to look for violated naming rules in our PL\/SQL code. Before we decide on the rules we want to enforce, let me quickly explain the usage of the <em>GCO_LOCAL_IDENTIFIER<\/em> and <em>GCO_GLOBAL_IDENTIFIER<\/em> constants in the <em>API_NAMING_CONVENTION<\/em> package body. These two constants accept a string value, which allows you to define the character(s) you use to differentiate between locally and globally scoped elements. I use the characters <em>L<\/em> and <em>G<\/em> for respectively local and global identifiers.<\/p>\n<pre class=\"theme:classic lang:plsql\">gco_local_identifier constant gt_string := 'L';\r\ngco_global_identifier constant gt_string := 'G';<\/pre>\n<p>We are now able to reference these values in our regular expression patterns by using the placeholder <em>:scope:<\/em>. For this example, I use the following naming rules.<\/p>\n<pre class=\"theme:classic lang:plsql\">gco_variable_pattern constant gt_string := '(^:SCOPE:_).+';\r\ngco_constant_pattern constant gt_string := '(^:SCOPE:CO_).+';\r\ngco_exception_pattern constant gt_string := '(^:SCOPE:EX_).+';\r\ngco_cursor_pattern constant gt_string := '(^:SCOPE:CU_).+';<\/pre>\n<p>This means that a regular variable should either start with <em>l_<\/em> or <em>g_<\/em>, depending on its scope. The rule for constants is very alike: local constants should start with <em>lco_<\/em>, while global constants should start with <em>gco_<\/em>. That makes sense, right?<\/p>\n<p>Okay, configuration is all set. I&#8217;ll now create a dummy package so that the schema we&#8217;re working in contains some PL\/SQL code.<\/p>\n<pre class=\"theme:classic lang:plsql\">create or replace package body my_package is\r\n \r\n  -- global\r\n  g_valid number;\r\n  gco_valid constant varchar2(255) := 'xyz';\r\n  invalid boolean;\r\n  lco_invalid constant date := sysdate;\r\n \r\n  procedure do_nothing is\r\n    -- local\r\n    v_invalid varchar2(255);\r\n    lco_valid constant number := 1;\r\n    invalid_exception exception;\r\n    cursor lcu_valid is\r\n      select *\r\n      from dual;\r\n    begin\r\n      null;\r\n    end do_nothing;\r\n \r\nend my_package;<\/pre>\n<p>The package compiled successfully and it&#8217;s obvious that it contains at least several naming violations. It&#8217;s time for our <em>API_NAMING_CONVENTION<\/em> package to identify the infringements. Here&#8217;s the query I use to specifically check for identifier name violations.<\/p>\n<pre class=\"theme:classic lang:plsql\">select *\r\nfrom table(api_naming_convention.all_coding_violations);<\/pre>\n<p>The result is a bit disappointing though. The query returns zero rows while it&#8217;s clear that my PL\/SQL code contains multiple improperly named identifiers. Is something wrong with the <em>API_NAMING_CONVENTION<\/em> package or did we overlook something? Remember the PL\/Scope parameter? I am required to set the <em>PLSCOPE_SETTINGS<\/em> parameter to <em>IDENTIFIERS:ALL<\/em> before compiling any PL\/SQL program-unit. Let&#8217;s fix that and rerun the query.<\/p>\n<pre class=\"theme:classic lang:plsql\">alter session set plscope_settings='identifiers:all';\r\nalter package my_package compile;\r\n \r\nselect *\r\nfrom table(api_naming_convention.all_coding_violations);<\/pre>\n<p>This time the query successfully identified the violated elements in my PL\/SQL code.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/02\/p29_invalid_identifiers.jpg\" \/><\/p>\n<p>The result set includes detailed metadata to help you understand and locate the naming violation.<\/p>\n<ul>\n<li><strong>Violation type<\/strong>: indicates whether it&#8217;s an <em>OBJECT NAME<\/em> or <em>CODING<\/em> violation.<\/li>\n<li><strong>Element type<\/strong>: the type of database object or identifier that caused the violation.<\/li>\n<li><strong>Element name<\/strong>: the object or identifier that&#8217;s been given an improper name.<\/li>\n<li><strong>Violated pattern<\/strong>: the violated regular expression pattern.<\/li>\n<li><strong>Object type<\/strong>: the database object type in which the violation occurred.<\/li>\n<li><strong>Object name<\/strong>: the name of the database object in which the violation occurred.<\/li>\n<li><strong>Line and column<\/strong>: the exact location of the improperly named identifier in the PL\/SQL program-unit.<\/li>\n<li><strong>Scope<\/strong>: is the violated identifier locally or globally scoped?<\/li>\n<\/ul>\n<h4>The ALL_VIOLATIONS function<\/h4>\n<p>You can query all naming violations regardless of their type by running the below query.<\/p>\n<p>The result is a combination of both <em>ALL_OBJECT_NAME_VIOLATIONS<\/em> and <em>ALL_CODING_VIOLATIONS<\/em>.<\/p>\n<p><img decoding=\"async\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2018\/02\/p29_all_violations.jpg\" \/><\/p>\n<p>It is even possible to only query for certain object or identifier type naming violations. Take a look at the <em>API_NAMING_CONVENTION<\/em> package specification for all available functions. The <em>SEQUENCE_NAME_VIOLATIONS<\/em> function for example only returns improperly named sequences.<\/p>\n<h4>Final words<\/h4>\n<p>Phew, this article is getting pretty long, so I&#8217;m going to stop right here. Congratulations for making it this far.<\/p>\n<p>This is an open source project by the way. The source code is being maintained on <a href=\"https:\/\/github.com\/nbuytaert1\/orcl-naming-convention\" target=\"_blank\" rel=\"noopener\">GitHub<\/a>. Leave me a comment in case you have any questions or remarks.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Most development teams have agreed upon some sort of naming convention as it typically increases the readability and comprehension of the source code. Defining these rules is one thing, but correctly and consistently applying them is another. Wouldn\u2019t it be great if we were able to actually validate whether our team-specific naming rules have been applied the right way?&hellip;<\/p>\n","protected":false},"author":318301,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[4459,48474,124952],"coauthors":[54935],"class_list":["post-77373","post","type-post","status-publish","format-standard","hentry","category-oracle-databases","tag-oracle","tag-plsql","tag-redgate-deploy"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77373","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/318301"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=77373"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77373\/revisions"}],"predecessor-version":[{"id":77384,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/77373\/revisions\/77384"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=77373"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=77373"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=77373"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=77373"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}