Answering PL/SQL

Apply best practices to defining arrays and stripping strings.

January/February 2006

PL/SQL expert Steven Feuerstein answers your PL/SQL questions at Best Practice PL/SQL (oracle.com/technetwork/articles/plsql). This column includes highlights from that forum.

How can I define a two-dimensional array of numbers in PL/SQL?

Although PL/SQL does not natively support the declaration and manipulation of multidimensional arrays, you can emulate these structures using nested collection definitions, which were first supported in Oracle9i Database Release 1.

Here is a brief example to get you started and introduce you to some of the challenges you may encounter as you use collections in this way.

First, create a collection of associative arrays.

CREATE OR REPLACE PACKAGE twodim_aa
IS
   TYPE data_t IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;
   TYPE array_t IS TABLE OF data_t
      INDEX BY PLS_INTEGER;
END twodim_aa;
/

The first, inner collection— data_t —contains the data for each cell in the two-dimensional array. Each row in the outer collection— array_t —contains a collection of the first type.

Now declare a variable based on that outer collection type — array_t —, which will serve as a two-dimensional array. In the following script, I declare such a collection—

DECLARE
   l_2d_grid twodim_aa.array_t;

—and then assign values to three cells: (1,1), (1,2), and (200,206). Notice that the syntax is different from that used in traditional array cell specification, namely: (1)(1), (1)(2), and (200)(206). Also, since I am using associative arrays to define my two-dimensional array, I do not have to specify a size for this two-dimensional array.

DECLARE
   l_2d_grid twodim_aa.array_t;
BEGIN
   l_2d_grid (1) (1) := 100;
   l_2d_grid (1) (2) := 120;
   l_2d_grid (200) (206) := 200;
   IF l_2d_grid (1)(2) < 100
   THEN
      ...
   END IF;
END;
/

If you want to define a two-dimensional array with a specific size, you should use VARRAY s, because when you declare a VARRAY you must also specify a maximum number of elements allowed in the VARRAY . Here, for example, is the specification of a 100x100 element array:

CREATE OR REPLACE PACKAGE
twodim_varray
IS
   TYPE data_t IS
   VARRAY (100) OF NUMBER;
   TYPE array_t IS
   VARRAY (100) OF data_t;
END twodim_varray;
/

Populating this array becomes very complicated, however, because before I can assign a value to an index in the array, that array must be initialized and then extended (the same will be true of nested tables). To do this, I must now expose the stark reality that the twodim_aa.array_t is not really a two-dimensional array—rather, it is a collection of collections. I must first extend one dimension. That dimension, a VARRAY , defines an element that will in turn hold a VARRAY (the inner collection— array_t ). Before I can assign a value to that index, however, I must initialize the inner collection and extend it . Finally, I can assign a value.

DECLARE
   l_2d_grid twodim_varray.array_t
      := twodim_varray.array_t();
BEGIN
   l_2d_grid.EXTEND;
   l_2d_grid(1)
      := twodim_varray.data_t();
   l_2d_grid(1).EXTEND;
   l_2d_grid (1) (1) := 100;
END;
/

Because of these complications, you will almost certainly want to either

• Stick with associative arrays. It will greatly simplify your life when it comes to manipulating the values in multidimensional arrays.

• Build an encapsulation to hide the code required to manipulate a VARRAY -based multidimensional array.

Regardless of which collection type you choose to emulate the multidimensional array, you will need to write your own programs to perform high-level operations on your array.

Finally, if you need to perform advanced operations on vectors and matrices, you might also consider using the UTL_NLA package, new to Oracle Database 10g Release 2. This package exposes within PL/SQL a subset of the BLAS (Basic Linear Algebra Subprograms) and LAPACK (Linear Algebra PACKage) Version 3.0 operations on vectors and matrices. These structures are represented as VARRAY s in UTL_NLA .

I'm using Oracle9i Database. How can I strip out unwanted characters from a string variable? For example, I would like to be able to call a function named stripped_string like this:

DECLARE
   l_before   VARCHAR2 (100) :=
      'This is my string';
   l_after    VARCHAR2 (100);
BEGIN
   l_after := stripped_string
                (l_before, 'is');
   DBMS_OUTPUT.put_line (l_after);
END;
/
and set l_after to 'Th  my trng'.

While Oracle9i Database does not provide a built-in function specifically to remove characters from a string, you can easily adapt TRANSLATE to this purpose.

The TRANSLATE function allows you to replace characters in an expression that appear in a from string to their corresponding characters in the to string.

Suppose that I want to replace all instances of the letter e in my name with the letter X. I will then execute this code:

