How to avoid hard-coding in your PL/SQL code?

31 August 2015, Mariusz Skóra

Hard-coding – common sense definition

Hard-coding appears in our software when we (programmers) write inflexible code with special “magic” values that are meaningful to original coder when written, but when times goes by no one knows what they stand for.  In effect we got code that is hard to read and expensive to maintain. Hard-coding is the easiest way to feed the “technical debt monster”. If you would like your PL/SQL code to be free from this flaw just apply some simple rules which I will try to outline below using sample code.

Never hard-code anything

PL/SQL is really easy to learn and allows you to develop database systems in Oracle RDBMS. As with many programming languages there is a hard-coding trap into which beginners and experienced software developers fall alike. From my observations PL/SQL is maybe more susceptible to the hard-coding, as it seems to occur more often than in e.g. Java code. Let’s analyse some sample source code to see what issues we can come across in a seemingly simple PL/SQL procedure.

Let’s begin from header of the procedure, line 1:

You can find two arguments in the procedure. The second one  pi_department_id is a numeric value. Unfortunately, it is the first step towards future troubles. The field references to department ID in EMPLOYEES table. If DBA or other developer changes the type of department_id column, your code will blow up. The better way is change the type of column to  EMPLOYEES.DEPARTMENT_ID%TYPE.

Next take a look at lines 3-5:

Same as before, you can find hard-coded types for variables v_employee_id and v_salary. This code will cause some troubles if anybody changes column types in EMPLOYEES table. In case of v_empname the author wrote just 100 bytes for the variable. No more, no less. It is risky and bad idea (it’s also double risky with charsets that store variable number of bytes for characters outside of the ASCII charset). I cannot estimate how many bytes the variable will need in future, so it is better to use more than just 100 bytes in that case. In PL/SQL you can use 32767 bytes for Varchar2 variables, in SQL it’s 4000 bytes.

Now the line #8 – another hard-code:

At first glance this code seems correct, but unfortunately it is not. There is a white-space character between first name and last name of an employee. Is it a space or a tab? It is better way to use a constant variable or function chr(32), which returns space from ASCII code.

Another line, another place where simple change can improve readability – line 15:

It is correctly written but it is hard to read and for analysis. It is better to fetch cursor to record variable with  %ROWTYPE of that cursor. You can also rebuild the loop construct to a more readable form – FOR rec IN <cursor> LOOP. You can find examples of other versions of this code in later part of this post.

Line 22:

Yes, commit is a hard-code as well. In this example developer saved all database changes. From this point we cannot go back if any problem occurs. Moreover, commit in our code it is bad idea because of testing reason. We loose flexibility. It is hard to test anything.

And the last one, line 26:

The procedure – raise_application_error – allows to use user-defined exceptions. If any problem occurs, the procedure returns error code and message to the application. First argument is an integer value in range of -20000 to -20999. The second argument is an error message. What are the mistakes in this code above? Yes, you’re right – the error code and error message are hard-coded. The error codes should be available and easily understanable for all members of the team, which with hard-coded integer values is not possible (or very hard, at the least). Moreover the values should be visible in the function or in the package specification, so at first glance one can know what types of errors one can expect.

Correctly written code might look like the one below. In the example, we can use constants or function values.

Another way you could write this code is presented below – I replaced the loop to a more readable version with  v_emp variable removed.

Don’t Repeat Yourself

The DRY principle should be used during software development process (term was introduced by authors of The Pragmatic Programmer book – a must read for all programmers / software developers. It is advised to avoid all “copy-pastes” in your code, as they produce lots of bugs in code, leading to conflicts in the project team. A good programmer is always DRY ;)! If you need to repeat any fragment of your code, it is better to use procedure or function. Your code will be more readable and easier to maintain. DRY it is a part of good quality, in the way of working as well.

Summary

Hardcoding will mess up code readability and might force (in the worst case) a complete rewrite / major refactoring of the application. Software written in PL/SQL language depends heavily on Oracle’s data logic structures so any changes in these structures may have a huge impact on the PL/SQL application. It is very easy to write code sensitive to the hard-coding using e.g. coded types of variables such as  VARCHAR2 and NUMBER. For more information on how to limit the impact of hard-coding on your apps I encourage you to look for Steven Feuerstein’s movies on Youtube. I hope that all discussed examples were clear and they will allow you to write better code, without typical bugs, that can be easily avoided. If the above doesn’t convince you to avoid hardcoding try to remember just this quote :):

psycho

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!