Oracle Pro Tips, Trends & Technology eNewsletter Pinnacle Publishing http://www.pinnaclepublishing.com Issue 2.6 March 20, 2001 TABLE OF CONTENTS 1) Pop Quiz 2) Oracle's Strategy for Java - Part III 3) News: Oracle Share Price 4) Your Feedback 5) Answer to the Pop Quiz --------------------------------------------------------- 1) POP QUIZ (answer at the end) --------------------------------------------------------- Q1. If the time is March 15, 2001 7 a.m., what is the output of the following SQL? SQL> select sysdate + 1 from dual; Q2. What's the difference between the following two expressions? IF sysdate = to_date('20010315','YYYYMMDD') THEN ... IF trunc(sysdate) = to_date('20010315','YYYYMMDD') THEN ... Q3. In SQL*Plus, how do you display the current time in the SQL prompt? Q4. How do you set the default date format for SQL statements? --------------------------------------------------------- 2) ORACLE'S STRATEGY FOR JAVA -- PART III --------------------------------------------------------- Many thanks once again to my colleague Jim Skehill who's writing the third 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 4 years, mostly in the financial services field. You can reach him at mailto:JSkehill@ProcaseConsulting.com. * * * * * My last column was devoted the Oracle's Java Development environment, JDeveloper. In this column, I'll discuss Oracle's Business Components for Java, or BC4J for short. Business Components for Java (BC4J) is a library of Java classes that, coupled with the Oracle and Jdeveloper, dramatically speeds the development of distributed database applications. It does this in two main ways. 1) Effortlessly reproduces, in Java classes, any database schema; and 2) Simplifies the coding of business logic. Let's look at each of these. BC4J's strongest selling point is its ability to reproduce, in Java classes, any database schema. Once the schema is designed, producing the corresponding Java classes can be done in minutes. All you have to do is connect to the database in JDeveloper, select the desired schema from a list, hit the OK button, and the Java classes are generated. The generated classes provide object-oriented access to all tables and columns in the schema. They are aware of primary- and foreign-key constraints and how tables are logically linked through foreign keys. They manage the mapping from SQL data types to equivalent Java classes (something that is often a problem in JDBC). And because they are Java they can run almost anywhere, bringing the logic of the database schema with them. Let's look at a simple example. Say your database schema had an INVOICE table that was related to an INVOICE_LINE_ITEM table in a one-to-many fashion (i.e., a single INVOICE record could be related to one or more records from the INVOICE_LINE_ITEM table). Generating BC4J classes for this schema would result in Java classes named Invoice and InvoiceLineItem corresponding to these two tables. Also the Invoice class would have a method called getInvoiceLineItem. Now, once you had an invoice object and had scrolled to a certain record of interest, you only need to call the getInvoiceLineItem method to access the related line item data. Note that you would not get ALL line item data, just the data related to the current record of the Invoice object. From the InvoiceLineItem, you could go on to access the price data, tax data, discount data, etc. Basically, if tables were linked in the schema, then the corresponding BC4J classes would be linked and you could use these links to access related data. You may say, "Big Deal! I can easily do that in the database using SQL." But remember you aren't in the database -- you're on the middle tier. And you aren't using SQL -- you're using Java (If the significance of this isn't clear to you, go back to my first column in this series). That all the information encoded in a schema can so effortlessly make the trip from the database in SQL to the middle tier in Java is, in fact, a very big deal. Okay. So you have a bunch of Java classes on the middle- tier representing your schema. Now what? Well BC4J also simplifies the coding of business logic. Once these classes are generated, the programmer can immediately begin coding the business rules. BC4J provides an architecture for slotting in data formatting, validation, calculated fields, etc. It also provides messaging to coordinate refreshing of middle-tier objects. BC4J is comparable to EJB (Enterprise JavaBeans) in that it takes care of the database reads and writes and other "database plumbing" so that the programmer can devote himself/herself to the business rules, which are the real meat of the application. In my next column, a case study that shows BC4J in action. --------------------------------------------------------- 3) NEWS: Oracle Share Price --------------------------------------------------------- Oracle share price recently sank to its lowest value since November 1999, after the company reported earnings that met previously lowered forecasts and said it was uncertain about future results because of the weakening U.S. economy. Oracle share price, approximately US$14, is about 30% of its peak value of US$46-7/16. Oracle's database revenues grew 6 percent, whereas applications revenue growth came in at 25 percent, less than the 50 percent the company had been expected to achieve just weeks earlier. Oracle Chief Financial Officer Jeff Henley said Oracle was assuming that the economy would worsen and that his best "guess" was for the company to post flat license revenue growth for its database products and 15 to 30 percent growth for applications sales. CIBC World Markets analyst Melissa Eisenstat, however, questioned the company's blaming the economy for its disappointing quarter. "In speaking with Oracle salespeople, we have heard repeatedly that they are having difficulty competing with Siebel Systems for CRM (customer relationship management) and i2 Technologies for supply chain. Management's lack of explanation and our conversations with customers and salespeople lead us to believe that the economy is not the only reason for the shortfall," she said. For more information, refer to the following article: http://dailynews.yahoo.com/h/nm/20010316/bs/oracle_earns_dc_8.html --------------------------------------------------------- 4) YOUR FEEDBACK --------------------------------------------------------- In my previous article, I wrote that a PL/SQL stored program has a maximum size of 64K. According to Keith Swartz: "The maximum size in 8i and beyond is a whopping 64 *MB*. More specifically, up to 7.3, you could have 2**14 (16K) diana nodes, and from 8.0 to 8.1.3, 2**15 (32K) diana nodes were allowed. With 8.1.3, this limit has been relaxed so that you can now have 2**26 (i.e., 64M) diana nodes in the parse tree for package and type bodies. There are also source code limits, but those were relaxed too. Prior to 8.1.3, the compiler could cleanly compile up to about 3,000 lines of code. Starting with 8.1.3, the limit was relaxed for package bodies and type bodies which can now have approximately up to about 6,000,000 lines of code. (This assumes 5 to 10 diana nodes per line of source code.) That means with 8.1.3 and later, your PL/SQL source code can actually be up to 256 MB in size!" Keith mentioned as well that this information is also found in Oracle Metalink, Bulletin 62603.1: http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=62603.1&p_database_id=NOT (if you're not a registered Metalink user, find out how to set up an account at http://metalink.oracle.com). Thanks, Keith, for your feedback. --------------------------------------------------------- 5) ANSWER TO THE POP QUIZ --------------------------------------------------------- Q1. If the time is March 15, 2001 7 a.m., what is the output of the following SQL? SQL> select sysdate + 1 from dual; Ans. March 16, 2001 7 a.m. Unit of measure for date manipulations is number of days. Q2. What's the difference between the following two expressions? IF sysdate = to_date('20010315','YYYYMMDD') THEN ... IF trunc(sysdate) = to_date('20010315','YYYYMMDD') THEN ... Ans. The first expression compares the date as well as the time component of the current system date ("sysdate"). In other words, only if the current date/time is midnight March 15, 2001 will be expression be evaluated to true. Any other time during March 15, 2001 will yield a value of false. On the other hand, the second expression removes the time component from the sysdate function, implying that any time during March 15, 2001 will produce a value of true. Other options for the date truncation command are available to reset the date value. Resetting the date value to the beginning of the calendar month: select trunc(sysdate, 'MONTH') from dual; TRUNC(SYSDA ----------- 01-MAR-2001 Resetting the date value to the beginning of the calendar year: select trunc(sysdate, 'YEAR') from dual; TRUNC(SYSDA ----------- 01-JAN-2001 Q3. In SQL*Plus how do you display the current time in the SQL prompt? Ans. Enter "SET TIME ON." Q4. How do you set the default date format for SQL statements? Ans. SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYYMMDD'; SQL> SELECT sysdate FROM dual; SYSDATE -------- 20010319 --------------------------------------------------------- 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.