BEGIN
    DBMS_OUTPUT.PUT_LINE (
       TRANSLATE (
          'Steven Feuerstein'
          , 'e', 'X')
    );
END;
/

And see this result:

StXvXn FXuXrstXin

Note that TRANSLATE is case-sensitive. If I run this variation on the above block

BEGIN
    DBMS_OUTPUT.PUT_LINE (
 TRANSLATE (
          'Steven Feuerstein'
          , 'E', 'X')
    );
END;
/

I see these results:

Steven Feuerstein

From these demonstrations, you might quickly conclude that to remove all the e's from my name, I can simply call TRANSLATE as follows:

TRANSLATE ('Steven Feuerstein', 'e', NULL)

or

TRANSLATE ('Steven Feuerstein', 'e', '')

Alas, this will not work as desired. Both expressions will return NULL , since the empty string is treated as a NULL , and generally if you provide a NULL argument to an Oracle function, it will return a NULL (there are some exceptions to this rule, like the NVL function).

To use TRANSLATE as a character-stripper, then, I need to finesse the function a bit. For example, I can strip out e's from my name with this block of code:

BEGIN
    DBMS_OUTPUT.PUT_LINE (
       TRANSLATE (
          'Steven Feuerstein'
          , '#e', '#')
    );
END;
/

Here is the resulting output:

Stvn Furstin

Notice what I have done: I included in both the from string and the to string the same first character. But in the to string, that leading character is the only character. TRANSLATE goes one by one through each character in the from string and replaces it with the character in the to string at the same position. Consequently, # is replaced by # and e is replaced by NULL . So all instances of e disappear: TRANSLATE has done the job for me.

Before you rush off to apply TRANSLATE in this fashion, make sure you know how to best implement it as a character stripper. Consider the following block

BEGIN
    DBMS_OUTPUT.PUT_LINE (
       TRANSLATE (
          'Steven Feuerstein'
         , 'ee', 'e')
    );
END;
/

and its output:

Steven Feuerstein

TRANSLATE did not, in this case, remove the e's from my name, because the leading character for replacement was also an e , and that translation of e to e took precedence over the second translation of e to NULL . So you need to make sure that the leading character in both strings is not the same as any of the characters you wish to strip from the string.

Now, you could simply try to remember all this, but you would be much better off encapsulating all this information into a function so it can do the remembering and applying of these TRANSLATE -related rules on your behalf.

Consider the stripped_string function in Listing 1. This simple program offers the following advantages:

Code Listing 1: The STRIPPED_STRING function

CREATE OR REPLACE FUNCTION stripped_string (
   expression_in    IN   VARCHAR2
  ,characters_in    IN   VARCHAR2
  ,placeholder_in   IN   VARCHAR2 DEFAULT CHR(1)
)
   RETURN VARCHAR2
IS
BEGIN
   RETURN TRANSLATE ( expression_in
                    , placeholder_in || characters_in
                    , placeholder_in
                    );
END stripped_string;
/

• It hides the use of TRANSLATE to perform the character stripping. You no longer need to be aware of how to use TRANSLATE in this fashion.

• It contains the knowledge about the need to provide a leading character in both from and to strings. By default, it uses the nonprinting CHR(1) ("Start of header" character) as the placeholder for TRANSLATE -based stripping. It is very unlikely that you will ever have a string with CHR(1) in it, so you can usually call the function without specifying a placeholder character. If, however, you ever need to override that placeholder, you can do so by calling the function and specifying all three arguments.

Listing 2 includes examples of calls to stripped_string .

Code Listing 2: Using STRIPPED_STRING with different inputs

BEGIN
   DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', 'e'));
   DBMS_OUTPUT.put_line (stripped_string (NULL, 'e'));
   DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', 'e', NULL));
   DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', NULL));
   DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', 'e', 'e'));
   DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', 'e', 't'));
   DBMS_OUTPUT.put_line (stripped_string ('Steven Feuerstein', 'etn'));
END;
/

This process of information hiding is certainly useful for this exercise with TRANSLATE . It is absolutely critical, however, with the more-complex programs you will write for your application. Take the time to encapsulate implementation of business rules and formulas in their own functions, such as stripped_string . The resulting code will be much easier to debug and maintain.

I wrote the simple test script in Listing 3 to exercise stripped_string .

Code Listing 3: Testing STRIPPED_STRING

DECLARE
   l_success   BOOLEAN DEFAULT TRUE;
   PROCEDURE report_failure (description_in IN VARCHAR2)
   IS
   BEGIN
      l_success := FALSE;
      DBMS_OUTPUT.put_line ('stripped_string failure!');
      DBMS_OUTPUT.put_line ('   ' || description_in);
   END report_failure;
