Hard coding in PL / SQL-why you should avoid hard coding.

Hard coding in PL / SQL-why you should avoid hard coding.

Hard-coding in free translation-rigid coding, appears when we-programmers-write code that is not flexible or supplemented with “magic” literals. The result of such actions is a rigid, difficult and expensive to maintain code. It is worth avoiding such mistakes, and just follow the simple rules.

Do not create rigid code

The PL / SQL language is intuitive, easy to learn and allows you to program systems at the level of the Oracle database, and thus-allows you to work with data using SQL. There is a catch in all this, a trap that every beginner and experienced PL/SQL programmer falls into. Going for shortcuts, easy about the code is not flexible enough, not adapted to changes in requirements at the level of system logic. Below is an example code containing only 27 lines and as many as 10 errors.

CREATE OR REPLACE PROCEDURE p_process_salary(pi_how_much NUMBER, pi_department_id NUMBER)
IS
  v_employee_id NUMBER;
  v_salary      NUMBER;
  v_empname     VARCHAR2(100);
  CURSOR emp_salary_cur 
  IS
    SELECT employee_id, salary, first_name || ' ' || last_name empname
      FROM employees
      WHERE department_id = pi_department_id;
BEGIN

  OPEN emp_salary_cur;
  LOOP
    FETCH emp_salary_cur INTO v_employee_id, v_salary, v_empname;
      IF v_salary < 10000 THEN
        pi_increase_salary(v_employee_id, pi_how_much);
      END IF;
    EXIT WHEN emp_salary_cur%NOTFOUND;
  END LOOP;
 
  COMMIT;

EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(-20017, 'Something went wrong!');
END;

Let’s start with the procedure heading, line one:

 	
CREATE OR REPLACE PROCEDURE p_process_salary(pi_how_much NUMBER, pi_department_id NUMBER)

Two input arguments to the procedure, the first hardcode. In the second argument, the programmer assumed that pi_department_id it will always be a number. This may be for a few months or years from the implementation of the project, but it is asking for trouble. If the argument points to the department ID in the employees table, then the developer should consider changing the type of this column to another.

Line 3-5:

v_employee_id NUMBER;
v_salary      NUMBER;
v_empname     VARCHAR2(100);

As in the previous example, fixed variable types are assumed for v_employee_id and v_salary. Such code can cause a problem when someone decides to change the column types in the employees table. In the case of v_empname, it is assumed that the variable will never be larger than 100 bytes. If such a requirement is included in the project specification, then as much as possible the code is correct and we can blame the reported errors on the analyst ;-) However, it is better to be pragmatic and take into account the larger size, if in the future it happens that the name of the employee is longer than 100 bytes. In PL / SQL, you can declare a maximum of 32767 bytes for a variable of type varchar2. In SQL, this size is already 4000 bytes.

Now line 8:

SELECT employee_id, salary, first_name || ' ' || last_name empname

Initially, the code may look correct. Unfortunately, it’s an illusion. A white mark between the employee’s name and first name is included. Space or tab? It would be better to replace this with a predefined constant, for example, or a function chr(32) returning spacing – space from ASCII code – #32.

Line 15:

FETCH emp_salary_cur INTO v_employee_id, v_salary, v_empname;

Such an entry can often be found in the code of a novice PL / SQL programmer. The notation is syntactically correct, but in practice it is difficult to analyze and poorly read. In practice, it will be better to return from the cursor all the columns that were declared in it directly to an object of type RECORD of type %ROWTYPE  on that cursor. On the other hand, you can rebuild the LOOP EXIT WHEN to make it more readable FOR rec IN <kursor> LOOP. More in the revised code at the end of this article.

Line 16:

IF rec.salary < 10000 THEN

This is a typical example of hard-coding. The programmer has written a value of 10000 in the code. No less, no more. If this code had a few thousand lines, finding such a condition could be troublesome. It is better to treat this literal as a constant value in the procedure declaration (or package specification) or as a return value from a function.

Line 22:

COMMIT;

Yes, commit to hard-code. At this point, the developer consciously saves the changes. However, from such a situation there may be no return when a mistake occurs. In addition, such a record makes testing difficult. Testers are losing flexibility in their test programs. Such a statement, although it seems correct, can cause misunderstanding in the design teams.

