Oracle Corooration has.broaae ned its development platform, integrating open standards such as ava and XML into the heart of the Oracle 8i database. This extended programming environment continues to exploit the qualities of scalabi[itv re[iabi[itv and efficienc~ of the world's most successful data management software but at the same time it provides new challenges and opportunities to programmers.
This book shows vou how to de/e[oo enterorise PL/SQL applications exploiting Java and XML. and how technologies such as EJBs can be moved to the 8i database. You'll work through case studies using a mix of both familiar and unfamiliar tools and languages, showing you now the various programming approaches can enhance each other.
Who is this book for?
This book is for orofessional Oracle database programmers who need a comprehensive roadmaD of the new programming possibilities with Oracle 8i. On the other nano if ou are already developing with Java and XML this book will show ~ou low to exploit PL/SQL and the enterprise features of Oracle 8i in optimally distributing, our n-tier applications. Knowledge of the essentials of PL/SQL programming is required, and familiarity with Java would be useful.
What does this book cover?
The Oracle 8i platform and architecture covering Net8 and the Oracle JVM
PL/SQL development using ti e Web Toolkit. PSR and Designer 6i
Java application development including a Java primer
Oracle 8i and .]ava Stored Procedures EJB, BC4J, JDBC, SQLj
XML development with an XML primer
Oracle XDK, XSQL, XSLT. Transviewer JavaBeans
Case studies using Designer 6i interMedia. Portal-to-Go. ASP and SOAP
Oracle Corooration has.broaae ned its development platform, integrating open standards such as ava and XML into the heart of the Oracle 8i database. This extended programming environment continues to exploit the qualities of scalabi[itv re[iabi[itv and efficienc~ of the world's most successful data management software but at the same time it provides new challenges and opportunities to programmers.
This book shows vou how to de/e[oo enterorise PL/SQL applications exploiting Java and XML. and how technologies such as EJBs can be moved to the 8i database. You'll work through case studies using a mix of both familiar and unfamiliar tools and languages, showing you now the various programming approaches can enhance each other.
Who is this book for?
This book is for orofessional Oracle database programmers who need a comprehensive roadmaD of the new programming possibilities with Oracle 8i. On the other nano if ou are already developing with Java and XML this book will show ~ou low to exploit PL/SQL and the enterprise features of Oracle 8i in optimally distributing, our n-tier applications. Knowledge of the essentials of PL/SQL programming is required, and familiarity with Java would be useful.
What does this book cover?
The Oracle 8i platform and architecture covering Net8 and the Oracle JVM
PL/SQL development using ti e Web Toolkit. PSR and Designer 6i
Java application development including a Java primer
Oracle 8i and .]ava Stored Procedures EJB, BC4J, JDBC, SQLj
XML development with an XML primer
Oracle XDK, XSQL, XSLT. Transviewer JavaBeans
Case studies using Designer 6i interMedia. Portal-to-Go. ASP and SOAP
The life of the Oracle database programmer is in a state of flux. Oracle Corporation is advancing rapidly into the middle and presentation tiers of the enterprise application with an array of new products and technologies. Perhaps more significanfiy, with the launch of Oracle 8i, more and more enterprise features are being moved into the database, bringing with them a wealth of new programming possibilities.
There is no doubt that Oracle programmers will continue to be in high demand - Oracle database products still dominate the world market. But there is some work to be done to close the gap between the traditional database programming environment and today's world of the component-based enterprise-oriented application. That's why Oracle has moved heavily into supporting standards such as Java and XML. And that's the gap that this book fills.
What this Book is About
This is a book about developing applications that use the Oracle 8i database. Since the launch of Oracle 8i,developers have had the option of working not only with PL/SQL but also with Java and XML directly within the database. Oracle Corporation has vigorously supported the integration of these languages, and their associated development tools, into their core database products. These new languages may, or may not, be familiar to database programmers, but the chances are that even if you are experienced in PL/SQL, many of the new programming opportunities in the Oracle environment will be unfamiliar.
This book will show you how to build Java and XML-enabled applications that interact with Oracle 8i. You will be using a variety of tools, ranging from established ones such as Oracle Designer, to new tools such as Oracle Portal-to-Go. Mostly, however, you will be developing code by hand, learning about Java technologies, XML tools, building web applications, and solving enterprise development problems.
Who Should Use this Book
This book is primarily aimed at programmers with a database background. Oracle has adopted Java and XML in a big way. As a result, a host of new Oracle middle tier and web tier applications have appeared, based upon these and other new technologies. It is possible to continue following the PL/SQL:stream, and to produce great applications, but sometime, someone is going to ask you to write your first Java stored procedure, an XML-enabled customer management system, or a wireless adapter for your existing PL/SQL web product.
This book will break down those barriers for you. You will see plenty of chapters with a mixture of the familiar and the unfamiliar. This will help you work out how these technologies can interact with, and enhance, each other.
How this Book is Structured
To help you use this book and structure your learning, we have divided it into the four sections outlined below. These are not rigid divisions, but they will help you navigate quickly to the area you need most.
Database. Two introductory chapters on the Oracle 8i platform and Enterprise Application Design lead into chapters on database architecture and connectivity, and how to design your database using Oracle Designer 6i. We don't attempt to cover all the database issues that you will need to know in order to work effectively with PL/SOL and Oracle. Some database knowledge, and some SQL programming, are assumed. These chapters will serve as a quick reference point and a source of vital knowledge when you hit problems in your own designs and implementations.
PL/SQL and the Web. In this section of the book, we focus on the web capabilities of Oracle's proprietary database language, PL/SOL. We will look at PL/SOL coding from the point of view of application design and from the point of view of making the best use of PL/SQL when it makes sense to use it in an enterprise application.
Java. Although the Java section is divided approximately into individual Java technologies, there is significant cross-over and you will find that each chapter covers several Java tools, as well as focusing on how to integrate yourJava with Oracle, PL/SQL and XML. We cover Java Stored Procedures, JDBC, SO LJ, EJB, BC4J,]avaMail, Servlets and JSPs. Each chapter in this section is database-oriented, and provides a starter database and full set-up instructions. Check out thelava Primer in Appendix B first, if you need to get yourself up to speed.
XML. The idea behind XML is to provide a common communication and data representation language with which to bring together diverse platforms and enterprises. The chapters in this section will show you how to build applications based on an Oracle data tier and connect them to other tiers with XML. You will learn to use Oracle XML Developer's Kit, XML SQL utility, XSLT, ASP, SOAP, interMedia, and Portal-to-Go's SimpleP, esult XML, along with other techniques for building Java and PL/SQL-enabled applications. We provide an XML primer in Appendix C, for those who need to catch up with the basic XML notions.
This book has 25 chapters and 4 appendices. In the following sections we will give you a breakdown of the content of these chapters so that you can plan how to use this book to suit your needs best. The Database
Chapter 1, Internet Programming with Oracle 8i. This chapter presents the Oracle 8i platform, the new tools and technologies that have appeared or been updated in Oracle 8i and Oracle Internet Application Server (iAS). In particular we set the scene for Oracle and Java, and talk about the new features and programming opportunities that this combination has introduced.
Chapter 2, Enterprise Application Design. Here we take a side step and remind ourselves of why we need to learn all these new languages and design patterns. Whether you are developing a company intranet, a simple web storefront, or a commercial N-tier application, you should be aware of the principles that are emerging in this field. In the chapter, we will look at the evolution of the N-tier approach, and at the challenges it presents to the programmer.
Chapter 3, Database Foundations. Complementing Chapter 16, this is a chapter that will stand you in good stead in your applications programming. This chapter is aimed at those who are moving from middle tier to database tier programming and who need to brush up on what goes on inside the database and how it works.
Chapter 4, Scaling your Applications with Net8. Communicating remotely to the Oracle 8i database introduces a new element: the fallibility and idiosyncrasies of networks. Net8 is the successor to Oracle's SQL*Net, and provides an integrated and homogenous networking solution for Oracle products. However good your application code, you will invariably spend time troubleshooting connectivity problems. This chapter will help you minimize that overhead and give you valuable insight into what is going on beneath the covers when you connect to Oracle. Chapter 5, Enterprise Development with Designer 6i. Any database application (which means any enterprise database solution) should consider using Oracle Designer and repository-based development to build a manageable and maintainable database infrastructure. This chapter will progress from designing the initial data model and translating it into a database design, to full generation of the database definition code. We'll build the foundations of a web application for an Education Center database, which we'll continue building in Chapter 10.
PL/SQL and the Web
Chapter 6, PL/SQL Development. This is where the real programming starts. Whatever programs we use to access the database, our calls will be ultimately executed in PL/SQL. We concentrate on the fine art of effective, well-defined and re-usable PL/SQL packages. The chapter contains advice on how to program effectively in a group development environment and we build a test framework for reporting on the status of any P/SQL development effort. This will prepare us for developing with the PL/SQL web toolkit in the remaining chapters of this section of the book.
Chapter 7, PL/SQL Server Pages (PSP). New to Oracle 8.1.6 is the PSP system for serving up entire web applications directly from the database. Using PL/SQL as a scripting language, we can write our PSP pages, compile them and upload them to the database. The pages are then accessed via any web server running a PL/SQL Gateway, such as Apache/mod_plsql on Oracle lAS.
Chapter 8, The PL/SQL Web Toolkit. This chapter covers everything you need to get coding with the PL/SQL Web Toolkit. We'll discuss how to exploit the reliability and scalability of the Oracle 8i database to deliver web content directly from stored PL/SQL procedures. We will develop a basic interactive web application for manipulating tabular information.
Chapter 9, A Stock Tracker using the PL/SQL Web Toolkit, XML and JavaScript. Som}~ of the elegance and power of the PL/SQL Web Toolkit will be explored in this case study.'We'll build a stock tracking application with a web front end, focusing on good design and clarity in the implementation. As well as showcasing the toolkit features, we'll show how it can work alongside other technologies such as XML, JavaScript and Cascading Style Sheets, to generate finished web applications.
Chapter 10, Web PL/SQL Application Development using Designer 6i. Whereas the previous chapter focused on hand coding of PL/SQL procedures, we look here at how Oracle Designer 6i can generate entire PL/SQL based web applications based on our design specifications. We will extend our Education Center example from Chapter 5 and show how the powerful Web PL/SQL Generator can enhance productivity and time-to-market for database-powered websites.Java
Chapter 11, Oracle JVM and Java Stored Procedures. Here we explain how PL/SQL andJava can work together. We look at the OracleJVM, relating the use of Java to familiar database notions. The chapter progresses from getting your first Java stored procedure uploaded and callable from PL/SQL, to using Oracle Advanced Queuing andJava stored procedures with
PL/SQL triggers.
Chapter 12, Oracle Access with JDBC.Java Database Connectivity is the industry standard for accessing SQL databases from Java. Oracle supportsJDBC with a number of Java database drivers and extendedJDBC features for access to Oracle databases. In this chapter we explore the use of these drivers, with a sequence of clearly worked examples.
Chapter 13, Connection Pooling and Caching. In this chapter, we build on Chapter 12, and move into the world of multiple concurrentJDBC access to the Oracle 8i database, from the middle-tier. We look at how we can reduce the burden on the database by pooling connection resources, instead of opening a new connection for each client. The chapter contains a complete example of a catalogue browser servlet, with middle-tier connection pooling using bothJDBC 1.0 and the newJDBC 2.0 specification, and returning the results from the Oracle database.
Chapter 14, Database Connectivity with SQLJ. Now that you have got to work onJDBC, you may have wondered whether there was any easier way of coding SQL intoJava programs. SQLJ provides an interesting and productive way of writing more concise and maintainable database access code, usingJDBC as the underlying connectivity technology. The chapter progresses through a number of clear examples, exploring the features of this ANSI standard, which is well supported, and has been enhanced, by Oracle.
Chapter 15, Enterprise JavaBeans and Oracle. You will almost certainly have heard of EJBs, but you may not have heard of EJBs running inside an Oracle database. This chapter will introduce you to EJBs from the point of view of theJ2EE specification, and then move on to show how we can get EJBs deployed in the Oracle 8i database. We will build a restaurant finder application with the searching and updating features coded into EJBs running in Oracle 8i.
Chapter 16, Inside the Database. This chapter will cover vital ground for both experienced database programmers, and those keen to move on to more advancedJava programming. We will review the importance of knowing how the Oracle 8i database works. We will show how you can tune your client applications to make the best use of database features, and how to avoid inefficient and incorrect code. The chapter will give an example of tracing the SQL generated by JDBC code, and discussing what steps can be taken to make best use of Oracle features.
Chapter 17, Business Components for Java. In this chapter we will bui~ a restaurant booking application with a substantial web interface. We'll use Oracle's BC4J development environment, built intoJDeveloper, to generate our application from scratch, modify it to our needs, and then deploy the business logic as an EJB to Oracle 8i. Then we will publish a JavaServer Pages front end to the Oracle HTTP Server, built into iAS.
Chapter 18, A Discussion Forum with PL/SQL and Java. We extend here the work we did in Chapter 13 on connection pooling. We'll build a discussion forum with aJavaServer Pages interface. The forum will use PL/SQL stored procedures for database operations. We'll use Java code both inside and outside the database. We'll call PL/SOL procedures from Java using JDBC. And this is not all! Although completely self-contained, the case study will be further extended in Chapter 20.
Chapter 19, An Overview of Oracle interMedia Text. Searching capability is a big issue in any enterprise application - the first thing many people do on reaching a website is to search for something. Searching is computationally intensive and therefore expensive. In this chapter we will study the elements of Oracle interMedia and the query language that is supported by this technology, as a way of collating information, creating indexes and optimizing searches.
Chapter 20, Building a Search Engine using Java and interMedia. We build on the previous two chapters, adding a search facility to the Discussion Forum developed in Chapter 18. Using interMedia, we index all messages in the forum archives, and we can then useJDBC through our connection pooling infrastructure, to get back search results from a wide range of search queries. We display the results using Java Server Pages.XML
Chapter 21, XML and Oracle. We explore the Oracle XML Developer's Toolkit. We will examine the Oracle Java DOM and SAX parsers that can be used by client or database Java. We will use XSLT transformation, and explore Oracle's notion of combining XML and SQL data access into a scripted page using the XML SQL Utility (XSU). We'll run the XSOL servlet to process these pages. The Oracle TransviewerJavaBeans provide components for building XML-enabledJava applications, and we'll have a look at those too. All of these XML features are demonstrated using worked examples.
Chapter 22, A Case Study in Enterprise Application Development. In this chapter we will put the material we reviewed in Chapter 2 into practice. We'll build an efficient and effective enterprise solution for an internal data centralization problem. We'll use XML as our representation language, XSL, JavaBeans, and a servlet on our web tier, and an EolB running in Oracle 8i.
Chapter23, Application Integration using SOAP. SOAP is a platform-neutral communications protocol based on XML. It is heavily supported by Apache and Microsoft and is rapidly developing into an important standard. This chapter will show how we can build connectivity between Oracle, Microsoft, Apache and other commercial products using SOAP, and we will work through a substantial case study. Though not directly supported by Oracle yet, this looks like being one to get familiar with now, because it has a big future.
Chapter 24, Wireless Applications with Oracle Portal-to-Go. As we move into the mobile age, there is more and more demand for diverse information sources to be available via wireless devices. In this chapter we will look at setting up the Portal-to-Go client and server environment, and porting various network resources, such as HTML and LDAP, into a personalized wireless portal. We will examine the integrating protocol, SimpleResult XML, which maps resources to a common format before delivery to individual device types.
Chapter 25, ASP, XML and Oracle. ASP is a Microsoft web page scripting language that is gaining a foothold on other platforms such as Solaris and Linux. We'll examine Microsoft's ActiveX Data Objects and how it works through Net8 to access Oracle 8i. We'll also look at how to use ASP with Oracle's XML SQL utility, to fetch and manipulate data in XML form.
Appendices
As well as an appendix covering installation issues, we have also provided two primers ill the appendices for those who are unfamiliar with, or need to brush up on, Java and XML:
Appendix A, Installations Issues. The appendix covers installing Oracle 8i and many of the tools used in the book.
Appendix B, Java Primer. Here we will help get you up to speed on Java. Anyone who is starting out in Java will appreciate the handy reference material and tutorials. Intermediate programmers might appreciate not having to carry another Java book around!
Appendix C, XML Primer. This appendix contains a refresher on XML for the reader who isn't familiar with basic XML concepts, or just needs to get back up to speed.
Appendix D, P2P, Support and Errata. Find out about Wrox Press support and the forums at P2P (Programmer to Programmer*U).
The Tools you Need for this Book
In this section we give you some advance warning of the software and tools you need to develop the applications in this book. You will need an Oracle 8i database. In order to obtain some reasonable performance from the database you will need a machine that exceeds certain minimum specifications (please see Appendix A for more details). The versions and editions of the database are covered in
Chapter 1. You will need development kits, such as Sun Microsystems'sJDK and Oracle's XDK. You will need a web server that can run servlets andJSPs. If you install Oracle lAS, then you will have an
Apache installation that can be used for all the web applications, including those of the Portal-to-Go chapter. Alternatively, you will need to install Apache. In several chapters we use the Tomcat web server. This can be configured to run with Apache (either standalone or under iAS). To develop the
BC4J application in Chapter 17 you will need OracleJDeveloper.
All Oracle software products, including Oracle 8i,JDeveloper, Oracle iAS and Oracle Portal-to-Go, can be downloaded from the Oracle Technology Network (OTN or sometimes referred to as Technet) at the website http://technet.oracle.com.
Please note: This does not mean that Oracle products, or other downloadable software kits, are free! Before you download anything, please read the licensing agreement very carefully. Please ensure that you do not break the licensing agreement for any software that you download.
Development Kits
JDK. For Oracle 8.1.5, you will probably want to be working withJDK 1.1.6. For Oracle 8.1.6 and above, you will need to work withJDK 1.2.1. These kits can be obtained from http://java.sun.com.
XDK. This product, along with several other XML packages used in the book can be downloaded from OTN at http://technet.oracle.com. Please see Appendix A for installation details.
PL/SQL Web Toolkit. This is bundled with iAS, but can also be downloaded separately from OTN.
Web and Application Servers
Apache. You can download the Apache web server from the Apache Foundation at
http://www.apache.org.
Tomcat. You can download the Tomcat web application server from the Jakarta Project at
http://jakarta.apache.org.
JBoss. ThisJ2EE open source application server is available from JBoss.org at
http://www.jboss.com. JBoss is used only in Chapter 23.
Tools
JDeveloper. This Java, XML, BC4J and Oracle-enabled Integrated Development Environment is available for download at OTN. We usedJDeveloper 3.1.1.2 for Chapter 17. You will also be able to useJDeveloper to explore most of the code fi*om this book (all chapters with Java), by downloading appropriate material from the Wrox website. Designer 6i. This application can also be downloaded from OTN.
Conventions
We have used a number of different styles of text and layout in this book to help differentiate between the different kinds of information. Here are examples of the styles we use and an explanation of what they mean:
Code has several fonts. If it's a word that we're talking about in the text, for example when discussing a
PL/SQL SELECT query, it's in this font. If it's a block of code that you can type as a program and run, then it's in a gray box: public void close () throws EJBException, RemoteException
Sometimes you'll see code in a mixture of styles, like this:
Widget
S10 . 00
In cases like this, the code with a white background is code we are already familiar with; the line highlighted in grey is a new addition to the code since we last looked at it.
Advice, hints, and background information comes in this type of font.
Important pieces of information come in boxes like this.
Bullets appear indented, with each new bullet marked as follows:
Important Words are in a bold type font
Words that appear on the screen, in menus like File or Window, are in a similar font to that you would see on a Windows desktop Keys that you press on the keyboard like Ctrl and Enter, are in italics
Customer Support
We've tried to make this book as accurate and enjoyable as possible, but what really matters is what the book actually does for you. Please let us know your views, either by returning the reply card in the back of the book, or by contacting us via email at leedback@wrox.com.
Source Code and Updates As you work through the examples in this book, you may decide that you prefer to type in all the code by hand. Many readers prefer this because it's a good way to get familiar with the coding techniques that are being used.
Whether you want to type the code in or not, we have made all the source code for this book available at our web site at the following address:
http://www.wrox.com/
If you're one of those readers who likes to type in the code, you can use our files to check the results you should be getting - they should be your first stop if you think you might have typed in an error. If you're one of those readers who doesn't like typing, then downloading the source code from our web site is a must!
Either way, it'll help you with updates and debugging.
Errata
We've made every effort to make sure that there are no errors in the text or the code. However, to err is human, and as such we recognize the need to keep you informed of any mistakes as they're spotted and corrected. Errata sheets are available for all our books at http://www.wrox.com. If you find an error that hasn't already been reported, please let us know.
Our web site acts as a focus for other information and support, including the code from all our books,sample chapters, previews of forthcoming titles, and articles and opinion on related topics.