Oracle Best Practices – win with compelxity – naming standards

16 December 2015, Monika Mitura

Standards – annoying warrant or useful tool?

Poorly prepared standards which don’t  provide anything positive  in the software development process can annoy. Programmers instead of focusing on creative programming – are concentrating on following The Standard, which is often written on several pages and can be very meticulous. You can see these poor people counting spaces, the number of which was specifically stated in the applicable convention. Too meticulous standards, even if they improve the clarity and readability of the code, can bring more harm than good. Therefore, when preparing the standards one must firmly restrict only to the things that may improve the quality of the code. Contrary to appearances, the advantages of using naming standards are not limited only to improving clarity and readability of the code. They can give us a lot more benefits.

Using proper prefixes / suffixes we can encode a huge amount of essential information in the variables’ names. Such information available immediately, without searching the code – simplifies understanding and analysis of procedures and what is more, it can prevent many bugs. Offhand knowledge of the main attributes of variable speeds up programming and helps the whole process.

Key attributes of variables that are relevant in the programming process are:

  • purpose / function in the procedure
  • scope
  • direction: is the input, output, both
  • is it a constant
  • data type

The naming convention should allow simple encoding of the information in the variable name.

The purpose of the variable in the procedure

Proper naming of variables is an absolute necessity to make our code readable. It seems very simple – but in practice it turns out to be quite difficult. I’m sure you often came across code crammed with variables like these: x_data, data_tmp, y_data, cur_data. The names of those variables do not report a purpose and hinder the analysis procedure. The code should be clear and understandable. At higher decomposition levels – it should be even readable like a book.

The above code says something to someone? Let’s try to convert it:

Proper variable names is the first and most important element necessary to improve the quality of our system.

Variable Scope

The ability to determine at a glance what scope has the variable is very important, especially in complex processes, which use many different types of variables: local, global, parameters.

Mistakes caused by invalid scoping are very difficult to detect! Once we have debugged such an error for a good few days before we found it. The ability to distinguish the scope of a variable eliminates this type of error.

The scope of variables can be distinguished using prefixes in variable name:

g <name> – global variables i.e. gCurrentDate

p <name> – input parameters i.e. pdCurrentDate

And what about local variables? Local variables are best left without prefixes. We use local variables most often so why write every time the extra letter? If the variable does not have the prefix g or p that means we are dealing with a local variable. Thanks to that we have one char more to use in the variable name to reflect its purpose.

Variable – IN or OUT?

The parameters can be input or output, they can also be constant. Their use and behavior considerably differs from each other. Knowing the type of the variable can facilitate the understanding of the code and the programming.

CONSTANT variables and input variables cannot be changed in the procedure. For output variables you need to prepare a variable to capture the output parameter. Ability to distinguish the direction of variables makes the work easier.

When calling a procedure from the parent procedure it is important to distinguish inputs from outputs. Of course, you can check the variable type in the specification. But wouldn’t it be faster to “decode” the information from the variable name? It will reduce the frustrating jumping from snippet of code we are working on to specification and back.

Because we already use prefixes, the for the purpose of direction specification we may apply suffixes:

p <name> io and – IN / OUT parameter

p <name> o – OUT parameter

<UPPERCASE_VARIABLE_NAME> or c <name> – a constant

What’s missing is the suffix for “IN parameter”. But on the other hand, if the variable starts with “p”  there is no suffix _io or _o so it must be an IN type parameter, the most popular. Once again we saved a char :). As they say, DRY – Do not Repeat Yourself: P

Instead of:

we can write this:

We can see that:

  • new_employee_sal_o is an output variable, calculated and returned by the procedure
  • p_employee_sal variable is an input parameter
  • company_income is a local variable
  • g_SAL_RISE_PERCENT is a global constant variable.

When calling procedures sensible variable suffixes give even greater benefits.

Let’s take a look at the following snippet:

Is there anything we can know based on the code? Not really. To learn more we must either see calc_salary procedure code or / and check the variable types Employee and newSalary.

But as we add prefixes and suffixes:

We can see that both parameters are numeric, pnEmployee parameter is an input parameter, the parameter is pnNewSalary_o → output parameter, so we cannot put there a literal, constant or input parameter. We have to define a numeric variable nNewSalary to capture the value from the procedure.

It should be pointed out that the benefits of this approach can only be observed when we call a procedure using named parameters, not the positional (parameters will be passed to the procedure by  names).

Scope prefixes

