TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Oracle Professional 3) SQL Tip 4) Oracle’s FREE database 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What does SET TRANSACTION READ ONLY do? --------------------------------------------------------- 2) Oracle Professional --------------------------------------------------------- In the November issue of Oracle Professional, Steven Feuerstein offers a smorgasbord of tips and reflections on various elements of PL/SQL’s error handling and raising capabilities. Bulusu Lakshman discusses the top 10 enhancements he feels Oracle has introduced in Oracle10g PL/SQL: better PL/SQL exception handling, collection improvements, performance- related improvements, and new PL/SQL API packages to name a few. And, Andrew Batishchev proposes a method to simplify lengthy chains of Pipelined Table functions. Here’s how you can access Oracle Professional: http://www.oracleprofessionalnewsletter.com --------------------------------------------------------- 3) SQL Tip --------------------------------------------------------- Have you ever had the need to return the day of the week that is closest to a given date? For example, let’s say you are thinking of a date in history, which you know is a Saturday, but not sure the exact date in question. I recently was asked that question from a student of mine during a training session and came up with the following answer. I accept 2 input parameters; 1 for the date in question and 1 for the day of the week you are interested in finding. accept dt char prompt 'please enter your date: ' accept dy char prompt 'please enter day of week: ' select &&dt "date", case when next_day(&&dt,'&&dy') - (&&dt) > 3.5 then next_day(&&dt-7,'&&dy') else next_day(&&dt,'&&dy') end "closest &&dy" from dual / Let’s assume my next birthday is June 18, 2006 and I’m looking to have a party on the closest Saturday, then I could run this script and get that information: SQL> @closest_day please enter your date: to_date('18-jun-2006') please enter day of week: Saturday date closest saturday --------- -------------------- 18-JUN-06 17-JUN-06 Let me know if you can think of another way to get the same result. -------------------------------------------------------- 4) Oracle’s FREE database -------------------------------------------------------- Oracle has release Oracle Database 10g Express Edition (Oracle Database XE), a free version of its fully functional database software. The code base is no different than Oracle’s fee-based database products and is fully compatible with the family of Oracle Database products including Oracle Standard Edition One, Oracle Standard Edition and Oracle Enterprise Edition. Oracle Database XE is available on 32-bit Linux and Windows operating systems. Since it is free, it does have some limitations such as: * at most one CPU or one dual core of processing capability is used; * memory usage is kept below one gigabyte (GB); * is limited to a single instance per system; and, * stores up to four GB of user data. So, small companies can start with the free version, then upgrade as demand increases. You can download this version from Oracle’s website. -------------------------------------------------------- 5) In the News -------------------------------------------------------- Oracle was back on the acquisition path this past month completing an acquisition of Global Logistics Technologies, Inc (G-Log). G-Log is a leading provider of logistics and transportation management software and Oracle expects the product line to mesh nicely with its existing ERP and supply chain applications. They plan to offer a Logistics Hub solution: an integrated offering helping customers in all industries address critical logistics and information requirements across global supply chains. * * * * Oracle’s Chief Technology Officer (CTO), Greg Maffei recently announced he’s leaving the company after only 4 months on the job. Prior to Maffei, Harry You was in the same role and left to become chief executive of BearingPoint Inc. after only 8 months on the job. Although analysts don’t expect the change to disrupt day to day operations, it does add an element of uncertainty as Oracle pushes ahead on the large task of integrating products from its acquisitions of PeopleSoft Corp. and Retek, and its planned purchase of Siebel Systems Inc. Oracle is not expected to replace Mr. Maffei and stated that his duties will be taken over by co-President Safra Catz. * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- The READ ONLY clause establishes the current transaction as a read-only transaction. This transaction-level read consistency differs from that of the default statement-level read consistency. All queries in your transaction see only changes that were committed before the transaction began. Read-only transactions are useful for reports or processes that run multiple queries against one or more tables while other users update these same tables. There are some restrictions to using this clause. For starters, it is not supported for the SYS user. Also, you cannot use a FOR UPDATE clause in a cursor select statement.