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 is the DBMS_ODCI package used for? --------------------------------------------------------- 2) Did you know ... --------------------------------------------------------- ... in Oracle 9i, a DBA can change the password of a user with the SYSDBA role? It’s true as long as the init.ora parameter, remote_login_passwordfile is equal to “EXCLUSIVE”. If it is set to “NONE”, then you aren’t allowed. Try this on your database and see what you can do with this knowledge. In next month’s edition of this eNewsletter, I’ll show you some that I’ve come across so send me your ideas if you have any. --------------------------------------------------------- 3) Oracle Professional --------------------------------------------------------- In the upcoming issue of Oracle Professional, Oracle expert Steven Feuerstein explores the weaknesses in one of the DBMS_UTILITY programs, NAME_RESOLVE, and shows you how he came up with an alternative. Moru Nuhamovici explains the benefits of autonomous transactions and shows you various ways how this feature can be exploited. And, Bulusu Lakshman explores how to apply best practices in Oracle Reports development with reference to error message handling. Here’s how you can access Oracle Professional: http://www.pinpub.com/op/ --------------------------------------------------------- 4) SQL Tip --------------------------------------------------------- Have you ever been in a situation within a procedure where you have to perform a series of INSERT statements and 1 or more fail, while others succeed and thus get COMMITed? If this is a scenario that you want to control, you might want to look at the MultiTable Insert feature introduced in Oracle 9i, Release 2. The syntax looks like the following: INSERT {all|first} [WHEN {condition}] INTO {table_name} ( {column_names} ) VALUES ( {select_column_names} ) … {select_statement}; I’ll use an example from Oracle documentation, but a better and more complete example will be provided in an upcoming Oracle Professional article written by Al Hetzel. Supposed I have an orders and customers table and want to break up the data and insert it into separate tables (small, medium, large and special). Here’s an example of this command: INSERT ALL WHEN ottl < 100000 THEN INTO small_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 100000 and ottl < 200000 THEN INTO medium_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 200000 THEN into large_orders VALUES(oid, ottl, sid, cid) WHEN ottl > 290000 THEN INTO special_orders SELECT o.order_id oid, o.customer_id cid, o.order_total ottl, o.sales_rep_id sid, c.credit_limit cl, c.cust_email cem FROM orders o, customers c WHERE o.customer_id = c.customer_id; Now you could have accomplished this with multiple insert statements, but each insert statement would have to perform a separate SELECT, whereas the example above has 1 SELECT for each INSERT performed. Performance is increased and you accomplish a single COMMIT point for each INSERT statement. I’m sure you can think of some good uses for this feature; I know I can! -------------------------------------------------------- 5) In the News -------------------------------------------------------- In last month’s edition of this eNewsletter, I reported that Oracle’s Stock had been trading at 52 week lows and was priced below $10 per share. Well, the stock is now trading around the $12/share range, as a number of Brokerage firms have increased their outlook for the stock. Thompson First Call recently increased its Next Fiscal Year EPS estimates from .64 to .65 and the Analyst Consensus recommendation is now a BUY (http://investdb.theglobeandmail.com/invest/investSQL/gx.estimate_prof? symbol_in=ORCL-Q ). * * * * Well, I’m sure you’d already heard about the landmark ruling from the U.S. District Court that Oracle’s takeover bid for PeopleSoft does not hurt competition. European authorities are expected to make their decision sometime this month, but indications are that they will follow the U.S.’s lead. This would essentially clear the way for Oracle to pursue this takeover bid, although 2 remaining hurdles would remain: the standard poison-pill and the special money-back-guarantee customer program, which Oracle says could add a $2 billion liability to the acquisition. Both of these are being contested in a Delaware Court. Most recently, PeopleSoft have ousted their CEO, Craig Conway and replaced him with retired founder, David Duffield. This comes on the heals of allegations that Conway lied to analysts about the impact of Oracle’s takeover bid on its business. * * * * PalmOne and Microsoft have recently announced a joint venture that will allow the next generation of PalmOne’s Treo to work with Microsoft’s Exchange email system. To read the full story, visit: http://www.globetechnology.com/servlet/story/RTGAM.20041005.gtpalmoct5/ BNStory/Technology/. * * * * --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- DBMS_ODCI is an Oracle-supplied package introduced in Oracle 9i Release 1. It contains a function, ESTIMATE_CPU_UNITS, which returns the approximate number of CPU instructions (in thousands) corresponding to a specified time interval (in seconds). This is useful in estimating CPU usage in cases where you may want to charge customers accordingly or associate CPU cost with a user-defined function. Here’s a simple example of its use: DECLARE ts date; te date; i integer; v varchar2(10000); BEGIN ts := sysdate; --call program you want to trace --I’ll run a test loop here instead for a in 1..500000 loop v := a; --force an implicit conversion on purpose end loop; te := sysdate; i := DBMS_ODCI.ESTIMATE_CPU_UNITS((te-ts)*1000); DBMS_OUTPUT.PUT_LINE('CPU units = '|| i); END; / CPU units = 2632 PL/SQL procedure successfully completed. I’d like to thank Dan Clamage (http://www.clamage.com) for introducing me to this new feature. If you have a new feature that you’d like to introduce us to, please send me an email and I’ll do my best to include it in an upcoming edition.