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.
Examples
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.
DECLARE
/* 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 */
l_outcomes qu_outcome_tp.qu_outcome_tc;
BEGIN
/* 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
LOOP
/* IF the string has not already been used... */
IF NOT string_tracker.string_in_use (c_list_name
, l_outcomes (indx).variable_name
)
THEN
/* Add the declaration to the test package. */
generate_declaration (l_outcomes (indx));
/* Make sure I don't generate duplicate declarations. */
string_tracker.mark_as_used (c_list_name
, l_outcomes (indx).variable_name
);
END IF;
END LOOP;
/* Clean up! */
string_tracker.clear_list (c_list_name);
END;
Gotchas
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 steven@stevenfeuerstein.com.
About Qusefuls
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?").
1 comment:
Hallo Steven,
Reading your post about String Tracking I was wondering if it can be easilly adapted to become a Function Memoization mechanism.
For the brave lurkers of your Blog Site Memoization is a topic adressed by Wikipedia with pseduo code examples too.
The optimization techniques are generally categorized as RDBMS tuning methodologies by Oracle Practitioner and if you have something slowing down or not enoughly keeping up to the pace, you should look into your tkprof'd trace file leaving your code untouched. Rethink or tweaking your SQL statement is the major activity during a DB dependent software refactoring process. Surely you are on the good way doing this way, it is the correct approach if you think that the relational logic provides the abstraction of application business rules logic.
Despite the premises, modern RDMBS are far away from a pure and simple data store. They contain a full array of functionalities stacked into a programmable layer. So, caching the result of expensive evaluations is a good idea sometime.
Memoization in general requires a hash tables, a functionality reproducible with VARCHAR2 indexed PL/SQL tables since Oracle 9i came out, if you are working with string value parameters it'll work pretty good.
"Memoizing by Hand" is a thing I've done myself recently after having discovered that the use of Oralce external tables as lookup tables wasn't so good as expected. These entities are fully loaded into memory on every access for what I can remember. Implementing a control structure and preventing the reread of the the flat file mapped table saved me the day. All the records are cached on the first "full scan" and "transparently" returned as many time as you need them now. (ie. They are fetched from a PL/SQL table type)
To make a step forward we can start to consider how an "Automatic Memoizing" can be coded in PL/SQL. Developing a memoizer applicable to any function the view USER_ARGUMENTS should be a good starting point.
Actually, I would stick with the naive solution that assume you are coding internally a solution where values are evaluated, retained and then returned from the cache on request. It's advisable to add a feature to reset the memoized function store to its initial state in those cases you're dealing with mutable data.
I am very curious to read your thoughts about this argument Steven. Giving the possibility to a PL/SQL developer to add a memoizer to her/his code leaving the underlying source untouched (interfacing to a function wrapper), is interesting and productive.
In case we are hitting a snag, the reason of the infeasibility is due to the PL/SQL's lack of dynamicity? Comparing our beloved language to python and ruby for example..
I hope you have the time to read my dense comment. Take this as a consequence of your thought-provoking programming books and blog posts.
Best regards!
Giorgio.
Post a Comment