TABLE OF CONTENTS 1) POP QUIZ (answer at the end) 2) Did you know ... 3) IOUG Live 2004 4) SQL Tip 5) In the News 6) ANSWER TO THE POP QUIZ -------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is an autonomous transaction? -------------------------------------------------------- 2) Did you know ... --------------------------------------------------------- Oracle 10g Database Server is now generally available. This highly anticipated version is being touted as the industry's first database designed for grid computing. Grid computing reduces the cost of IT by clustering servers together to act as a single large computer, shifting resources dynamically between applications. And, Oracle is offering a new low cost option for small to mid-sized businesses, called Oracle Standard Edition One. A single server, 2 CPU machine costs only US$4,995 per processor or you can chose the Named User Plus option at US$149 per user (minimum of 5 users). For more information about Oracle 10G, visit: http://www.oracle.com/database/. -------------------------------------------------------- 3) IOUG Live 2004 --------------------------------------------------------- The International Oracle Users Group (IOUG) is meeting this year in Toronto, Canada from April 18-22. This is an excellent event to meet new people and share ideas and tips, as well as gain information about new products and techniques to help database administrators and developers work more efficiently. If you register by March 19th, you’ll save US$325 off the regular price of US$1,350 for the week. Daily passes are available for US$450. For more information, visit: http://www.ioug.org/live2004/. --------------------------------------------------------- 4) SQL Tip --------------------------------------------------------- I was asked by a student in a class I was teaching recently if it was possible to use the FORALL clause with a Merge statement in Oracle 9i? Actually, the answer to the question is NO. But the following workaround came from a colleague, Andrew Okimi, Managing Partner for Procase Consulting Inc.: The MERGE already has an implicit one row by one row operation, so from that perspective it is not really needed - to combine FORALL with MERGE... however, if the data is already in a pl/sql table, then I assume you could use a TABLE CAST in the USING clause as in the following example. --regular merge MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM employees WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*0.1); --table cast merge MERGE INTO bonuses D USING (SELECT employee_id, salary, department_id FROM table(cast(em_array as emp_table)) WHERE department_id = 80) S ON (D.employee_id = S.employee_id) WHEN MATCHED THEN UPDATE SET D.bonus = D.bonus + S.salary*.01 WHEN NOT MATCHED THEN INSERT (D.employee_id, D.bonus) VALUES (S.employee_id, S.salary*0.1); Let me know if you have any SQL tips that you’d like to submit and we’ll do our best to publish them in an upcoming eNewsletter. -------------------------------------------------------- 5) In the News -------------------------------------------------------- The Justice department announced Thursday, Feb 26th that it will file suit to block the takeover bid of PeopleSoft Inc. by Oracle. It stated that the merger would reduce competition in the corporate software market. Oracle later commented that they plan to fight this lawsuit and continue its aggressive bid for control of the company. For more information, please review the press release at http://www.oracle.com/corporate/press/index.html?2934537.html. * * * * Bill Gates held on to his reign as the World’s Richest Man with a net worth of $46.6 Billion according to the 2004 Forbes rating. Warren Buffet had the biggest jump coming in second with $42.9 Billion. Oracle’s Leading Man, Larry Elison jumped a little to $18.7 Billion, but dropped from 6th to 12th place this year. Many new faces joined the list of Billionaires, including JK Rowling, author of the Harry Potter books, and the creators of the Google search engine. For more information and the complete list, go to: http://www.forbes.com/maserati/billionaires2004/bill04land.html. * * * * Oracle’s plans to release its third quarter results after market close on Thursday, March 11th. After the announcement, CEO Larry Ellison and Chairman and CFO Jeff Henley will host a conference call for the financial community at 5:30pm (EST). A survey of brokers from First Call is expecting Earnings per share (EPS) to be 0.12 for the quarter, which compares to 0.11 for the same period a year ago. I guess we’ll find out soon how close they were. Oracle stock closed at 13.08, Monday, March 01, 2004. That’s a current P/E of 27.8 as compared to 32.6 for Microsoft. -------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- Normally, COMMIT and ROLLBACK statements placed inside called procedures or functions have an impact on pending data changes made in the main transaction. But, by adding the AUTONOMOUS_TRANSACTION PRAGMA to your procedure definition, you can instruct Oracle to perform the COMMITs or ROLLBACKs separately from your main transaction. The autonomous transaction is completely independent and manages its own locks and resources. Oracle treats the program as if it were running in a different session. This is useful when you want to construct modular pieces of code that start and complete an operation. For example, you might want to have a procedure that inserts into a log table each time somebody performs an operation, despite the fact that the user might cancel their operation. The syntax is something like: CREATE OR REPLACE PROCEDURE proc_error_log(pi_msg VARCHAR2) is PRAGMA AUTONOMOUS_TRANSACTION BEGIN INSERT INTO error_log VALUES (pi_msg, sysdate); COMMIT; END; / Then in my session I could do this: BEGIN -- perform some DML operation DELETE from emp where emp_id = 123; -- force a PK error UPDATE emp set emp_id=456; EXCEPTION WHEN OTHERS THEN proc_error_log(‘problem: ‘||sqlerrm); -- rollback the first DELETE ROLLBACK; END; / The reason I need the ROLLBACK in the EXCEPTION block is because the autonomous transaction that performed the COMMIT didn’t impact the main transaction, so my DELETE was still pending after the call to the proc_error_log procedure. Note that you can use the PRAGMA in a trigger as well if you need to perform a COMMIT inside without impacting the DML operation that caused the trigger to fire.