BEGIN
   IF stripped_string ('abc', 'b') != 'ac'
   THEN
      report_failure ('Default placeholder');
   END IF;
   IF stripped_string ('abc', 'abc') IS NOT NULL
   THEN
      report_failure ('All characters removed');
   END IF;
   IF stripped_string (NULL, 'abc') IS NOT NULL
   THEN
      report_failure ('NULL input string');
   END IF;
   IF stripped_string ('abcdefb', 'b') != 'acdef'
   THEN
      report_failure ('Repeats of character');
   END IF;
   IF stripped_string ('abc', 'a', '#') != 'bc'
   THEN
      report_failure ('# as placeholder');
   END IF;
   IF stripped_string ('abc', NULL) != 'abc'
   THEN
      report_failure ('NULL character list');
   END IF;
   IF l_success
   THEN
      DBMS_OUTPUT.put_line ('stripped_string passed its tests!');
   END IF;
END;
/

If you were using Oracle Database 10g, there would be another way to do this:

REGEXP_REPLACE.
BEGIN
   -- Oracle Database 10g only!
   DBMS_OUTPUT.put_line (
      REGEXP_REPLACE (
         'STEVEN FEUERSTEIN'
       , 'E*', NULL));
END;
/
Stvn Furstin

Regular expression functions rely on a set of metacharacters to determine their behavior. The asterisk (*) means "match zero or more occurrences," which is the behavior I desired to strip out all the e's.

What if the character you needed to strip was (*)? Use an escape character in the call to REGEXP_REPLACE , as shown here:

REGEXP_REPLACE ('ABC*', '\**');
/
ABC

Listing 4 is a revision of the stripped_string function, called stripped_string_10g , which uses REGEXP_REPLACE instead of TRANSLATE to do the string stripping.

Code Listing 4: The STRIPPED_STRING function using REGEXP_REPLACE

CREATE OR REPLACE FUNCTION stripped_string_10g (
   string_in               IN   VARCHAR2
  ,strip_characters_in     IN   VARCHAR2
)
   RETURN VARCHAR2
IS
-- With REGEXP_REPLACE, each character to be replaced with NULL,
-- must be followed by a "*".
   c_asterisk              CONSTANT CHAR (1) := '*';
   l_strip_characters      VARCHAR2 (32767);
   l_length                PLS_INTEGER;
   l_character             VARCHAR2 (2);
BEGIN
   l_length := LENGTH (strip_characters_in);
   IF l_length > 0
   THEN
      FOR l_index IN 1 .. l_length
      LOOP
         l_character := SUBSTR (strip_characters_in, l_index, 1);
         IF l_character = c_asterisk
         THEN
            l_character := '\' || c_asterisk;
         END IF;
         l_strip_characters :=
               l_strip_characters
            || l_character
            || c_asterisk;
      END LOOP;
   END IF;
   RETURN regexp_replace (string_in, l_strip_characters);
END stripped_string;
/

Does the complexity of using REGEXP_REPLACE justify all this code (as well as the need to maintain it and the overhead of running it)? I would say no, that in this case you are better off learning the nuances of REGEXP_REPLACE and applying it directly to your string to strip characters.

I have a requirement to have a timer start after an insert into a table. After a short time, I would initiate another activity. How can I program that requirement in PL/SQL?

It's not hard to start a timer in your session. Check out the Timer or Stopwatch Object Type Definition in Downloads (at oracle.com/technetwork/articles/plsql/files/tmr10g.zip), which allows you to create an object type-based "stopwatch" so you can start and stop a timer from within PL/SQL.

Here is an example that uses this stopwatch timer:

DECLARE
   c_iterations CONSTANT    PLS_INTEGER := 100;
   stopwatch_tmr tmr_t :=
      NEW tmr_t ('Run My Test',
                  c_iterations);
BEGIN
   stopwatch_tmr.go;
   FOR indx IN 1 .. c_iterations
   LOOP
      run_my_test;
   END LOOP;
   stopwatch_tmr.stop;
END;
/

The tricky part of the question has to do with being able to detect that a certain amount of time has passed, which then triggers a new activity. PL/SQL executes in a single thread. If you want to "kick off" new activities (and then continue on with your current activity), you essentially need to send a message to another session, which then gets moving on your request.

You can use either Oracle's Advanced Queuing facility or the simpler DBMS_PIPE package to implement messaging between sessions.

You might also want to consider DBMS_JOB or Oracle Database 10g's new DBMS_SCHEDULER . If you simply want to start a new activity in, say, 30 seconds, you could submit a job with a start time of 30 seconds in the future and no repeat execution of the job. The job manager would then wake up, check for the job, and kick it off, independently of the session from which the job was scheduled.