SCOPE PREFIX EXAMPLE
GLOBAL g<name> gdCurrentDate
LOCAL without prefix vUserName
CONSTANT <type prefix><NAME> (uppercase) gnUSER_ID
PARAMETR IN p<type prefix><name> pnUserId
PARAMETR OUT p<type prefix><name>_o pvUserName_o
PARAMETR IN OUT p<type prefix><name>_io pvFeeAmt_io

Determining the type of data in the variable name is probably the least common convention. This convention is not difficult to apply. We just need to add the letter as a prefix in the variable name which reflects the variable type ex. v for varchar2, n for number. This information will help us to prevent from unpleasant but easy to avoid bugs. The data type of a variable.

Thanks to that we can distinguish variable names from the names of columns in SQL queries. Why do we need this? Consider the following example:

Oracle will compile the procedure without warning. If we do not notice anything unusual – request that remain in out application. But the consequences can be significant. In the WHERE emp_id column is compared with … emp_id column of the table. Although we have variable with the same name in the code – Oracle will not use it. And instead of the record for the employee – we will get all the records in the table. Probably this error will not be detected on unit tests as testing just one row in the employee table is enough, isn’t it? …

Well, let’s try to improve:

Well, now it should work. But are we sure?

It depends on what the type of variable l_emp_id! If it is the same type as emp_id, then we are lucky. If variable l_emp_id has different type than the emp_id column in the table, and there is an index – Oracle performs implicit data conversion in order to adapt it to variable parameter l_emp_id. As a result, instead of a fast query using index, we have a full table scan.

To detect this type of error  we need experience, luck or time-consuming debugging. When we try to get the explain plan for query using bind variable:

Oracle will calculate query cost assuming  correct type of variable! The cost will be low but time execution still poor.

We can also set the proper value instead of l_emp_id in the query. But since we probably know the type of the column we will use the proper one. Oracle again will tell us that the query is fast and uses the index.

But performance still not good.

What can we do about it?

At first glance we can see that the variable type is varchar2  making it much easier to notice a potential problem. Usually indexed fields are well-known and the picture of the number primary key compared with a variable of different type (v – varchar2) may raise a reasonable doubt. Thanks to information encoded in the variables’ names we can avoid common mistakes in data types which are usually harmless but can have significant performance implications.

Suffixes and prefixes have to be combined depending on the scope, direction and data type of the variable. If we have parameter IN / OUT , number -> we should encode all of information in the name ex. pnEmpSal_io.

Do we always use prefixes and suffixes?

No, there are always exceptions. For variables such as well known iterators we should use usual name like  i, j, z, x, y. Every programmer seeing something like that:

knows exactly what it is.

this introduces a slight uneasiness. Iterators’ names are often used in arrays or collections. Writing long names can be frustrating.

It’s probably better to stick with the good old i, j, x:

That’s better 🙂

CamelCase or under_score

Yes, this topic has been specifically omitted, pushed to the end. Because it does not really matter if you use CamelCase or under_score . If only the variable names are well chosen and the convention allows you to quickly distinguish different types and scope of variables and parameters – this is what we want to achieve through standards. Arguing whether CamelCase or under_score is pointless – just agree to one of those and get it over with 😉

Preparing standards

Well prepared naming standards can greatly simplify the work and prevent code from bugs. You can apply the proposed standards but you can also modify them or prepare your own. It is important to meet the basic purposes  that make developers’ work easier and more enjoyable.

  1. The variable name should define its task / function in the process
  2. The variable name should specify its scope
  3. The variable name should specify its direction
  4. The variable name should specify the data type of the variable

Examples of standards:

SCOPE PREFIX EXAMPLE
GLOBAL g<name> gdCurrentDate
LOCAL without prefix vUserName
CONSTANT <type prefix><NAME> (uppercase) gnUSER_ID
IN PARAMETER p<type prefix><name> pnUserId
OUT PARAMETER p<type prefix><name>_o pvUserName_o
IN/OUT PARAMETER p<type prefix><name>_io pvFeeAmt_io

 

TYPE PREFIX EXAMPLE
VARCHAR v<name> vUserName
NUMBER n<name> nUserId
BOOLEAN b<name> bIfUserActive
ROWTYPE rec<name> or row<name> recUSerData
TABLE t<name> or tab<name> tUsers
CURSOR cur<name> curUserTxn
INTEGER i<name> iTxnCnt
DATE d<name> dCurrentDate

 

This post was published originally at my blog, which you can find here how2ora.blogspot.com.

Tagged with: , , , , , , , , ,

Hire us!

Pretius is a software development company.
We create web applications using: Java, Oracle DB, Oracle Apex, AngularJS.
Contact us to talk about how we can help you with your software project!