TECHNOLOGY: PL/SQL Practices
By Steven Feuerstein
Apply best practices to defining arrays and stripping strings.
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
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 .
Stripping the Unwanted
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:
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:
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)
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:
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:
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; /
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; /
Using Oracle Database 10g
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.
Can I Start a Timer?
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.
Steven Feuerstein (email@example.com) is considered one of the world's leading experts on the Oracle PL/SQL language, having written 10 books on PL/SQL, including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices (O'Reilly Media). Feuerstein serves as a senior technology advisor for Quest Software, and his projects include Qnxo, the world's first active mentoring product (www.qnxo.com).