Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 3.2 January 23, 2002 TABLE OF CONTENTS 1) Pop Quiz (answer at the end) 2) Did You Know... 3) URLs of the Issue 4) SQL Tip: Dropping Columns in Oracle 5) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What are global temporary tables in Oracle? --------------------------------------------------------- 2) DID YOU KNOW... --------------------------------------------------------- * that Oracle 8.1.6 supports the embedded CASE operator in an SQL statement? The ANSI SQL-92 standard includes a CASE operator that is similar in functionality to Oracle's DECODE operator. Syntax: CASE WHEN THEN [ WHEN THEN ... ] [ ELSE ] END The above CASE syntax can be embedded in a SELECT, InSERT, UPDATE, and DELETE statement, as illustrated in the example below: SQL> select emp_name ,country ,case when country in ('US','CANADA') then 'North America' when country in ('JAPAN','CHINA') then 'Asia' when country in ('UK','ITALY') then 'Europe' else 'Unknown' end continent from emp; EMP_NAME COUNTRY CONTINENT -------------------- --------------- ------------- Mary CANADA North America John US North America Sue JAPAN Asia --------------------------------------------------------- 3) URLs OF THE ISSUE --------------------------------------------------------- You can check out events organized by Oracle at: http://www.oracle.com/jsp/events/Events.jsp Also, Oracle is about to release the latest version of its ERP software, Oracle Applications 11i. Let's see what the analysts have to say regarding this latest release and its impact on Oracle. http://news.com.com/2100-1017-816801.html?legacy=cnet&tag=pt.mrktwtch.story.alrt.8507929 --------------------------------------------------------- 4) SQL TIP: Dropping Columns in Oracle --------------------------------------------------------- Prior to Oracle8i, you can't easily remove a column from a database table. In order for you to work around it, you basically have to re-create the table without the columns you wanted to remove. Because you're essentially dropping and re-creating the table, you'll need to recreate the dependent objects associated with the original table such as triggers, indexes, and grants. Oracle8i allows you to drop a column using the ALTER TABLE command. Syntax: ALTER TABLE DROP (, ..., ) [CASCADE CONSTRAINTS]; The command removes the column definition from the table, the associated data from each row, the indexes defined on the dropped columns, and the database constraints referencing the dropped columns. The CASCADE CONSTRAINTS option removes foreign key constraints that reference the dropped columns. Example: ALTER TABLE emp DROP (emp_desc); ALTER TABLE emp DROP (emp_id) CASCADE CONSTRAINTS; --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ --------------------------------------------------------- Starting with Oracle8i (8.1.5 and later versions), you can create a table that stores data that only exists within the current transaction or session. Upon completion of the transaction (or session), Oracle can clear the data automatically. Syntax: CREATE GLOBAL TEMPORARY TABLE <(column,...,column)> [ ON COMMIT PRESERVE ROWS ]; Note that the table's definition is permanent. In other words, once created, the table will continue to exist until you explicitly drop it. Further, the same table can be shared by multiple sessions to record temporary data for those sessions. Oracle ensures that each session or transaction only "sees" its own data. If the optional clause ON COMMIT PRESERVE ROWS is included, Oracle will remove the rows created by your session when you exit. On the other hand, if the clause is omitted, the contents will be local to your transaction, and the rows will be removed on transaction termination. Here's an example: SQL> create global temporary table temp1 (id number, name varchar2(20)); Table created. SQL> insert into temp1 values (1, 'A'); 1 row created. SQL> insert into temp1 values (2, 'B'); 1 row created. SQL> select * from temp1; ID NAME ---------- -------------------- 1 A 2 B SQL> commit; Commit complete. SQL> select * from temp1; no rows selected --------------------------------------------------------- Well, that's it for this week. 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. ********************************************************************** ORACLE PRO eXTRA (BI-WEEKLY) To be removed from this mailing list, forward this message to pinnacleor-unlist@dsi-epubs.net. (be sure to forward the ENTIRE message, or it will not unsubscribe you!) **********************************************************************