Oracle Pro Tips, Trends & Technology eNewsletter
Pinnacle Publishing http://www.pinnaclepublishing.com
Issue 2.23
November 14, 2001

TABLE OF CONTENTS

1) Pop Quiz (answer at the end)
2) Did You Know ...
3) Industry News
4) Oracle SQL Tip
5) Answer to the Pop Quiz

---------------------------------------------------------
1) POP QUIZ (answer at the end)
---------------------------------------------------------
What is ARCHIVELOG mode?

---------------------------------------------------------
2) DID YOU KNOW ...
---------------------------------------------------------

... that Oracle9i JDeveloper, the interactive development
environment (IDE) for Java program modules, will be
available without charge via the Internet next month?

Find out more at:
http://dailynews.yahoo.com/h/nm/20011105/tc/tech_oracle_dc_3.html.

---------------------------------------------------------
3) INDUSTRY NEWS
---------------------------------------------------------
According to the Bureau of Labor Statistics, the
unemployment rate in the IT industry in the United States
had reached 5% in October. IT unemployment was 2.6% in
October 2000. The latest figure is the highest since the
last recession in 1991, when the rate was 4.3%.

A number of economists have tried to explain the
increasing unemployment rate in the industry. Further, it
is generally believed knowledge workers are shielded from
the economic cycles and are expected to enjoy full
employment even in economic downturns. The latest figure
reveals that IT unemployment is now closer to the overall
unemployment rate.

You can find the answers to these questions in the
following article:

http://www.informationweek.com/story/IWK20011107S0002.


More depressing news about the tech slump: Silicon Valley
is apparently facing "...its worst slump in its 50-year
history. It may not have bottomed yet." Find out more by
reading the following article:

http://www.usatoday.com/usatonline/20011108/3606227s.htm.

---------------------------------------------------------
4) ORACLE SQL TIP
---------------------------------------------------------
Oracle9i provides the MERGE INTO statement that combines
insert and update operations. The MERGE INTO statement is
efficient because it combines two or more SQL operations
into one. This is useful in operations in which some rows
are inserted into a table while others are updated,
depending on whether or not these rows already exist in
the table.

Without this command, one option of dealing with this
scenario is to write the following PL/SQL code:

BEGIN
   ...
   UPDATE TABLE t1 SET c1 = 'A' WHERE id = 123;
   IF SQL%ROWCOUNT = 0 THEN
      INSERT INTO t1 ( c1, c2, ... ) VALUES ( 1, 2, ... );
   END IF;
   ...
END;

Let's see how we can accomplish this using the MERGE INTO
command. Here's the command syntax:

MERGE INTO table
USING (subquery)
ON (conditions)
WHEN MATCHED THEN
   UPDATE SET column = expression | DEFAULT,...
WHEN NOT MATCHED THEN
   INSERT (column_name,..., column_name)
      VALUES (expression | DEFAULT,..., expression | DEFAULT);

Legend:
- The INTO clause specifies the target table
- The USING clause specifies the data source
- The ON clause specifies the conditions for distinguishing between insert and update operations

Here's how the command works. For each row in the target
table, if the "conditions" are met, then execute the
MATCHED clause to update the table, else execute the NOT
MATCHED clause to insert data.

Oracle has provided an example of the MERGE command.
Check it out at:

http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89856/13_elems30.htm - 37772.

---------------------------------------------------------
5) Answer to the Pop Quiz
---------------------------------------------------------
The ARCHIVELOG mode allows full recovery of the database
to the point of failure. You can verify its status by
running the following query:

SQL> select * from v$database;

The ARCHIVELOG mode is disabled at database creation. To
enable it, enter the following command:

SQL> alter database archivelog;

There're several additional configuration steps that are
required before ARCHIVELOG mode can function properly.
These steps will be discussed in the coming articles as
part of the coverage on backup and recovery.

---------------------------------------------------------
Well, that's it for this week. I welcome your feedback,
input, tips, suggestions, Web sites, and other Oracle-
related news. If you send me something, please let me
know whether I can use your name with your comments. I
apologize in advance if I don't respond personally to
each of your questions or suggestions, but I'll get to as
many as I can in the eNewsletter if not personally.

Garry Chan, Editor
Database Architect
mailto:GChan@ProcaseConsulting.com

This eNewsletter is brought to you compliments of
Pinnacle Publishing, Inc.
Copyright(c) 2001
http://www.pinnaclepublishing.com
All rights reserved.