I offer in this Quseful (Quick and Useful) a package that you can use to dynamically retrieve the value of almost any column from any table. I created this package as a "helper" utility for Quest® Code Tester users. Here's the problem that I was solving with this package:

We added support for automated testing of XML documents in Quest Code Tester 1.6, which will be released in a month or so (a very solid beta is available at http://unittest.inside.quest.com/beta.jspa). So if you have a function that returns an XML document or a procedure that has an OUT XML document, you can very easily specify a test of that XML document through the Expected Results Properties window:

And this is all great, except we noticed one problem: if your XML document is stored in a column in a table, then you cannot easily point to that column and say "Please test the contents of that column in this row."

We plan to make possible in the future direct testing of a column in a table through the user interface.
In the meantime, though, I decided to build a backend API to allow developers to easily test their column value. It wasn't too hard, because we built lots of customizability (a word?) into Quest Code Tester from the very start.

So for this particular situation, you can ask to test an expression and then choose XMLType as your type of expression:

But then you need to write a chunk of PL/SQL code to return the column's value. So I wrote the dynamic query function in the qu_helper package to make it easy to do just that.

Here is a call to the xml_column_value function to retrive an XMLType column value:

Now, that seems generally useful, so I moved that code from qu_helper into the dyn_column_value package and I offer it to you!

Show me the code!

Rather than show you all the code here (poor use of blog real estate), 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 Quseful4.zip:

• dyn_column_value.pkg – the package itself
   
• Q##DYN_COLUMN_VALUE.qut – a Quest Code Tester test definition export that exercises some of the programs in the package to verify its correctness

How do I use it?

Each of the retrieval functions has the same four arguments:

Argument Name	Significance
owner_in	The name of the owner of the table
table_in	The name of the table
column_in	The name of the column
where_in	The where clause that should identify a single row
raise_ndf_in	If you pass TRUE, then this function will raised NO_DATA_FOUND if no row is found for the where clause specified. Otherwise, NULL is returned.

  
If other errors are raised, they are propagated out with RAISE_APPLICATION_ERROR.

Here is an example:

BEGIN
   my_salary := dyn_column_pkg (USER
                              , 'EMPLOYEES', 'SALARY',
                              , 'LAST_NAME = ''FEUERSTEIN''');
END;