Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.19 September 20, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) SQL Tip: PL/SQL Tables 3) SQL Tip: Database Triggers 4) News 5) Your Feedback 6) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is Oracle's Database Cache? --------------------------------------------------------- 2) SQL TIP: PL/SQL Tables --------------------------------------------------------- My fellow Managing Partner at Procase, Andrew Okimi (mailto:aokimi@ProcaseConsulting.com), has a tip for our team. In an application, a user may enter 1, 2, 3, or many items -- for example, putting items on a shopping cart. Since it's up to the user, you don't know before hand how many rows there will be. How do you process the rows entered? One solution is to loop and do a query for one item at a time, but that is slow, requiring N queries. Another option is to use dynamic SQL, but there is parsing overhead. Yet another solution is to insert the values in a table and create a query with a join; but, while the query is now efficient, the insert causes too much I/O. Here is another solution that you may want to think about. Put the items in a PL/SQL table, and then have the query treat the PL/SQL table just like a relational table! This works fine in relatively simple cases. create type itemtype as object ( item_id number ,name varchar2(2000)) / create type itemtbltype as table of itemtype / set serveroutput on declare cursor c is select * from item where item_id between 17900 and 18010; t itemtbltype := itemtbltype(); begin -- populate the pl/sql table -- with 1, 2, 3, or 100's of rows... -- this is not the important part for rec in c loop t.extend; t(c%rowcount) := itemtype(rec.item_id, rec.name); end loop; -- now use the pl/sql table to dynamically control the result set -- this is assuming that nested loops is the way to go -- i.e. the number of entries in the pl/sql table is relatively few for rec in ( select /*+ ordered use_nl(f) index(f) */ f.name ,f.item_id from table(cast(t as itemtbltype)) t, item f where f.item_id = t.item_id ) loop dbms_output.put_line('found ' || rec.item_id || ' ' || rec.name); end loop; end; / --------------------------------------------------------- 3) SQL TIP: Database Triggers --------------------------------------------------------- Subscriber Bret Lowry (mailto:bret_lowry@hotmail.com) sent me this tip. I'll share it with you. Here's what Bret wrote: "Here's a tip on an obscure feature in Oracle. Let's say you have an application you purchased, and it creates tables in a schema called app. Now let's say that you want to track anyone who inserted or updated data in the table app.t1. Luckily, app.t1 contains some user_text fields which are free for the end-user to use as he/she sees fit. Of course, you don't want to place a trigger directly on app.t1 because the vendor releases updates on a regular basis, and those updates can include schema changes. The vendor may decide to add an insert or update trigger to app.t1, thus overwriting your trigger. Solution: Create an application schema of your own -- for example, myapp -- and create a synonym back to app.t1. create synonym myapp.t1 for app.t1; Then create an insert or update trigger on myapp.t1. This trigger will fire any time an insert or update is run against app.t1. The beauty is that even if an insert or update trigger already exists on app.t1, you can place an insert or update on myapp.t1, and both will fire. create or replace trigger myapp.iu_t1 before insert or update on myapp.t1 for each row begin select osuser into :new.user_text1 from v$session where audsid = (select userenv('SESSIONID') from dual); :new.user_text2 := to_char(SYSDATE, 'dd-MON-yyyy hh24:mi:ss'); end; The above trigger updates the columns in the app.t1. You could also modify this to insert the logging data into a table you create in the myapp schema, thus creating a running log of all DML against the table(s) in a common logging table." --------------------------------------------------------- 4) NEWS --------------------------------------------------------- There's an interesting article on India's emergence as a leading provider of IT services. It has identified the contributing factors for India's success: government support, highly skilled knowledge workers, export of high-tech workers from India to the United States, and the close business relationships that exist between Indian firms and overseas customers. Find out more at the following link: http://www.zdnet.com/intweek/stories/news/0,4164,2808571,00.html. --------------------------------------------------------- 5) YOUR FEEDBACK --------------------------------------------------------- Last issue's discussion on TNS_ADMIN generated a lot of feedback. I've included several of your comments below. Ivan Samuelson wrote: "Your tip on the TNSNames environment variable is a good one. I never knew that option existed. I guess the only bad part is that you have to make sure everyone has access to that share. "Of course, the best way to do it is to set up an ONAMES server. Of course, it means one more machine you have to manage, but we've been using it for a long time and just switched over our client base to it. Most of our company was using it except for our group. However, we were finding it harder and harder to have to redistribute the TNSNAMES.ORA file each time we made a change. Since moving to the ONAMES server, life has been much easier. "Still, your tip is one for people who can't afford to set up an ONAMES server, or else don't have the time or resources to set one up." Michael Kassow responded: "Another option for the maintenance of distributed Oracle client configuration files (TNSNAMES.ORA and SQLNET.ORA files) is to create a common copy of these files in a networked location and make standard client installed TNSNAMES.ORA and SQLNET.ORA files with a single IFILE= reference to the networked file." --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- Oracle 9iAS includes the Database Cache, which is a lightweight database used to cache frequently accessed tables. You can configure the lightweight database on the middle layer, such that 9iAS will access the middle tier instead of going to the database server for data. This reduces contention and network traffic, giving you better overall performance. Please refer to the 9iAS documentation for further details. --------------------------------------------------------- On a personal note, I must confess I was quite affected by the tragic events that unfolded at New York's World Trade Center. Although it may sound naive, it is my hope that by sharing knowledge, by facilitating communication, and by using technology for productive work, we can help make life a little bit better for all of us. 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) 2001 http://www.pinnaclepublishing.com All rights reserved.