Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.7 April 4, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) Oracle's Strategy for Java -- Part IV 3) SQL Tip: Rollup and Cube functions 4) Your Feedback 5) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- Q1. After you've created a database user account, can you log in as that user right away? Q2. What is the default quota for a given database account on any tablespace? Q3. Mary has a quota of 10 M bytes on the data_ts tablespace, and John has a quota of 20M on data_ts. Mary has created a table called emp on data_ts, and has given the insert privilege to John. Assuming that there's enough free space on data_ts, how much data can John insert into the emp table? Q4. Assuming that there are 10 application tablespaces in the database, how do you grant Susan the ability to consume as much space as possible on these tablespaces? Q5. How do you limit the idle time of a database session to 30 minutes, after which point the session will be disconnected? --------------------------------------------------------- 2) ORACLE STRATEGY FOR JAVA -- PART IV --------------------------------------------------------- My colleague Jim Skehill will give examples of BC4J in this installment of the series on Oracle strategy for Java. Jim's biography: Jim has a BSc in Computer Science from the University of Toronto, Canada. He's been programming in C/C++ for 11 years and in Java for four years, mostly in the financial services field. You can reach him at mailto:JSkehill@ProcaseConsulting.com. * * * * * In past columns, I've discussed Oracle tools for Java development. In this column, I present a real-life case study using these tools. I was told to create a demo for a "TravelInfo" Web site that was supposed to provide reviews of hotels, restaurants and various other attractions in various cities around the world. The structure was simple: There would be a page for each city that would contain summary information for various attractions -- e.g., hotels, restaurants, etc. Clicking on the summary information for any attraction would give you the details for that attraction. All information came from an Oracle database. I got the word at 11:00 a.m. and had until the end of that day to produce the pages. At the point when the demo request was made I was familiar, in general, with the project spec, had mockups of the Web pages in the site, and a drawing of the data model. I wasn't too sure how much, if anything, I'd have to show by the end of the day but decided to aim to have some version of the Hotel Review page done. The first thing I did was draw up a task list that looked like this: 1) Decide what data was needed. 2) Decide how to generate the result set. 3) Convert the result set to an HTML document. 4) Test and Deploy. Below, I describe how I accomplished each of these tasks. 1) Decide what data was needed. The first step was to start JDeveloper, point the New Application wizard at the "TravelInfo" database schema, and generate the family of BC4J classes for that schema. You can then run these classes on the actual data in the schema using the Tester to get a bird's eye view on the data in those entities. Based on what you see, you can then pick the entities that contain the data you need for the Web pages you want to build. At this point, I decided to be more ambitious and, instead of creating a page to view Hotels, create a page to view any attraction for a city. The URL would be something like: http://../TravelInfo?CityCode=TOR&AttractionType=Hotel 2) Decide how to generate the result set. The second step was to create a BC4J View object (not to be confused with a database View) which will manage the result set. A wizard is provided which makes this largely a point-and-click exercise. It first provides a list of BC4J entities to chose from, then lists the columns within those chosen entities to fine-tune your selection. It also takes care of the join clause for any two entities with related foreign keys. In generating this BC4J View, the only SQL I had to hand-code was the bind variables -- i.e., input values -- in the WHERE clause. I wanted to be able to set the City (e.g., "Toronto") and AttractionType (e.g., "Hotel") and get the data for all relevant attractions in that city. So I added to the where clause: (Attraction.CITY_CODE = ?) AND (Attraction.ATTRACTION_TYPE = ?) At runtime I would supply values for the bind variables - - e.g., for the sample URL, above the values would be "TOR" and "Hotel" respectively. It's usually at this point that you add the business rules. Since this project only involved the output of data, the only business rules were the formatting of phone numbers and ZIP/postal codes. 3) Convert the result set as an HTML document. My goal is to view the result set through a Web browser. Oracle provides an extensive set of classes to generate HTML code from BC4J Views, but I avoid these. I prefer to first generate an XML document and then work the HTML/WML/, etc., conversion by applying an XSL style sheet (more about how you do this in next week's column). As an initial step, I just wanted to display the raw XML in the browser. Again, thanks to JDeveloper wizards, I could do this in minutes. One wizard generated an empty Java Server Page or JSP (another subject for a future column!). A second wizard let me embed an XMLData bean (another BC4J class) in that page that took my BC4J View object and rendered it as an XML document. I then ran the JSP in the JDeveloper debugging environment. After viewing the XML Document to ensure that I had all the data I needed, I wrote the XSL file to transform the XML into HTML and pointed the XMLData bean to it. I now had an HTML document. 4) Test and Deploy. JDeveloper comes with a complete debug/test/deploy toolkit for Java applications, servlets, and JSPs. In fact, this environment allows me to build up my result set in increments, adding more columns from more tables at each iteration. What impressed me most was the lack of hand-written code (as opposed to Wizard-generated code) in my application. So, in fact, there wasn't a lot to test. Conclusion: After nine hours and several cups of coffee, I now have a reasonable looking demo. In fact, it was more than a demo. Most demos are so full of hard-coded values and kludges that they have to be thrown out once the actually development begins. What I had here was a good starting point for the project. This column skirted around two key technologies : XML and JSP. More about them in my next column. --------------------------------------------------------- 3) SQL TIP: Rollup and Cube functions --------------------------------------------------------- A subscriber, Sameer Wadhwa, had submitted a tip on the use of rollup and cube functions in Oracle8i. Although we had explored this topic last year, it'll definitely be worthwhile to share Sameer's insight on this interesting topic with you all. Here it is: To understand the power of ROLLUP and CUBE functions, consider the following SQL statement: SQL> compute sum of totsal on deptno SQL> break on deptno SQL> select deptno,job,sum(sal) totsal from emp group by deptno,job; DEPTNO JOB TOTSAL ---------- --------- ---------- 10 CLERK 1300 MANAGER 2450 PRESIDENT 5000 ********** ---------- sum 8750 20 ANALYST 6000 CLERK 1900 MANAGER 2975 ********** ---------- sum 10875 30 CLERK 950 MANAGER 2850 SALESMAN 5600 ********** ---------- sum 9400 Now see the use of ROLLUP Function SQL> select deptno,job,sum(sal) totsal from emp group by ROLLUP(deptno,job); DEPTNO JOB TOTSAL ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 <- Total of Deptno 10 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 9400 29025 <- Grand Total So if you compare the two results, you will notice that you are getting the same output. By using rollup, you can avoid using the SQL*Plus compute operation and the SQL group by function. Now see the use of CUBE function. SQL> select deptno,job,sum(sal) totsal from emp group by CUBE(deptno,job); DEPTNO JOB TOTSAL ---------- --------- ---------- 10 CLERK 1300 10 MANAGER 2450 10 PRESIDENT 5000 10 8750 <- Total of Deptno 10 20 ANALYST 6000 20 CLERK 1900 20 MANAGER 2975 20 10875 30 CLERK 950 30 MANAGER 2850 30 SALESMAN 5600 30 9400 ANALYST 6000 CLERK 4150 MANAGER 8275 <- Total by JOB PRESIDENT 5000 SALESMAN 5600 Grand Total 29025 In addition to the functionality provided by rollup, the cube function automatically produces the total with respect to second group --i.e., the JOB column in our case. Also at end you will see the grand total. In summary, the rollup and cube aggregate functions allow developers and DBAs to avoid the compute and group by operations and simplify the programming efforts. Let me know if you've tips you wish to share with the other members of the newsgroup. --------------------------------------------------------- 4) YOUR FEEDBACK --------------------------------------------------------- David Cuffe wrote to me about the additional methods available to define the NLS_DATE_FORMAT option: "The NLS_DATE_FORMAT can be set at several different levels and an order of precedence determines which setting is used ... * session level using ALTER SESSION SET NLS_DATE_FORMAT... * client level as an environment variable * client level as a registry setting (Windows NT client) * database level as an initialization parameter in INIT.ORA If the parameter isn't set at any of these levels then the values of NLS_LANG and NLS_TERRITORY determine the effective value of NLS_DATE_FORMAT." Thanks, David, for your feedback. --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ --------------------------------------------------------- Q1. After you've created a database user account, can you log in as that user right away? Ans. No. You must grant the new user the CREATE SESSION privilege so that he/she can log in. Here's an example: SQL> CREATE USER jdoe IDENTIFIED BY jdoepw; SQL> GRANT create session TO jdoe; Note that user access is controlled by privileges. By default, a user has no privileges, meaning he/she cannot do anything -- e.g., creating a table, creating an index, etc. You must grant the appropriate privileges to the user. Q2. What is the default quota for a given database account on any tablespace? Ans. Zero. This implies that a user may have the CREATE TABLE privilege, but he/she will not be able to create any object requiring storage. Here's an example of how you can assign a quota to a user: SQL> ALTER USER jdoe QUOTA 10M ON data_ts; Q3. Mary has a quota of 10 M bytes on the data_ts tablespace, and John has a quota of 20M on data_ts. Mary has created a table called emp on data_ts, and has given the insert privilege to John. Assuming that there's enough free space on data_ts, how much data can John insert into the emp table? Ans. Quotas are based on the value assigned to the owner of the table. In this case, the owner of the table is Mary, so John can insert up to 10M bytes of data into the emp table. Q4. Assuming that there are 10 application tablespaces in the database, how do you grant Susan the ability to consume as much space as possible on these tablespaces? Ans. The easiest method is to grant UNLIMITED TABLESPACE to the user. This implies the grantee can use any amount of space in all ablespaces. If you wish to grant access only to the 10 listed tablespaces, and nothing else, you should issue the ALTER USER command: SQL> ALTER USER jdoe QUOTA 10M ON tablespace_1; SQL> ALTER USER jdoe QUOTA 10M ON tablespace_2; SQL> ALTER USER jdoe QUOTA 10M ON tablespace_3; SQL> ... Q5. How do you limit the idle time of a database session to 30 minutes, after which point the session will be disconnected? Ans. You can use the profile object to define maximum idle time for a session. --------------------------------------------------------- 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.