Last hard-code, line 26:

raise_application_error(-20017, 'Something went wrong!');

The raise_application_error procedure allows you to apply programmer-defined exceptions. If it fails, the procedure will return the error to the application (or the code that called the procedure). The first argument of this procedure (of the two required) is an integer in the range -20000 to -20999. In the second place is a string consisting of a description of the error. The errors that were made in this line are: the error code and its description. The error code that will be returned by the procedure in case of failures should be agreed with the design team and / or should be visible in the procedure declaration (or in the package specification). In the future, there is a chance that another programmer will need to use such a procedure in his code, so he should have knowledge of what such an error number means for him.

Correctly saved code can look like this. In the sample code, we can replace the constants with the result of the built-in function or component of the package we are working on.

CREATE OR REPLACE PROCEDURE p_process_salary(pi_how_much NUMBER, pi_department_id EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
  c_ex_numb CONSTANT NUMBER                := -20017;
  c_ex_text CONSTANT VARCHAR2(100)         := 'Something gone wrong!';
  c_space   CONSTANT VARCHAR2(1char)       := chr(32);
  c_max_sal CONSTANT EMPLOYEES.SALARY%TYPE := 10000;
  
  CURSOR emp_salary_cur 
  IS
    SELECT employee_id, salary, first_name || c_space || last_name empname
      FROM employees
      WHERE department_id = pi_department_id;
      
  v_emp emp_salary_cur%ROWTYPE;
BEGIN
  OPEN emp_salary_cur;
  LOOP
    FETCH emp_salary_cur INTO v_emp;
      IF v_emp.salary < c_max_sal THEN
        pi_increase_salary(v_emp.employee_id, pi_how_much);
      END IF;
    EXIT WHEN emp_salary_cur%NOTFOUND;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(c_ex_numb, c_ex_text);
END;

The code can also be saved differently-we rewrite the loop to a more readable form. One variable disappears – v_emp .

CREATE OR REPLACE PROCEDURE p_process_salary(pi_how_much NUMBER, pi_department_id EMPLOYEES.DEPARTMENT_ID%TYPE)
IS
  c_ex_numb CONSTANT NUMBER                := -20017;
  c_ex_text CONSTANT VARCHAR2(100)         := 'Something gone wrong!';
  c_space   CONSTANT VARCHAR2(1char)       := chr(32);
  c_max_sal CONSTANT EMPLOYEES.SALARY%TYPE := 10000;
  
  CURSOR emp_salary_cur 
  IS
    SELECT employee_id, salary, first_name || c_space || last_name empname
      FROM employees
      WHERE department_id = pi_department_id;
BEGIN
  FOR emp IN emp_salary_cur LOOP
    IF emp.salary < c_max_sal THEN
      pi_increase_salary(emp.employee_id, pi_how_much);
    END IF;
  END LOOP;
EXCEPTION
  WHEN OTHERS THEN
    raise_application_error(c_ex_numb, c_ex_text);
END;

Don’t ever repeat yourself.

Dry (don’t repeat yourself) is a rule used during software development, recommending that you avoid any repetition of code in your applications (a term introduced by the authors of the book The Pragmatic Programmer).  In PL / SQL, as in any other programming language, it is easy and convenient to repeat. Unfortunately, this can lead to errors and even conflicts in the design teams. That’s hard-coding, too. A good programmer always follows the dry rule! If you need to repeat a piece of code, it is better to use a function or procedure. This feature also has to do with the way you work. If something can be automated, it’s better to do it.

Summary

Hardcoding is a programming practice that can corrupt the readability of code or, in the worst case, force the programmer to review and repair the entire application. From my observations, it appears that the PL / SQL language may be more susceptible to hardcoding than other programming languages such as Java. PL / SQL programming is dependent on Oracle-based data structures. Any changes to these structures may have a detrimental effect on the performance of programs written in this language.

The topic of hardcoding has not been exhausted by me completely. There are many other reasons as the negative impact on applications has rigid coding. For more information on this topic, I encourage you to read the series of videos made available by the guru PL/SQL – Steven Feuerstein. I hope that these examples will illustrate the problem and encourage developers to take care of the code in their applications so that it is free from errors that can be easily avoided.

Marius Leather/Pretius

Go to our cases Get a free quote