Creating Oracle Naming Conventions using PL/SQL Variables

Naming conventions, which are often overlooked because they do not seem to be important, do not become an issue until they are not implemented. A lack of naming conventions causes developers to spend their valuable time trying to debug and maintain the application code.

Below are the common naming conventions:

1. Use a naming convention to avoid ambiguity in the code.
2. Avoid using the same name for the database column and variables in your code.
3. Adopt a naming convention for various objects such as the following example: Using v_ as a prefix representing a variable and g_ as a prefix representing a global variable avoids naming conflicts with database objects.

   vg_petname VARCHAR2(30);
   

The same naming conventions apply to PL/SQL constants, variables, cursors, cursor variables, exceptions, procedures, functions, and packages. Names can be simple, qualified, remote, or both qualified and remote. For example:

1. Simple.procedure name only:

   raise_salary(employee_id, amount);
   

2. Qualified.procedure name preceded by the name of the package that contains it (this is called dot notation because a dot separates the package name from the
   

   procedure name): emp_actions.raise_salary(employee_id, amount);	
   

3. Remote.procedure name followed by the remote access indicator (@) and a link to the database on which the procedure is stored:
   

   raise_salary@newyork(employee_id, amount);
   

4. Qualified and remote:
   

   emp_actions.raise_salary@newyork(employee_id, amount)
   

It is important to create and follow naming conventions.

Many Fortune 500 companies rely heavily on custom-built applications and have a large internal IT department of their own. They generally tend to build their own standards and naming conventions. This helps bring large development teams on common grounds, and it is very easy for any person on the team to understand as well as reuse code. These companies enforce naming conventions and standards through walkthrough.
Many other companies rely entirely on the standards recommended in the documentation that comes with a development tool. This is also a very effective, fast, and cost-efficient way to build standards for your applications. Companies that outsource their IT efforts generally seem to adopt this approach.
Oracle DBA's must pay careful attention to the structure and naming conventionswithin the database. All applications will reside within the same schema owner and naming conventions will be used to identify table/index components:

1. Schema objects: All non-table schema objects will be prefixed by their type and all index names will begin with idx, and all constraint names will begin with cons.
2. Referential Integrity conventions: All tables will have full RI, including PK constraints, FK constraints and check constraints. The default for foreign key constraints will be "On Delete Restrict", unless otherwise specified.  This means that no parent record can be deleted if there are corresponding child records.
3. Primary keys: Oracle Sequences will be used to generate unique row identifiers and all sequence numbers generally will start at one and increment by one.
4. Check Constraints: Lists of valid values will be used in all cases to restrict column values and validity

Standards provide a common language for everyone in the team to understand and maintain the code easily.
The next lesson concludes this module.

Click the Exercise link below to build a naming convention for your application.
Naming Conventions - Exercise