TABLE OF CONTENTS

1) POP QUIZ (answer at the end)
2) Did you know ...
3) Oracle Events and Seminars
4) PL/SQL Tip - NDS
5) In the News
6) ANSWER TO THE POP QUIZ


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

What is DBMS_UTILITY.FORMAT_ERROR_STACK used for?


---------------------------------------------------------
2) Did you know ...
---------------------------------------------------------
... that LinuxWorld Conference and Expo was held the week of August 2nd 
in San Francisco.  Oracle has been committed to Linux since 1999 and is 
driving to become the largest Linux-based development organization in 
the world.  For more information of Oracle’s involvement with Linux, 
visit: http://www.oracle.com/technologies/linux/index.html

---------------------------------------------------------
3) Oracle Events and Seminars
---------------------------------------------------------
If you’ve spent time perusing the Oracle.com website, you still may not 
have found the main page for Oracle’s list of Events and Seminars.  
That’s probably because Oracle’s website is so full of information, and 
if you are like me, you tend to get lost sometimes.  Well, for those of 
you interested in checking out Oracle’s events and seminars in your 
area, visit http://www.oracle.com/webapps/events/Events.jsp.  You can 
use their handy calendar to find internet seminars, Oracle university 
seminars, and special events from around the world.

Oracle OpenWorld 2004 is one of the next major North American events 
planned for December 5th to 9th in San Francisco.  Visit the link above 
for more information on this event as well as others.

---------------------------------------------------------
4) PL/SQL Tip - NDS
---------------------------------------------------------

This month’s tip came from Dan Clamage (http://www.clamage.com/) and 
shows us how to use Native Dynamic SQL (NDS).

Subject: dynamic USING examples

The question came up, how can you have a dynamic USING clause to use 
with Execute Immediate? The answer is to do it dynamically, of course.

-- match column fsb_variables.var_abbr datatype
CREATE OR REPLACE TYPE obj_var_abbr IS TABLE OF VARCHAR2(50);

-- obtains a collection containing var_abbr's from my test table
DECLARE
  arr_abbr obj_var_abbr;
  l_stmt1 VARCHAR2(32767);
  l_stmt2 VARCHAR2(32767);
  l_using VARCHAR2(4000);
BEGIN
  l_stmt1 := '
BEGIN
  SELECT var_abbr
  BULK COLLECT INTO :C
  FROM fsb_variables v
  WHERE v.var_actual_type = :T
    AND v.var_active_flag = :F;
END;
';
  l_using := 'OUT :C,''R'',''P''';
  l_stmt2 := 'BEGIN EXECUTE IMMEDIATE :str USING ' || l_using || '; 
END;';
  EXECUTE IMMEDIATE l_stmt2 USING l_stmt1, OUT arr_abbr;
  FOR i IN arr_abbr.FIRST .. arr_abbr.LAST
  LOOP
    put_long_line('var_abbr=[' || arr_abbr(i) || ']');
  END LOOP;
  put_long_line('#rows found: ' || TO_CHAR(arr_abbr.COUNT)); 
END; 
/


-- Here's a DML example.
DECLARE
  str1 varchar2(200);
  str2 varchar2(200);
  val varchar2(20);
begin
  val := '1,1';
  str1 := 'delete from item_dim where item_key = :b1 and vendor_key = 
:b2';
  str2 := 'begin EXECUTE IMMEDIATE :str USING ' || val || '; end;';
  EXECUTE IMMEDIATE str2 USING str1;
  dbms_output.put_line('Deleted '||sql%rowcount);
end;
/

If you have a tip that you’d like to submit to us, please send it along 
and we’ll do our best to include it in an upcoming eNewsletter.

--------------------------------------------------------
5) In the News
--------------------------------------------------------
Oracle’s marketing people would have us believe that their database is 
“unbreakable”, but unfortunately, they didn’t confer with their 
development group before announcing this.  David Litchfield, a 
researcher at UK-based NGSSoftware, and his colleagues have recently 
uncovered over 30 security flaws in the database.  Oracle has stated 
that they will quickly provide patches for these flaws.

For more information, read: 
http://news.com.com/Oracle+plans+to+patch+peck+of+flaws/2100-1002_3-
5295799.html?tag=nefd.top

*               *               *               *

Red Hat has been developing their own version of application-server 
software to compete with IBM, Microsoft and BEA Systems.  They plan to 
announce this at the LinuxWorld trade show this week in San Francisco.  
Stay tuned to see if this will cause tension with IBM, a major 
distributor of Red Hat’s Linux software.

*               *               *               *


---------------------------------------------------------
6) ANSWER TO THE POP QUIZ
---------------------------------------------------------

First off, let me explain what DBMS_UTILITY is; it is an Oracle-
supplied package, often overlooked – even by yours truly - that 
contains many useful procedures and functions that you can use. The 
FORMAT_ERROR_STACK function is used mainly in exception handlers to 
look at the full error stack.  It returns a VARCHAR2(2000) and thus is 
generally recommended over using SQLERRM, which is limited to 255 
characters.

To see how to use this function, read Steven Feuerstein’s article, A 
modified perspective on error handling in PL/SQL, in the upcoming 
September issue of Oracle Professional.  Visit 
http://www.pinnaclepublishing.com to get subscription information about 
the Oracle Professional monthly print publication.