TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Oracle Professional 3) SQL Tip 4) Oracle’s Acquisitions 5) In the News 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is the buffer limit for DBMS_OUTPUT.PUT_LINE? --------------------------------------------------------- 2) Oracle Professional --------------------------------------------------------- In the December 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 --------------------------------------------------------- Last month, I showed an example of how to find the closest day of the week given a date as a parameter. I got a response from a reader, Ilya Petrenko (ipetrenko@hotmail.com) regarding some alternatives and wanted to share them with you this month: -- 1 -- SIMPLE SQL alter session set nls_date_format='DD-MON-YYYY'; set ver off def DT="to_date('18-jul-2006')" def DY=Saturday select &&DT Date# , to_char(&DT , 'Day') Current_Day , '&&DY' Day# -- Find Closest Day based on Min Value of Subtracted Days , DECODE( LEAST(&DT - NEXT_DAY(&DT - 7,'&DY'), NEXT_DAY(&DT ,'&DY') - &DT ) ,&DT - NEXT_DAY(&DT - 7,'&DY') , NEXT_DAY(&DT - 7,'&DY') ,NEXT_DAY(&DT ,'&DY') ) Closest_Day from dual; DATE# CURRENT_D DAY# CLOSEST_DAY ----------- --------- -------- ----------- 18-JUL-2006 Tuesday Saturday 15-JUL-2006 def DT="to_date('19-jul-2006')" DATE# CURRENT_D DAY# CLOSEST_DAY ----------- --------- -------- ----------- 19-JUL-2006 Wednesday Saturday 22-JUL-2006 -- 2 -- Understandable SQL with NO CASE/DECODE scenarios SELECT Date# , DT_Day# Start_Day , DAY# , NEXT_DAY(Date# + (DT_D# - INIT_D), Day#) Closest_Day FROM( select &&DT Date# , '&&DY' Day# , TRIM(to_char(to_date('01010001','MMDDYYYY') + ROWNUM -1 ,'Day')) INIT_Day , to_char(to_date('01010001','MMDDYYYY') + ROWNUM -1 ,'D') INIT_D , to_char(&&DT,'Day') DT_Day# , to_char(&&DT,'D') DT_D# from all_synonyms where rownum<8 ORDER BY INIT_D ) WHERE INIT_Day=Day# ; DATE# START_DAY DAY# CLOSEST_DAY ----------- --------- -------- ----------- 18-JUN-2006 Sunday Saturday 17-JUN-2006 DATE# START_DAY DAY# CLOSEST_DAY ----------- --------- -------- ----------- 20-JUN-2006 Tuesday Saturday 17-JUN-2006 DATE# START_DAY DAY# CLOSEST_DAY ----------- --------- -------- ----------- 21-JUN-2006 Wednesday Saturday 24-JUN-2006 Ilya also mentioned that there are many documented examples out on the internet to help guide you through various ways to manipulate dates. If you are interested, drop me a note and I’d be happy to steer you in the right direction. -------------------------------------------------------- 4) Oracle’s Acquisitions -------------------------------------------------------- Oracle has been strategically acquiring companies to strengthen its existing product offering and form new products and services over the past year. I’ve attempted to keep you updated with many of these acquisitions though this electronic newsletter. Despite all of the good news and solid performance, Oracle’s stock has been a bit of an underachiever as can be seen from this 3 year chart history: http://investdb.theglobeandmail.com/invest/investSQL/gx.show_chart?iact ion=Generate&pl_period=36W&pl_primary_listing=ORCL-Q. Anyway, Oracle has devoted a special section of its website to keep everyone updated on its many acquisitions. Visit the following url for more information: http://www.oracle.com/corporate/acquisition.html -------------------------------------------------------- 5) In the News -------------------------------------------------------- Microsoft has targeted the $11 billion CRM market with its recent release of Microsoft Dynamics CRM several weeks ahead of schedule. The new offering is a direct threat to Salesforce.com, which is the fastest growing CRM vendor and sells its software as a Web-based service. The timing couldn’t better as both companies are seeking to gain customers from the segment leader, Siebel Systems Inc., which is in the process of being acquired by Oracle Corp. Microsoft plans to offer its service online through partner hosting services and will charge $24.95 per user, which considerably undercuts Salesforce’s $65 a month per user fee. * * * * Time Warner’s CEO, Dick Parsons has recently stated they are not interested in selling AOL despite the rumours. Instead, Time Warner is negotiating a deal that could help AOL's transition from a business that relies on paid subscriptions to one that makes money based on advertising revenue. The company is in talks with both Microsoft Corp. and Google Inc. about a potential deal with AOL. Yahoo Inc. was also a potential partner, but pulled themselves out of discussions last month. * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- If you are running any version of Oracle prior to Oracle 10g Release 2, then the answer is 1,000,000. Well, in all honesty, I can’t vouch for what the limit is for versions prior to Oracle 7.3, but my guess is none of you really care. Now, the important part is that in Oracle 10gR2, the buffer limit has been eliminated so in theory, there is no limit. Also, the line length has been increased to 32,767 bytes, which happens to be the maximum length of a PL/SQL VARCHAR2 variable. This is a pretty nice enhancement as I cannot count how many times I’ve accidentally left long DBMS_OUTPUT.PUT_LINE statements in my programs only to have them fail due to a buffer overflow error. Those days will be behind me as soon as I can get all of my clients using the newest Oracle release.