TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Did you know ... 3) Oracle Professional 4) SQL Tip 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What does AWR stand for? --------------------------------------------------------- 2) Did you know ... --------------------------------------------------------- ... Oracle OpenWorld took place in San Francisco from December 5 to the 9th. This year, Oracle combined its business applications conference (Oracle AppsWorld) and technology conference (OracleWorld) into 1 single event. You can view session information online at: http://www.oracle.com/openworld/index.html ... Also, registration for IOUG Live 2005, taking place May 1 – 5 in Orlando, Florida is now open. You’ll save $200 USD for registering early and if you register by December 31, you have a chance to win tickets to Cirque du Soleil’s La Nouba. For more information, visit: http://www.ioug.org/live2005/ --------------------------------------------------------- 3) Oracle Professional --------------------------------------------------------- In the upcoming issue of Oracle Professional, World renowned Oracle expert Steven Feuerstein shows us how to take a poorly written piece of PL/SQL code and make it more readable, structured and efficient. Scott Dial introduces us to Automatic Segment Storage Management (ASSM), first introduced in Oracle 9i to help the DBA manage the FREELISTS in database objects. And, Peter Hamilton describes a new application framework that makes use of the object type inheritance functionality delivered in Oracle9i to localize object-specific business rules and to hide such functionality from the core applications. Here’s how you can access Oracle Professional: http://www.pinpub.com/op/ --------------------------------------------------------- 4) SQL Tip --------------------------------------------------------- In the last eNL, I introduced you to Cursor expressions and showed an example of how to use them in SQL. Here, I’ll show you how to take advantage of this by creating a function in the database to accept the REF CURSOR and call that function to display out your results. I have a database that tracks mutual fund data. The main table, FUND, has a recursive relationship to identify a ‘parent’ mutual fund. Suppose I want to get list of all parent mutual funds whose child mutual fund was established on or prior to the parent’s established date. First I’ll create a function that will accept a REF CURSOR and date as a parameter: CREATE or REPLACE FUNCTION func_name (pi_cur SYS_REFCURSOR , pi_date DATE) RETURN NUMBER IS v_date DATE; v_before number :=0; v_after number:=0; begin loop fetch pi_cur into v_date; exit when pi_cur%NOTFOUND; if v_date > pi_date then v_after:=v_after+1; else v_before:=v_before+1; end if; end loop; close pi_cur; if v_before > v_after then return 1; else return 0; end if; end; / Then I can write my query like this, to call that function in my SELECT list and get the results I’m interested in: SELECT f1.name, f1.established_date FROM fund f1 WHERE func_name( CURSOR(SELECT f2.established_date FROM fund f2 WHERE f1.fund_id = f2.fund_id_same_as), f1.established_date) = 1; NAME ESTABLISH ----------------------------------- --------- Templeton Int'l Balanced 17-OCT-94 TD European Growth 15-JUL-94 Scotia CanAm US$ Money Market (US$) 03-SEP-96 Universal Growth 27-APR-95 Mackenzie STAR For Max Equ Growth 18-SEP-95 Mackenzie STAR For Max Lg-Tm Gwth 01-JAN-95 Vistafund 1 Shrt Tm Sec 01-JAN-80 Vistafund 1 Cap Gain Gw 01-JAN-84 etc… Cursor expressions can also be used in the WHERE clause of a VIEW, but I’ll save that example for another time. For any of you out there that have done this and would like to submit your examples, please send them to me and I might include your example in an up coming eNewsletter. -------------------------------------------------------- 5) In the News -------------------------------------------------------- The battle for Peoplesoft Inc. continues. Oracle $24 bid has been highly scrutinized as being too low by the Board of Peoplesoft, despite Oracle’s arguments to the later. Prior to the November 20th deadline, Oracle had secured the backing of 60.8% of the shareholders at the $24 price, but the Peoplesoft Board still rejected the offer. Recently, Peoplesoft tried to make a case that the company is worth $31 a share. This battle is far from over and we are expecting a proxy fight at the next Peoplesoft shareholders meeting. * * * * Lenovo, China’s biggest computer maker recently bought IBM’s PC business for $1.25-billion. IBM has been increasing its focus on consulting, outsourcing and software, so the deal makes sense to industry observers. IBM will retain 18.5% of the new company. The acquisition makes Lenovo the third-largest PC company in the world. * * * * Sieble has reorganized and created a new division in its company to focus on smaller customers. They have recently added 70 salespeople and signed up a bunch of new distribution and integration partners. Siebel’s main competitors are SAP at the high end and salesforce.com at the low end of the market. Revenues at the company are on a steady decline so CEO Mike Lawrie hopes this new initiative will help turn that trend around. To read more about this, visit: http://www.thestreet.com/_tsclsii/tech/billsnyder/10197935.html * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- AWR stands for Automatic Workload Repository and was introduced with Oracle 10g. An Oracle built-in tool, AWR collects performance related statistics and derives performance metrics from them to track a potential problem. Snapshots are collected automatically every hour by a new background process called MMON and its slave processes. To view the statistics collected, you use the new ADDM, Automatic Database Diagnostic Monitor, which uses the data collected by AWR and offers solutions to the DBA. This new feature of 10g offers a significant improvement over Oracle’s existing solution, Statspack, which lacks the robustness of AWR.