Sometimes you need to be able keep track of strings (names of some sort, usually) that you have used, so you do not use them again. I ran into this need, in fact, when I was building some backend code for Quest Code Tester for Oracle. We generate test code (a PL/SQL package) for the tests you described through the UI. That generated code includes declarations of variables. I can't declare a variable with the same name more than once. So I need to remember what I previously declared. To do that I built the qu_used package, which evolved into the string_tracker package.
The package requires Oracle Database 9i Release 2 and above, since it takes advantage of string-indexed collections. It is, I believe, an excellent demonstration of the elegance possible in one's code through the use of this structure.
I hope you can get as much value out of this package as I have.
Show me the code!
Sorry, rather than show you all the code here (very clumsy), I offer the source code and any supporting files in this zip file.
You can also download my entire "demo zip", containing all the scripts and reusable code that are part of my regular trainings. The zip for this Quseful is inside that zip as well.
Here are the files in the Quseful2.zip:
string_tracker3.pks - the lastest and greatest specification of the string_tracker package
string_tracker3.pkb - the lastest and greatest body of the string_tracker package
string_tracker.sql - a demonstration of using this code (also found below)
q##STRING_TRACKER.qut - a Quest Code Tester export of the test definition I built to veirfy that string_tracker works. You can import this into an installation of Code Tester and confirm for yourself that string_tracker works as advertised.
How do I use it?
The package contains several programs:
string_tracker.clear_all_lists Deletes all lists you may have defined in string_tracker in your session.
string_tracker.clear_list Deletes just the list specified in the call to clear_list.
string_tracker.create_list Creates a new list. Provide the name of the list, whether or not you want the strings in the list to be case-sensitive, and if you want to overwrite a list that already exists with this name.
string_tracker.mark_as_used Mark the specified string as "used" in the specified list.
string_tracker.string_in_use If the specified string is currently "used" in the specified list, return TRUE. Otherwise, return FALSE.
Here is an example of using string_tracker that mimics my own actual application of this package inside Code Tester.
I have a collection of outcomes (the tests I will perform after I test is run). For each outcome, I need to declare a local variable to hold the data. Since I can create more than one outcome for a particular OUT argument, I must be sure to avoid duplicate declarations.
/* Create a constant with the list name to avoid multiple,
hard-coded references. Notice the use of the subtype
declared in the string_tracker package to declare the
list name. */
c_list_name CONSTANT string_tracker.list_name_t := 'outcomes';
/* QCGU: A collection based on a %ROWTYPE associative array type */
/* Create the list, wiping out anything that was there before. */
string_tracker.create_list (list_name_in => c_list_name
, case_sensitive_in => FALSE
, overwite_in => TRUE
/* QCGU: get all the outcome rows for the specified test case. */
l_outcomes := qu_outcome_qp.ar_fk_outcome_case (l_my_test_case);
/* For each outcome... */
FOR indx IN 1 .. l_outcomes.COUNT
/* IF the string has not already been used... */
IF NOT string_tracker.string_in_use (c_list_name
, l_outcomes (indx).variable_name
/* Add the declaration to the test package. */
generate_declaration (l_outcomes (indx));
/* Make sure I don't generate duplicate declarations. */
, l_outcomes (indx).variable_name
/* Clean up! */
Keep the following in mind:
- With string_tracker, you can keep track of multiple (approximately 4.3 billion) of lists of used strings. Each list may contain approximately 4.3 billion strings in it.
- These lists only persist for the duration of your session (they are stored in package variables), and they consume PGA memory.
- The package requires Oracle Database 9i Release 2 and above, since it takes advantage of string-indexed collections.
I'd love to hear what you think of this utility and, in particular, if you found it useful. So please don't hesitate to post a comment on this blog or send me a note at email@example.com.
A Quseful is a Quick and Useful (as opposed to Quick and Dirty) tip on now to write programs in the Oracle PL/SQL language more effectively. I will publish Qusefuls on my blog (feuerthoughts.blogspot.com) on a regular basis. Each Quseful contains a description of the tip ("What's the point?"), some PL/SQL code you can install in your environment to implement the tip ("Show me the code!"), an example of how to use the code to help you get your job done ("How do I use it?").