Coding Guidelines

When I first started coding, I copied the writing style of the teacher. Having done peer reviews and read lots of code over the years I have seen many different styles of code. From how variables are constructed, code is aligned to how loop and if blocks are written.

 In a team or company, it is good practice to have standards and guidelines all members adhere to. For my latest project I was introduced to Trivadis. These are PL/SQL & SQL Coding Guidelines compiled by various experts and hosted on GitHub.

 There are the basics that everyone of us learned at the beginning of our developers journey:

• Names do not start with a numeric character
• Choose meaningful and specific names
• Words are separated with an underscore “_”  (l_employee_name)
• Plural names for tables and collections (employees)
• Singular names for columns (name, adress)

 And among the myriad of other guidelines, a few stood out to me:

3 space indentation instead of what I currently use and that is tab.

The reason tabs are not used is because the indentation can be different for different editors. To make sure the code looks the same, independent of the editor no tabs are used but spaces.

 G-2220: Use PLS_INTEGER instead of NUMBER for arithmetic operations with integer values

My go to for doing calculations is NUMBER but

-       PLS_INTEGER uses less memory

-       PLS_INTEGER uses machine arithmetic, which is up to three times faster than library arithmetic, which is used by number

 G2230: use SIMPLE_INTEGER datatype when appropriate

I have never used this datatype. It was introduced in Oracle 11g. It is a subtype of PLS_INTEGER but the difference is that SIMPLE_INTEGER is always not null. So when you know that the variable you will be using is never going to be null, like for example a counter, then you can declare it as SIMPLE_INTEGER. This data type is also faster than PLS_INGTEGER when code is compiled in native mode, because arithmetic operations on SIMPLE_INTEGER type are performed directly at the hardware level.

 G-2340: define your VARCHAR2 variables using CHAR SEMANTIC

I had issues because of semantics once when working with data in Latvian. That is because in a multibyte environment a varchar2(10) definition may not hold 10 characters when multibyte characters are part of the value that should be stored. It would not hold if the NLS_LENGTH_SEMANTICS where set to BYTE. That is why it is good practice to define your variable using the char semantic like name varchar2(200 char);

 G-5080: use FORMAT_ERROR_BACKTRACE

If you use SQLERRM or FORMAT_ERROR_STACK to log or display errors you will not het the exact line where the error occurred. When you include FORMAT_ERROR_BACKTRACE you will get the point where the exception was raised, even if the subprogram is called from an exception handler in an outer scope.

What are more useful guidelines you currently use?