Oracle Pro Tips, Trends & Technology eNewsletter 
Pinnacle Publishing http://www.pinnaclepublishing.com
Issue 2.17
August 22, 2001

TABLE OF CONTENTS

1) Pop Quiz
2) Did You Know ...
3) SQL Tip: Mutating Table
4) News
5) Answer to the Pop Quiz

---------------------------------------------------------
1) POP QUIZ (answer at the end)
---------------------------------------------------------
What is the difference between Oracle9i and Oracle9iAS?

---------------------------------------------------------
2) DID YOU KNOW ...
---------------------------------------------------------
...that INSTEAD OF triggers can be used to replace the 
default data operation of Oracle? We'll discuss this in 
the next issue.

---------------------------------------------------------
3) SQL TIP: Mutating Table
---------------------------------------------------------
In the last issue, we discussed what a mutating table 
error was. Typically, this error occurs when a row-level 
trigger tries to query data from a table that is in the 
mutating state, resulting in the error below:

   ORA-4091: Table XXX is mutating; trigger may not read 
or modify it

In this issue, we'll discuss how we can work around it.

At a high-level, this is what we need to do:

1. Create a database package that contains the 
   declaration of a PL/SQL table, say, t_arr.
2. Create a statement-level pre-* trigger that   
   initializes the array and the array pointer:

     v_arr_ptr := 0;

3. Replace the content of the row-level trigger with the 
   following:

     v_arr_ptr := v_arr_ptr + 1;
     v_arr(v_arr_ptr).id := :new.id;
     v_arr(v_arr_ptr).value := :new.value;

In essence, we are "pushing" the value to be updated onto 
the array. If five rows are modified by the UPDATE 
statement, then the row-level trigger will be invoked 
five times, and five entries will be created in the 
array.

4. Create a statement-level after-* trigger that 
   navigates the array entries and applies the update:

     for i in 1..v_arr_ptr loop
        update emp set value = v_arr(i).value
        where id = v_arr(i).id;
     end loop;

Do you have other ways of achieving the same result?

---------------------------------------------------------
4) INDUSTRY NEWS
---------------------------------------------------------
Check out the following link for a list of companies that 
are using Oracle9i and/or Oracle9iAS. In fact, one of the 
Web sites that our company built, 
http://www.workopolis.com/, is featured in the article.

http://www.oracle.com/customers/index.html?9i.html

---------------------------------------------------------
5) ANSWER TO THE POP QUIZ
---------------------------------------------------------
Oracle9i is the database server that was released 
recently, whereas Oracle9iAS stands for Oracle9i 
Application Server, an integrated application server for 
Web-enabling Oracle database applications.

It offers the following main features:

- Apache Web server is bundled with 9iAS for serving Web 
  pages. This is quite an important development in that 
  Oracle has adopted a robust, open solution for its Web 
  server
- Support for PL/SQL execution. This option allows 
  developers familiar with PL/SQL, Oracle proprietary 
  database programming language, to continue to use it 
  for Web application development
- Integration with Oracle Portals. Oracle Portals is a 
  product for rapid development of Web applications. The 
  pages generated by Portals are created using PL/SQL
- Support for Java servlet execution, JSP, XML
- Support for OC4J. This is kind of Oracle's 
  implementation of EJB, Java components for application 
  development.

---------------------------------------------------------
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.