What is the common-sense definition of hard coding? Well, 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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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);
CURSORemp_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 begin from header of the procedure, line 1:
|
1
|
CREATE OR REPLACE PROCEDURE p_process_salary(pi_how_much NUMBER, pi_department_id NUMBER)
|
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:
|
1
2
3
|
v_employee_id NUMBER;
v_salary NUMBER;
v_empname VARCHAR2(100);
|
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:
|
1
|
SELECT employee_id, salary, first_name || ‘ ‘ || last_name empname
|
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:
|
1
|
FETCH emp_salary_cur INTO v_employee_id, v_salary, v_empname;
|
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:
|
1
|
COMMIT;
|
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:
|
1
|
raise_application_error(-20017, ‘Something went wrong!’);
|
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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
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 went wrong!’;
c_space CONSTANT VARCHAR2(1char) := chr(32);
c_max_sal CONSTANT EMPLOYEES.SALARY%TYPE := 10000;
CURSORemp_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;
|
Another way you could write this code is presented below – I replaced the loop to a more readable version with
v_emp variable removed.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
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;
CURSORemp_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 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 :):
