TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Did you know ... 3) Oracle OpenWorld San Francisco 4) SQL Tip 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is the DBMS_UTILITY package used for? --------------------------------------------------------- 2) Did you know ... --------------------------------------------------------- ... you can download a free version of Oracle 10g Release 1 from Oracle’s website. So whether you are running Linux, Windows, Solaris, or HP, give the new database version a try before you buy. To download the software, visit their website at: http://www.oracle.com/technology/software/products/database/oracle10g/i ndex.html and follow the download steps. If you want to buy the software, you’ll first want to review the various licensing plans and offers that are available at: http://www.oracle.com/database/index.html. And, for those of you who are running SQL Server or DB2 databases, you’ll be interested to read about Oracle’s 100% trade-in credit. --------------------------------------------------------- 3) Oracle OpenWorld San Francisco --------------------------------------------------------- OpenWorld is the premier event for Oracle developers, IT managers and professionals. This year’s North American event will take place in San Francisco from December 5th to the 9th. If you register before October 15th, you’ll save $500 off the full conference price of $1750, so don’t wait until the last minute. For more information about this event and others, visit http://www.oracle.com/webapps/events/Events.jsp. Oracle OpenWorld London 2004 took place Sept. 6th through to the 8th and if you want content details from the event, visit the link above for more information. --------------------------------------------------------- 4) SQL Tip --------------------------------------------------------- We are often given data reporting requests that require us to calculate (sum) data across multiple levels of output or dimensions. For example, I may need to calculate the total sales for all of my products across each state, country, region and for each year. I can do this using a query with multiple UNION clauses, or write a pl/sql procedure to loop through my data and aggregate it, but that’s not efficient. Introducing ROLLUP and CUBE; they are part of the Analytical features that Oracle began introducing in Oracle 8i. They are extensions to the GROUP BY clause and are useful in data warehousing environments where you need to calculate subtotals across varying dimensions. ROLLUP will create a subtotal for each level of aggregation starting with the top level and moving downward, including a grant total. CUBE, on the other hand, can generate similar subtotals, but for all possible dimensions. Think of CUBE as being able to generate a cross tabular report for multiple dimensions of data. Let’s look at a brief example taken from Oracle documentation, first with ROLLUP across 3 dimensions: SELECT Time, Region, Department, SUM(Profit) AS Profit FROM sales GROUP BY ROLLUP(Time, Region, Dept); Time Region Department Profit ---- ------ ---------- ------ 1996 Central VideoRental 75,000 1996 Central VideoSales 74,000 1996 Central NULL 149,000 1996 East VideoRental 89,000 1996 East VideoSales 115,000 1996 East NULL 204,000 1996 West VideoRental 87,000 1996 West VideoSales 86,000 1996 West NULL 173,000 1996 NULL NULL 526,000 1997 Central VideoRental 82,000 1997 Central VideoSales 85,000 1997 Central NULL 167,000 1997 East VideoRental 101,000 1997 East VideoSales 137,000 1997 East NULL 238,000 1997 West VideoRental 96,000 1997 West VideoSales 97,000 1997 West NULL 193,000 1997 NULL NULL 598,000 NULL NULL NULL 1,124,000 Note that the NULLs in the output denote where the subtotals are being calculated for each level of data. Now you can see subtotals for your dimensions and this is quite useful, but what if I wanted to get a subtotal of different combinations of the level shown above. Meaning, let’s say I want to know what the total of VideoRental sales for 1997 regardless of Region, or I want to know total VideoSales in the West region, regardless of year? Well, let’s look at a CUBE example and see how we can get this information with a simple query: SELECT Time, Region, Department, SUM(Profit) AS Profit FROM sales GROUP BY CUBE (Time, Region, Dept); Time Region Department Profit ---- ------ ---------- ------ 1996 Central VideoRental 75,000 1996 Central VideoSales 74,000 1996 Central NULL 149,000 1996 East VideoRental 89,000 1996 East VideoSales 115,000 1996 East NULL 204,000 1996 West VideoRental 87,000 1996 West VideoSales 86,000 1996 West NULL 173,000 1996 NULL VideoRental 251,000 1996 NULL VideoSales 275,000 1996 NULL NULL 526,000 1997 Central VideoRental 82,000 1997 Central VideoSales 85,000 1997 Central NULL 167,000 1997 East VideoRental 101,000 1997 East VideoSales 137,000 1997 East NULL 238,000 1997 West VideoRental 96,000 1997 West VideoSales 97,000 1997 West NULL 193,000 1997 NULL VideoRental 279,000 1997 NULL VideoSales 319,000 1997 NULL NULL 598,000 NULL Central VideoRental 157,000 NULL Central VideoSales 159,000 NULL Central NULL 316,000 NULL East VideoRental 190,000 NULL East VideoSales 252,000 NULL East NULL 442,000 NULL West VideoRental 183,000 NULL West VideoSales 183,000 NULL West NULL 366,000 NULL NULL VideoRental 530,000 NULL NULL VideoSales 594,000 NULL NULL NULL 1,124,000 Now I can easily get the information I wanted with the use of a simple SQL statement. 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 Stock has been trading at 52 week lows recently and is priced below $10 per share at the close of trading September 8th. Recently Moors & Cabot downgraded the stock from a BUY to HOLD and Morgan Stanley is lowering their 2005 EPS estimate to .55 from .58 according to realtimetraders.com. Analyst Tad Piper, of USB Piper Jaffray, also downgraded the stock based on opinions from a survey of IT buyers which displays the company at its lowest level in 12 years and is showing signs of deterioration. * * * * Research in Motion, makers of the popular Blackberry email device, has recently unveiled its first smartphone. It has the shape of a standard mobile handset, but retains the typical Blackberry features, such as keyboard, email and organizer. This new device, named the BlackBerry 7100t, will compete against Nokia’s Communicator, Motorola’s MPx200 and palmOne’s Treo 600, though the 7100t will be priced hundreds of dollars cheaper at about $200 after rebates. * * * * Intel is predicting that by 2005, they will have a computer chip capable of performing 2 tasks at the same time. They plan to achieve this by building 2 microprocessors on top of a single silicon chip. This will allow a user to play a video game at the same time as burn a CD. In an attempt to remain ahead of their competitors, Intel also announced that it had begun shipping its next generation wireless internet technology, known as WiMax. To read the full story, visit: http://www.globetechnology.com/servlet/story/RTGAM.20040908.gtrintel08/ BNStory/Technology/. * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- DBMS_UTILITY is an Oracle-supplied package that comes along with the database. It contains a bunch of useful procedures and functions that you can reuse, though some have limitations in their use. For example, one procedure is called COMMA_TO_TABLE, which will convert a comma- separated list of names into a PL/SQL table of names. This could be a useful string parsing utility, except that it will only work with comma-delimited strings and the elements in the string must be valid PL/SQL identifiers. Another procedure is RECOMPILE_SCHEMA, which is supposed to recompile any invalid objects in the schema, but unfortunately, it fails to resolve all objects in a dependency tree. There are other programs that you can use, though only with trial and error will you find out which are truly useful and which you’ll have to redevelop yourself. To learn more about this package and how to overcome its deficiencies, be sure to look for Steven Feuerstein's article, "Resolving NAME_RESOLVE Issues", in the upcoming November issue of Oracle Professional. If you're not already a subscriber, visit http://www.oracleprofessionalnewsletter.com for subscription information and to get a free trial.