Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.10 May 15, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) Did You Know ... 3) Oracle's Strategy for Java 4) Your Feedback 5) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- How do you create an HTML Web page from SQL*Plus? --------------------------------------------------------- 2) DID YOU KNOW... --------------------------------------------------------- O'Reilly has published a book on Oracle's open source software. Here's some additional information released by O'Reilly: The just-released "Oracle & Open Source: Tools and Application" (Duncan and Hull, O'Reilly & Assoc. $39.95) is the first book to tie together the commercial world of Oracle and the free-wheeling world of open source software. It describes nearly 100 open source tools, from the widely applied (like Linux, Perl, and Apache) to the Oracle-specific (like Orasoft, Orac, OracleTool, and OraSnap). The authors describe where to find these tools, what their advantages are, and how to create and release new open source Oracle tools yourself. Chapter 1, "Oracle Meets Open Source," is available free online at: http://www.oreilly.com/catalog/oracleopen/chapter/ch01.html An article by Andy Duncan, "Oracle & Open Source: Gazing at the Crystal Ball," is available online at: http://oracle.oreilly.com/news/oracleopen_0401.html Registration has opened for O'Reilly's Open Source Convention in San Diego, July 23-27, 2001. For more information, see: http://conferences.oreilly.com/oscon/ For more information about the book, including Table of Contents, index, author bio, and samples, see: http://www.oreilly.com/catalog/oracleopen/ --------------------------------------------------------- 3) ORACLE STRATEGY FOR JAVA --------------------------------------------------------- Jim Skehill is back! 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. * * * * * Two columns ago, I gave a case study illustrating the benefits of BC4J. It involved reading tourist information (for example, hotels, restaurants, etc.) from a database and displaying it in a Web browser. XML was used in this case study, but I was vague about how. In my last column, I presented a primer on XML and mentioned that an HTML document is really a type of XML document. In this column, I'll go into details about using XML with BC4J. Generating an XML document from a BC4J View is easy. Oracle provides a Java class called XMLData that handles most of the details. In the BC4J View, you can set the XML tag names for the various fields, or you can use the default value which is the field name. So we just set these tags to get the desired HTML document, right? Wrong! The main reason we don't do this is that we'd like to keep the content (that is, the data being displayed) and presentation (HTML font, foreground and background color, etc.) separate. If we don't do this, then each time we want to change the way the data is displayed in the browser we'd have to edit our BC4J View. What we do is let the View produce a "raw" XML document that contains the basic structure of the View (that is, the record and field layout) and use XSL to generate the HTML document. XSL stands for eXtensible Style Language, and an XSL style sheet defines a way to format an XML document. An XSL style sheet is applied to an XML document to derive a different XML document. All we need to do is write an XSL style sheet that does the HTML formatting. Schematically, it would look like this: Raw XML HTML HTML Document + Formatting = Document XSL Doing things this way allows us the keep content and display logic separate. Our View doesn't know or care how it is displayed. Its responsibility is to generate an XML document that correctly represents the structure of the data it contains. On the other hand the XSL style sheet is completely responsible for the display. It is where you set the background colors, fonts, etc. This would come in handy if, say, we wanted to support WML in the future. All we would need to do is write the XSL style sheet to do the WML formatting and use that style sheet when we detect that the HTTP request came from a wireless device. Everything else would stay the same. Or say you provided Internet content - that is, Web pages -- for various third parties. Your customers would surely want to stamp their own look-and-feel unto the content you provided. An easy way for you do to this would be to generate the basic content as XML documents and use XSL as a final step to "plug in" the look and feel. Another example comes from my last column. Here you had to supply data from a book database to three different servers as catalogue, inventory, and price data. We had assumed that you sent the same raw XML document to all three, but you could use XSL to customize to document sent to each. In fact, many times this customization is absolutely necessary because the system receiving the data will only accept an XML document in a particular form. XSL is a language in itself and provides a powerful mechanism to transform and format XML documents -- that is, to make them look the way you want. In my next column, I'll discuss Java Server Pages. --------------------------------------------------------- 4) YOUR FEEDBACK --------------------------------------------------------- Jason Dufair has the following comment regarding Oracle's random number generator: "Anyway, at the risk of being too pedantic, dbms_random does not actually generate random numbers, but pseudo- random numbers (cryptographically speaking). These may be fine for various business applications, but would not likely be suitable for use in cryptography. For the a fine treatise on the difference between random and pseudo-random numbers, see http://www.counterpane.com/yarrow-notes.html or other postings at http://www.counterpane.com/." Robert Baillie came up with a good example on the use of autonomous transactions: "Procedure 'p_validate_route' is a procedure which takes records from a table, performs validation / defaulting on that record and then performs some processing driven by that record (an API procedure for example). p_validate_route FOR cursor_record IN cursor LOOP -- lb_validation_successful := TRUE; -- -- perform validation, setting referenced information, etc -- ... -- on error in validation (I.E. data problems) -- lb_validation_successful := FALSE; -- p_log_error ( cursor_record.primary_key , ln_error_number ); ... -- -- once validation / defaulting is complete -- IF lb_validation_successful THEN -- -- complete routing, mark record as routed -- ... -- COMMIT; -- ELSE ROLLBACK; END IF; -- END LOOP; p_log_error operates in an autonomous transaction as per your error procedure. What we're doing is effectively holding two transactions at a time, the commit and rollback processing of which are completely independent. So we always want to commit error records, but may not want to commit the parent record. The main difference between my and your examples is that we don't want to have to stop all processing in order to report the errors. Rather we want to report all the errors we can find, then fail the validation, rollback the validation / defaulting changes to the parent record and then continue on to the next record. This is a lot more difficult to do without autonomous transactions!" Many thanks to you both for your feedback. --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ --------------------------------------------------------- In SQL*Plus, you can format output in HTML by issuing the following command: SQL> set markup html on spool on The command enables HTML output. In addition, the 'spool on' option directs SQL*Plus to embed the document tags each time you issue the SPOOL command to save the output in a text file. Let's capture the output of a SQL in an HTML-formatted file: SQL> set markup html on spool on SQL> spool c:\temp\x.html SQL> select * from emp where rownum <= 2; SQL> spool off SQL> set markup html off spool off The c:\temp\x.html file contains the following: SQL> select * from emp where rownum <= 2; EMP_ID EMP_NAME 1 Mary 2 John SQL> spool off You can now open it in a Web browser. --------------------------------------------------------- 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.