Advanced Oracle PL/SQL Programming with Packages

3.1. The Basic Problem

Let's suppose that I am writing an application that does lots of string manipulation. One action that I find myself coding again and again is the duplication of a string. In other words, I need to convert "abc" to "abcabc". I write this:

v_new_string := v_old_string || v_old_string;

and then I write this:

v_comp_name := v_comp_name || v_comp_name;

and I sense a pattern.^[2]

^[2] You are not allowed to wonder why I would need to do something like this. I am not obligated to construct a real-life application that does this. You just have to take my word for it.

Whenever I notice a repetition in my coding, I instinctively put on the brakes and examine more closely. A pattern implies that I can generalize to a formula. I can then encapsulate my formula into a function or procedure. I can then write the formula once and apply it often. This cuts down on my typing and improves my ability to maintain and even enhance my code.

In this case, the pattern is clear: I want to double the supplied string and return it to another PL/SQL variable. I want, in other words, to create a function. So in very quick order I write the twice function as shown in Example 3.1:

Example 3.1. The First Version of the twice Function

CREATE OR REPLACE FUNCTION twice (string_in IN VARCHAR2) RETURN VARCHAR2 IS BEGIN RETURN string_in || string_in; END twice; /

With twice created in the database, I can replace those two explicit concatenations with these calls to twice:

v_new_string := twice ...