Oracle Pro Tips, Trends & Technology eXTRA Pinnacle Publishing http://www.pinnaclepublishing.com Issue 3.21 December 18, 2002 TABLE OF CONTENTS 1) Pop Quiz (answer at the end) 2) Industry News 3) Oracle News 4) Books 5) Answer to the Pop Quiz -------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is a global temporary table? --------------------------------------------------------- 2) INDUSTRY NEWS --------------------------------------------------------- This insightful article talks about the cost of overseas IT outsourcing -- getting IT and software development tasks in overseas IT shops such as India, China, Russia, etc. It talks about the benefits of overseas outsourcing, which includes the ability to provide support around the clock (one site in North America and the other in Asia). Further, it discusses how small to medium sized businesses can take advantage of overseas outsourcing. Find out more at: http://story.news.yahoo.com/news?tmpl=story&u=/nf/20021205/bs_nf/20160 --------------------------------------------------------- 3) ORACLE NEWS --------------------------------------------------------- Oracle CEO Larry Ellison is investing US$100 million in Pillar Data Systems, which specializes in network storage solutions. This investment is coming out of his funds instead of Oracle. Find out more at: http://biz.yahoo.com/rb/021210/tech_ellison_2.html Pillar Data Systems' Web site is: http://www.pillardata.com/flash/ --------------------------------------------------------- 4) BOOKS --------------------------------------------------------- Bulusu Lakshman, one of the contributors to Pinnacle's Oracle Professional journal, has written a book on PL/SQL. Published in November, "Oracle9i PL/SQL: A Developer's Guide" includes techniques for existing PL/SQL constructs as well as new PL/SQL features for Oracle9i. You can find out more at the publisher's Web site: http://www.apress.com/book/bookDisplay.html?bID=142 --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ --------------------------------------------------------- Oracle provides a global temporary table for you to store temporary data that will be automatically cleaned up when you complete your transaction or session. A session-level temporary table is one that holds the temporary for the duration for the current session. As long as you stay connected, the data will be kept. As soon as you disconnect (either explicitly or implicitly), Oracle will remove the data from the table for you. Within your current, however, you can issue multiple transactions, i.e. commit and rollback operations, and the data will still be there. On the other hand, a transaction-level temporary table is cleared as soon as you complete a transaction. The SQL syntax is: CREATE GLOBAL TEMPORARY TABLE ( ) ON COMMIT [ PRESERVE ROWS | REMOVE ROWS ]; The GLOBAL TEMPORARY keywords are needed to denote that this is a temporary table. Use PRESERVE ROWS to keep data for the session, and REMOVE ROWS to keep data only for the current transaction. I'll illustrate this with an example. First, let's test a session-level temporary table. SQL> create global temporary table tmp_session 2 (tmp_id number, tmp_desc varchar2(10)) 3 on commit preserve rows; Table created. SQL> insert into tmp_session values (1, 'A'); 1 row created. SQL> insert into tmp_session values (2, 'B'); 1 row created. SQL> commit; Commit complete. SQL> select * from tmp_session; TMP_ID TMP_DESC ---------- ---------- 1 A 2 B Note that after the commit, we'll still able to see the rows in the temporary table. Let's see what happens when I disconnect to the database. SQL> disconnect SQL> connect xxx/xxx Connected. SQL> select * from tmp_session; no rows selected As you can see, the data is gone. Next, let's look at a transaction-level temporary table. SQL> create global temporary table tmp_txn 2 (tmp_id number, tmp_desc varchar2(10)) 3 on commit delete rows; Table created. SQL> insert into tmp_txn values (1, 'A'); 1 row created. SQL> insert into tmp_txn values (2, 'B'); 1 row created. SQL> select * from tmp_txn; TMP_ID TMP_DESC ---------- ---------- 1 A 2 B Prior to the commit/rollback, you'll continue to be able to see the rows in the table, but what happens after the commit? SQL> commit; Commit complete. SQL> select * from tmp_txn; no rows selected The data is deleted! So do you find this feature useful? Can you come up with scenarios in which each of these types of tables can be used? Let me know and I'll publish your feedback in the next issue. --------------------------------------------------------- Well, that's it for this issue. I welcome your feedback, input, tips, suggestions, Web sites, and other Oracle- related news. If you send me something, please let me know whether I can use your name with your comments. I apologize in advance if I don't respond personally to each of your questions or suggestions, but I'll get to as many as I can in the eNewsletter if not personally. Garry Chan, Editor Database Architect mailto:GChan@ProcaseConsulting.com This eNewsletter is brought to you compliments of Pinnacle Publishing, Inc. Copyright(c) 2002 http://www.pinnaclepublishing.com All rights reserved.