Oracle Pro Tips, Trends & Technology eXTRA Pinnacle Publishing http://www.pinnaclepublishing.com Issue 3.16 September 24, 2002 TABLE OF CONTENTS 1) Pop Quiz (answer at the end) 2) Industry News 3) Oracle News 4) Oracle Web Application Case Study 5) Oracle Products 6) Answer to the Pop Quiz -------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- How do you find out which database packages are being executed in memory? --------------------------------------------------------- 2) INDUSTRY NEWS --------------------------------------------------------- There's an interesting article that discusses how IT departments may have staff members from different generations. This may not be an uncommon phenomenon in other industries; however, as the IT industry matures in which multiple generations of technology co-exist, the multi-generational workforce will bring varied skills to the table, and enrich the industry as a whole. Some organizations have instituted a "reverse-mentoring program" that allows the younger staff members to educate the "senior executives and managers about the IT tools." This program also allows the younger members to learn non-technical skills, so that the learning process is truly bi-directional. Find out more at the following URL: http://www.informationweek.com/story/IWK20020906S0001 --------------------------------------------------------- 3) ORACLE NEWS --------------------------------------------------------- Oracle just announced its first quarter results. Revenue and profits were down compared to the same period last year; however, the results were mostly in line with the analysts' expectations. In particular, Oracle has managed to survive this economic downturn with massive layoffs. http://biz.yahoo.com/rb/020917/tech_oracle_earns_9.html --------------------------------------------------------- 4) ORACLE WEB APPLICATION CASE STUDY --------------------------------------------------------- Last week I mentioned that I'd provide a case study of building Web applications using Oracle. Let me start with one of the projects we're currently working on: Access Copyright - the Canadian Copyright Licensing Agency (http://www.accesscopyright.ca). Business model -------------- In Canada, newspapers, magazines, books, etc. are covered under the Copyright Act, meaning that it is illegal to make unauthorized copies such as photocopying and digital download of copyrighted works. Access Copyright is responsible for managing the licensing process, granting licensees the right to make copies on ad hoc or comprehensive basis. The fees charged to the licensees will be distributed to the publishers, authors and other rightsholders on a regular basis. Access Copyright keeps a small portion of the fees as the administration fee. Technology and business need ---------------------------- Access Copyright wanted to deploy a Web-based application for managing copyrights, which include licensing, rightsholder management, and royalty distribution. Technology and application architecture --------------------------------------- In the back end we use the Oracle9i database running on Unix. In addition we also use Oracle Reports server for reporting. The Web technology is Microsoft ASP and COM. The Web application is custom-built and supports a three-tier architecture. The front-end ASP pages include user interface controls, and do not include interaction with the database. The middle application tier provides advanced data manipulation capability such as caching and data validation. The Web module does not have direct SQL access to the back end; the interaction is done using database stored procedures and packages - the so called APIs (application program interface). This approach offers the following advantages: (a) The Web and the back-end teams can work in parallel. A key deliverable is for the respective team leads to define the APIs, and each team can start the development right away. Without these APIs, the front-end team will have to wait for the database tables to be 100% ready. Now, through the APIs, we may be able to return some values even though the underlying may not be finalized. This at least allows the Web developers to complete their development. (b) The APIs shield the detailed data interaction from the Web developers, so that they can focus on optimizing the Web access, and not having to worry about underlying relationships among the database tables. Often times, an API is defined for a Web page, and the data may be sourced from different tables. (c) The business rules (or at least the majority of them) can be centrally enforced in the APIs. In the next issue, we'll discuss our design approach and some of the challenges we face while developing the application. --------------------------------------------------------- 5) ORACLE PRODUCTS --------------------------------------------------------- Oracle iLearning is a module within the Oracle human resources product suite that allows an education provider to assemble education material. According to Oracle, "Oracle iLearning is an enterprise learning management system (LMS) that provides a complete infrastructure for organizations to manage, deliver, and track training participation of employees, customers, and partners in both e-Learning and classroom based environments." The main URL for the product is: http://www.oracle.com/applications/human_resources/index.html?ilearning.html On this page there is an interesting article on the power of iLearning - its scalability. It also provides some information on how much the product costs: http://www.oracle.com/applications/human_resources/index.html?brandonhall.html If you are interested in finding out more about this product, there are free Internet seminars provided by Oracle. Find out more at: http://www.oracle.com/start/ilearn/intro.html?src=1392173&Act=9 --------------------------------------------------------- 6) ANSWER TO THE POP QUIZ --------------------------------------------------------- My colleague Thomas Al-Hamad (mailto:talhamad@procaseconsulting.com) indicated one way of finding this out is to check the shared pool. He suggested the following query: select u.username, substr(v.sql_text, 1,70) "Start of sql", count(v.sql_text) "Seperate Instances" from v$sqlarea v, dba_users u where v.parsing_user_id = u.user_id and v.sql_text like '%BEGIN %' group by u.username, substr(v.sql_text, 1,70) having count(v.sql_text) >= 1 order by count(v.sql_text) desc Let me know if you have other ways of finding out, and I'll publish accordingly. --------------------------------------------------------- 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.