Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.8 April 17, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) Oracle's Strategy for Java -- Part V 3) Your Feedback 4) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- What is an Oracle8i INTERVAL? --------------------------------------------------------- 2) ORACLE STRATEGY FOR JAVA -- PART V --------------------------------------------------------- My colleague Jim Skehill will focus on XML in the next few issues; and then after that, he'll cover JSP. Jim's biography: Jim Skehill 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. * * * * * When I presented a case study in my last column, I mentioned two technologies for the first time: XML and Java Server Pages (JSP). I could be professorial and claim that I didn't explain them in advance in an attempt to "motivate the question." But the truth is I just forgot that these technologies were used in the case study. So let me make up for that lapse right now. XML is a big topic. A visit to your local bookstore would confirm that, so I won't attempt to explain it in detail here. Fortunately, the concept of an XML document is pretty easy to grasp, so I'll just present that. If you want more information on XML, the Web site http://www.xml.org serves as a clearinghouse for information on the topic. If you want a tutorial, check out http://java.sun.com/xml/tutorial_intro.html. Also, I strongly recommend the O'Reilly publication "Building Oracle XML Applications" by Steve Muench. XML is a markup language (XML stands for eXtensible Markup Language) similar to HTML. But unlike HTML, whose tags are set, XML allows you to define your own tags as you see fit (that's why it's called extensible). It's not hard to see that XML, in fact, includes HTML -- i.e., an HTML document is simply an XML document whose tags are , , , , and so on. So in a very real sense, XML is a meta markup language -- a markup language whose definition is general enough to include HTML, WML, DHTML and most other markup languages (but NOT SGML). So how would you use this? Well, say you have some (highly unnormalized) information about books in a database. You start up SQL Plus, type in the SQL: "select * from BookPrices" and get: Author Title Price Discount Quantity David Copperfield Charles Dickens 9.95 12 War and Peace Leo Tolstoy 11.95 14 Jude the Obscure Thomas Hardy 10.95 10% 22 Imagine that you wanted to read this data from your database and send it to one server as catalogue data, to another server as inventory data, and to a third server as price data. How would you do that? You could devise a format to pass the data and its structure and then arrange with each server so that they could properly interpret the format. With a bit of coordinated programming between you and the three other sites, it just might work. Or you could use XML. Oracle has provided the tools to convert any result set into an XML Document. It's a trivial exercise to take the above result set and generate the following XML document: Charles Dickens 9.95 12 Leo Tolstoy 11.95 14 Thomas Hardy 10.95 10% 22 Now, you have a document that contains not only the data but its structure as well. But wouldn't it take some programming to parse that document and extract the relevant information? Not if you're working in Java. XML has enjoyed very wide support in the industry, and there are several XML toolkits, available in the Java language. Using these toolkits you could easily parse this document and then query the parsed data, much like you would a database, to extract the data you want. Because of its flexibility and strong software- support, XML is fast becoming the standard way to send data from Point A to Point B. The steps are simple: 1) generate an XML document (which includes the data AND its structure) at Point A. 2) send the XML document to Point B. 3) At Point B, use Java-based XML tools to extract and manipulate the data from that XML document. In my next column, I'll go back to the case study from last week and give technical details about how to do this. --------------------------------------------------------- 3) YOUR FEEDBACK --------------------------------------------------------- Paul Dal Bianco has the following comment regarding the advantages of database packages: "Another benefit would be the use of (public) named constants similar to those in Oracle built-ins. Such constants can allow English names to be used for exception handling or defining valid parameters for packages." Thanks, Paul, for your feedback. I definitely agree that it is advantageous to use database packages. Regarding my pop quiz in the last issue, Michael G. Lunny noted that I had a typo in the answer to question 4. I had left out the "t" in "ablespaces." More importantly, when setting quotas on multiple tablespaces for a particular user, you can combine the operation in one command, as shown below: ALTER USER jdoe QUOTA 10M on tablespace_1 QUOTA 10M on tablespace_2 QUOTA 10M on tablespace_3 . . . ; Thanks, Michael, for your feedback. --------------------------------------------------------- 4) ANSWER TO THE POP QUIZ --------------------------------------------------------- Oracle8i has a new feature called INTERVAL which allows you to specify a period of time, providing a simple way for you to specify days, hours, minutes, and seconds. Let's take a look at several examples. SQL> alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS'; Session altered. SQL> select sysdate + interval '10' year from dual; SYSDATE+INTERVAL'10' -------------------- 15-APR-2011 23:12:25 SQL> select sysdate + interval '100' year(3) from dual; SYSDATE+INTERVAL'100 -------------------- 15-APR-2101 23:11:32 SQL> select sysdate + interval '99' month from dual; SYSDATE+INTERVAL'99' -------------------- 15-JUL-2009 23:14:04 SQL> select sysdate + interval '100' month(3) from dual; SYSDATE+INTERVAL'100 -------------------- 15-AUG-2009 23:14:13 SQL> select sysdate + interval '8-4' year to month from dual; SYSDATE+INTERVAL'8-4 -------------------- 15-AUG-2009 23:14:45 Here are some additional examples of INTERVAL specifications and their meaning: Example Meaning ------------------------------------ --------------------------------- INTERVAL '2' YEAR 2 years INTERVAL '25' YEAR 25 years INTERVAL '2000' YEAR(4) 2000 years INTERVAL '30' MONTH 30 months INTERVAL '300' MONTH(3) 300 months INTERVAL '3' DAY 3 days INTERVAL '999' DAY(3) 999 days INTERVAL '72' HOUR 72 hours INTERVAL '15' SECOND 15 seconds INTERVAL '15.712' SECOND(2,3) 15.712 seconds INTERVAL '2-0' YEAR TO MONTH 2 years 0 months INTERVAL '2-6' YEAR TO MONTH 2 years and 6 months INTERVAL '3 23' DAY TO HOUR 3 days and 23 hours INTERVAL '4 8:59' DAY TO MINUTE 4 days, 8 hours, and 59 minutes INTERVAL '5:30:10' HOUR TO SECOND 5 hours, 30 minutes, and 10 seconds INTERVAL '20:10' MINUTE TO SECOND 20 minutes and 10 seconds As you know, Oracle date operations are based on the number of days. With intervals, you can now use more granular control when specifying date values. In summary, two types of intervals are supported: - YEAR TO MONTH handles year and month intervals - DAY TO SECOND handles day, hour, minute, and second intervals The same result can be achieved by more than one interval setting, as shown below: INTERVAL '2-6' YEAR TO MONTH INTERVAL '30' MONTH Both values equate to 30 months. Note that SECOND is the only interval component that accepts fractional components. Also take note of the delimiter used: Year and month values are separated by a dash (-); day and hour values are separated by a space; hour, minute, and second values are separated by colon (:). --------------------------------------------------------- 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.