TABLE OF CONTENTS

1) POP QUIZ (answer at the end)
2) Oracle Professional
3) PL/SQL Tip
4) Did you know?
5) In the News
6) ANSWER TO THE POP QUIZ


---------------------------------------------------------
1) POP QUIZ (answer at the end)
---------------------------------------------------------

What is the DBMS_WM package used for?


---------------------------------------------------------
2) Oracle Professional
---------------------------------------------------------
In the July issue of Oracle Professional, Roy Gomes explains how to 
manage long duration database transactions using Oracle’s new Workspace 
Manager facility. Steven Feuerstein explains various options for 
implementing a dynamic IN clause within your PL/SQL procedures.  And 
Scott Dial explains the ins and outs of the new Oracle 10g Data Pump 
feature.

Here’s how you can access Oracle Professional:
http://www.oracleprofessionalnewsletter.com

---------------------------------------------------------
3) PL/SQL Tip
---------------------------------------------------------
This month’s tip comes from Dan Clamage (http://www.clamage.com).

The program below illustrates using collections of cursor%rowtype in 
9.2.0.4. This makes it very easy and clean to load a collection from an 
explicit cursor, and then bulk load the data into another table (of the 
same form). Note, basing the collection on the cursor or table rowtype 
is interchangeable when selecting all columns in the table.

I prefer to use explicit cursors rather than an implicit cursor (I 
could just have easily done SELECT BULK COLLECT INTO) because the 
former are more flexibly defined and provide the following advantages:
  * ability to determine the desired scope of the cursor declaration;
  * ability to accept parameters, which allows me to enforce a limited 
scope;
  * reusability.

-- logged in as SCOTT
CREATE TABLE obj (
 object_type VARCHAR2(18)
,object_name VARCHAR2(30)
,status VARCHAR2(7)
);

DECLARE
  CURSOR cur_obj(v_user VARCHAR2)
  IS
  SELECT
    object_type
   ,object_name
   ,status
  FROM all_objects
  WHERE owner=v_user;
  TYPE typ_obj IS TABLE OF cur_obj%ROWTYPE;
  -- TYPE typ_obj IS TABLE OF obj%ROWTYPE;
  arr_obj typ_obj := typ_obj();
BEGIN
  OPEN cur_obj(USER);
  FETCH cur_obj BULK COLLECT INTO arr_obj;
  CLOSE cur_obj;
  IF (arr_obj.COUNT > 0) THEN
    FORALL i IN arr_obj.FIRST .. arr_obj.LAST
      INSERT INTO obj
      VALUES arr_obj(i);
    DBMS_OUTPUT.PUT_LINE('#rows inserted: ' || SQL%ROWCOUNT);
  ELSE
    DBMS_OUTPUT.PUT_LINE('no objects found.');
  END IF;
END;
/
#rows inserted: 7

select *
from obj;

OBJECT_TYPE OBJECT_NAME STATUS
----------- ----------- ------
TABLE       DEPT        VALID
INDEX       PK_DEPT     VALID
TABLE       EMP         VALID
INDEX       PK_EMP      VALID
TABLE       BONUS       VALID
TABLE       SALGRADE    VALID
TABLE       OBJ         VALID

Dan Clamage is a regular contributor to the Oracle Professional 
newsletter.  Look for his upcoming article on using Stored Procedures 
within Crystal Reports.

--------------------------------------------------------
4) Did you know?
--------------------------------------------------------
Oracle Database 10g Release 2 is now available for download from 
Oracle’s OTN website.  The new release is currently only available on 
the Linux x86 platform, but expect other platforms to be supported 
shortly.  Improvements in Automatic Storage Management (ASM), the 
Database Upgrade Assistant, RAC, XML support and performance are just a 
few of the areas addressed in this new release.

For more information, visit : 
http://www.oracle.com/technology/products/database/oracle10g/index.html

--------------------------------------------------------
5) In the News
--------------------------------------------------------
After its recent acquisition of Retek Inc, Oracle just announced that 
it has acquired retail software maker ProfitLogic Inc. for an 
undisclosed sum.  ProfitLogic, a privately-held company with about 250 
employees, makes software that analyzes sales data in order to help 
retailers better promote and price their wares and manage inventory.  
This latest deal fits with expectations cited earlier in the week by 
industry sources that Oracle will continue to snap up smaller software 
vendors, particularly those specializing in specific industries, in a 
bid to nab market shares from larger rival SAP AG.  Expect the buying 
spree to continue through the year.

*               *               *               *

Oracle’s stock has climbed about $2.00 in the past 2 months to about 
$13.50.  Recent acquisitions, strong Second Quarter financial results 
and lower than expected merger costs of the Peoplesoft purchase are all 
contributing factors to the strength in the stock price.  Oracle CEO, 
Larry Ellison stated that new application sales jumped 52% in the 
Quarter and is upbeat about the future.

*               *               *               *

---------------------------------------------------------
6) ANSWER TO THE POP QUIZ
---------------------------------------------------------
The DBMS_WM package was first introduced in Oracle 9i as part of the 
Oracle Workspace Manager. The pre-defined package has a set of 
procedures for version enabling a database table and performing other 
workspace operations.

Let’s say you have a table called PERSON and you want to manage long 
transactions on the table without having to lock the table for long 
periods of time.  1 option is to use the Workspace Manager using the 
sytax of:
exec DBMS_WM.EnableVersioning(‘PERSON’);

This will replace the real PERSON table with a view of the same name 
and then create a table with a suffix like PERSON_LT.  The table will 
have 4 additional columns added to track the versioning of the data 
changes made to it.

There are many additional procedures available to manage workspaces, 
privileges, locks, conflicts, etc.

The concept is pretty cool and if you want more information about 
Workspace Manager and how it works, I urge you to read Roy Gomes’ 
article in the July 2005 edition of Oracle Professional.