Oracle Magazine - September/October 2010

Database Application Developer and DBA
ASK TOM BY TOM K Y TE

On Popularity, Learning,
and Unlearning
Our technologist revisits a classic, recounts Cardinality
Feedback, and remembers SQL*Plus.

ORACLE DATABASE 11g

From time to time, I get e-mails from Oracle Magazine readers in which they ask follow-up questions or point out a mistake (or two) in my columns. Recently I received a follow-up question (not a mistake report!) regarding the most popular question and answer on Ask Tom (and in Oracle Magazine at oracle.com/ technology/oramag/oracle/09-jul/ o49asktom.html). To answer that follow-up question, I’ll begin by sharing the original Q&A. This will be the third time I’ve written about this now—it is the idea that will not go away.

First, to refresh you on the topic of that most popular Q&A, the original problem statement was

I want to declare multiple cursors based on the values passed through a procedure, and only the WHERE conditions of the cursors will change. The body of the procedure is the same for all the cursors otherwise.

Basically, the answer was to use native dynamic SQL and dynamically construct the WHERE clause based on the inputs. My suggestion was to incorporate code that resembled this:

if (some_parameter is not NULL)
then
the_query := the_query ||
' and some_column = :some_parameter ';
else
the_query := the_query ||

' and ( 1= 1 or :some_parameter is null) '; end if;

and, later in the code, open a ref cursor:

open the_cursor
for the_query
using some_parameter

<and any other parameters…>;

That way you could have a subroutine with many input parameters—as many as you wanted—and build a query that could always be opened with a constant number of bind inputs (a requirement of native dynamic SQL). I encourage you to read the earlier column to review this technique and my reasons for originally suggesting this approach.

The follow-up Oracle Magazine reader question I received was, in short, “How do I use this approach with an UPDATE statement?” In full, the question was

Great article. I would LOVE to know
how to apply this to dynamic update
statements. What can I use in place of
“??????” in the following:

create or replace
procedure my_new_procedure
(p_hiredate in date default NULL)
as
l_query varchar2(512);
BEGIN
l_query := 'UPDATE DETAIL SET
ACOLUMN = 1';

If ( p_date is NOT NULL )
then
l_query := l_query ||
' ,BCOLUMN = :p_hiredate ';
else
l_query := l_query ||

' '; end if;

… ccolumn and others here …
EXECUTE IMMEDIATE V_SQL
USING p_hiredate;

So, the follow-up question was a variation on a theme. Instead of selectively adding criteria to a predicate, which is what my original column was about, this reader would like to selectively modify columns if some input is passed along.

There are two cases to consider here: one is that there is either no WHERE clause to be added or the WHERE clause is static (constant). The UPDATE statement would either update all rows in the table (no WHERE clause) or a set of rows based on a constant criterion. In this case, the answer is easy: do not use dynamic SQL at all. Just code.

BEGIN
UPDATE detail
SET acolumn = 1,
bcolumn = nvl(p_hiredate, bcolumn),
<ccolumn and others here> …
<WHERE clause if provided>
END;

And that’s it. If P_HIREDATE is null, then the UPDATE will effectively set BCOLUMN to itself. If P_HIREDATE is not null, then P_HIREDATE will be used to update BCOLUMN. It is true that this would generate a bit more redo and undo, because every column would be modified every time, but because the indexes would not be maintained for any indexed column whose value did not actually change, this would be minimal. For the first case, then, the solution is to use static SQL and NVL().