dynamically constructed WHERE clause.
In this case, the answer is still to use
NVL()—so the SET portion of the UPDATE
will be constant—and then use the technique outlined in the original column for
the WHERE clause. That is, the query construction would look like this:
begin
L_query :=
'update t set x= 1,
c1 = nvl(:c1,c1),
c2 = nvl(:c2,c2), …
where 1= 1'
Database 10g Release 1 to Oracle Database
11 g Release 2. By the time this column
goes to print, the second edition of the
book should be released. In addition to an
entirely new chapter on database encryp-
tion, many new twists are documented in
the second edition. As I was revising it for
the current database releases, I found re-
affirmation that “some things change over
time” (I’ve been known to say that occa-
sionally). In some cases, the changes were
obvious, such as the new Oracle Database
11 g Release 2 Deferred Segment Creation
feature. That new feature prevented many
of the examples in the first edition of the
book from working as they used to, because
I sometimes created a table or some
segment and immediately queried the data
dictionary to show the segment attributes,
but in Oracle Database 11 g Release 2, the
segment wasn’t there! I had to modify
many of my examples to address this
change. For example
SQL> create table t1 ( x int );
Table created.
if (p1 is not null)
then
l_query := l_query ||
' and p1 = :p1 ';
else
l_query := l_query ||
' and ( 1= 1 or :p1 is null);
end if;
… other parameters …
execute immediate l_query using
c1, c2, …, p1, p2, …;
As in the static SQL example, I would set
all the column values every time—either to
the new values in the bind variables or to
themselves if the bind variables were NULL.
As an aside, and as I mentioned earlier,
I get e-mails from time to time regarding
the content of my column—as do other
Oracle Magazine authors. Anytime you see
something confusing, would like a clarification, or have a question on the content of
the magazine, feel free to drop the editors
an e-mail at opubedit_us@oracle.com.
Include the URL to the online version of the
column or the article title and issue date
of the magazine you are writing about, and
the editors will forward the question to the
person who wrote the article. Who knows,
you might appear in the front of the magazine one issue in the letters to the editor
(From Our Readers).
SOME NEW THINGS I LEARNED . . .
I’ve recently been reworking my last
book, Expert Oracle Database Architecture
(Apress, 2005), to upgrade it from Oracle
Code Listing 1: Estimated cardinality from the explain plan
SQL> create or replace type str2tblType
as table of varchar2( 30);
2 /
Type created.
SQL> create or replace
2 function str2tbl( p_str in varchar2, p_delim in varchar2 default ',' )
3 return str2tblType
4 PIPELINED
5 as
6 l_str long default p_str || p_delim;
7 l_n number;
8 begin
9 loop
10 l_n := instr( l_str, p_delim );
11 exit when (nvl(l_n,0) = 0);
12 pipe row ( ltrim(rtrim(substr(l_str, 1,l_n- 1))) );
13 l_str := substr( l_str, l_n+ 1 );
14 end loop;
15 return;
16 end;
17 /
Function created.
SQL> column plan_table_output format a80 truncate
SQL> variable in_list varchar2(255)
SQL> exec :in_list := 'DBMS_PIPE,DBMS_OUTPUT,UTL_FILE';
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly explain
SQL> select
Execution Plan
—————————————————————————————————————————————————————————————————————————————————————————————
Plan hash value: 2407808827
————————————————————————————————————————————————————————————————————————————————————————————————————————————————
|Id |Operation |Name |Rows |Bytes |Cost (%CPU)
————————————————————————————————————————————————————————————————————————————————————————————————————————————————
| 0 |SELECT STATEMENT | | 8168 |16336 | 29 (0)
| 1 | COLLECTION ITERATOR PICKLER FETCH|STR2TBL | 8168 |16336 | 29 (0)
