Lesson 7	Naming conventions
Objective	Create a naming convention in Oracle.

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.

Oracle Naming Conventions
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);
	
```

Oracle Documentation specification for creating naming conventions for PL/SQL Variables

Oracle does not enforce specific naming conventions for PL/SQL variables, but its official documentation and best practices provide guidelines for naming them effectively. Here are some key recommendations based on "Oracle PL/SQL Coding Standards" and Best Practices:

General Naming Rules
- Use meaningful, descriptive names that clearly indicate the purpose of the variable.
- Avoid reserved words and special characters.
- Use underscores (_) to separate words for readability.
- Use a consistent case (either snake_case or camelCase).

Prefixes for PL/SQL Identifiers

Oracle suggests using prefixes to distinguish between different types of PL/SQL identifiers:

Identifier Type	Suggested Prefix	Example
Global Variable	`g_`	`g_total_sales`
Local Variable	`l_`	`l_order_count`
Constant	`c_`	`c_pi_value`
Cursor	`cur_`	`cur_employee_list`
Exception	`e_`	`e_divide_by_zero`
Parameter	`p_`	`p_employee_id`
Record	`r_`	`r_employee`
Type	`t_`	`t_salary_table`
Collection (Associative Array, Nested Table, VARRAY)	`t_` or `tbl_`	`t_department_list`
Boolean Variable	`b_`	`b_is_active`

Scope-Based Naming Conventions
- Local variables: Begin with l_ to indicate their limited scope within a block.
- Global variables: Prefix with g_ to differentiate them from local ones.
- PL/SQL constants: Use an uppercase C_ followed by the variable name.

Example Implementation

DECLARE
    l_employee_id NUMBER;      -- Local variable
    g_company_name VARCHAR2(50); -- Global variable
    c_max_salary CONSTANT NUMBER := 100000; -- Constant
    e_invalid_data EXCEPTION;    -- Exception
    r_employee RECORD;          -- Record
    p_department_id NUMBER := 10; -- Parameter
BEGIN
    -- Sample usage
    l_employee_id := 1001;
    g_company_name := 'Oracle Corp';
    DBMS_OUTPUT.PUT_LINE('Company Name: ' || g_company_name);
END;
/

Additional Best Practices
- Follow database object naming conventions (avoid mixing table and variable names).
- Use PascalCase for record fields (e.g., r_employee.FirstName).
- Avoid overly abbreviated names, unless widely recognized (e.g., emp_id instead of eid).
- Differentiate between similar names to avoid confusion (e.g., p_id for parameters, l_id for local variables).

Oracle PL/SQL Programming

Naming Conventions

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:

Simple.procedure name only:
```
raise_salary(employee_id, amount);
```
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);	
```
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);
```

Qualified and remote:

emp_actions.raise_salary@newyork(employee_id, amount)

It is important to create and follow naming conventions.

Oracle 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:

The following standards will be used in all schemas:

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.
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.
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.
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.

Naming Conventions - Exercise

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