Oracle Web Application Programming for PL/SQL Developers

Preface

Oracle Web Application Programming for PL/SQL Developers gives PL/SQL programmers the knowledge necessary to build powerful applications on the Web.

PL/SQL, together with the Oracle Internet Application Server (iAS), provides a programmer with the ability to truly develop an entire application, from control over complex data processing on the back end, to the creation of a standardized, professional look-and-feel on the front end.

A programmer familiar with PL/SQL, but not necessarily familiar with the Web, can make the leap to building dynamic Web applications using this book. This book brings together the core pieces of the puzzle of PL/SQL Web development in one volume. In addition to reviewing a few basic PL/SQL concepts with examples, the practicalities of building a Web site are covered, including HTML and JavaScript. The essentials of how a computer connects to the Internet are explained, along with how a Web server handles requests for Web pages, Web browsers, image handling, FTP, and some of the basic UNIX commands you need to get started developing PL/SQL Web applications.

As you read and complete the exercises, you apply what you learn by building an application of your own. You create Web pages, connect them to an Oracle database, and use the power of PL/SQL to create a dynamic, database-driven, Web front-end application. The application presented in this book includes PL/SQL procedures developed using both PL/SQL Web Toolkit and PSP approaches, so that you learn and evaluate different development strategies. This hands-on approach gives you the opportunity to address many of the day-to-day challenges that a PL/SQL Web developer faces. Once you create the application, you should find you are ready to apply your knowledge to other projects.

Throughout the book you will analyze code in detail and are offered practical advice on writing—and debugging—complex code. Anyone that has struggled with the realities of JavaScript validation when it is embedded in HTML and PL/SQL, or dealt with similar situations, can refer to this book for tips and examples.

So many different elements work together to create a Web page that coordinating them can easily become the greatest challenge faced by the PL/SQL Web developer. This book focuses, in particular, on clarifying these issues. Many of the questions that a Web developer would ask are answered in this book. For example: There are references available on PL/SQL, HTML, and JavaScript individually, but how do these elements interact? In cases where they can accomplish the same task, how do they compare? What are the limits of each?

Putting all these various elements together to make an Oracle Web Application is much like preparing tapas, a Spanish meal comprised of many different appetizer-sized dishes. The beauty of tapas is the way individual elements work together to create a complex and delicious meal. That complexity is also a characteristic of Web programming. To achieve the results you desire, you must combine your knowledge of many different areas, and how they interact. This can be a tricky and frustrating process, but ultimately very powerful, and ideally very satisfying as well.

Who This Book Is For

This book is intended for anyone who needs an introduction to Web application programming with Oracle's PL/SQL language.

Readers are expected to be familiar with Oracle's SQL and PL/SQL languages. This book assumes you have the Oracle knowledge that can be obtained from the companion books in this series: Oracle SQL Interactive Workbook by Alice Rischert (Prentice Hall), and Oracle PL/SQL Interactive Workbook by Ben Rosenzweig and Elena Silvestrova (Prentice Hall). The ideal readers are those with some experience with Oracle relational databases. This book reviews basic PL/SQL concepts, particularly those that are helpful in building the Web application you create by completing the exercises in this book.

Readers of this book are not required to know HTML or JavaScript. When the fundamentals of how to create a Web page are covered, basic HTML and some of the JavaScript used most frequently in Web applications are introduced. We let you know where you can find details on the more advanced HTML and JavaScript features.

The content of this book is based on the material taught in the Database Application Development and Design concentration of Columbia University's Computer Technology and Applications (CTA) program in New York City. The student body is rather diverse, in that there are some students who have years of experience with Information Technology (IT) and programming, but no experience with building a Web application and then there are those with absolutely no experience in either IT or programming. The content of the book, like the class, is balanced to meet the needs of both extremes. The exercises in this book can be used as a lab and homework to accompany the lectures in such a course on Web application development.

How This Book Is Organized

The intent of this workbook is to teach you about Oracle Web applications by presenting you with a series of exercises that builds towards a complete application. The book consists of three sections. The first section is an introduction to the Web, as well as key concepts and skills required to develop a Web application. The second section is called "Building the Application." In this section, each chapter walks you through the construction of procedures and Web pages that ultimately comprise a complete application by providing you with all the coding skills required. Chapters in this section cover HTML, JavaScript, PL/SQL, the PL/SQL Web Toolkit and PL/SQL Server Pages (PSP). The complete application specification and source code can be found on the book's companion Web site (http://www.phptr.com/boardman/). The last section is called "Tapas." This section goes a step beyond and introduces the reader to a number of other tools and utilities available to a developer creating a Web site based on PL/SQL code.

About the Companion Web Site

At the companion Web site (http://www.phptr.com/boardman/) you will find three very important items:

1. Files you will need before you begin completing the exercises in the workbook.
2. Specification for the application developed in Section II.
3. Complete source code for the application developed in Section II.

All of the exercises and questions are based on a sample database schema called STUDENT. The files required to create and install this STUDENT schema are downloadable from the Web site.

In addition to required files and application source code, the Web site will have other features like additional review questions, a message board and periodically updated information about and errata for the book.

You should visit the companion Web site to this book (www.phptr.com/boardman) to download the STUDENT schema and install it in your database.

What You Will Need

There are software programs, as well as knowledge requirements, necessary to complete the exercises in this book.

Software

• Oracle 9i or higher
• Oracle 9iAS
• SQL*Plus 8.1.7 or higher
• Internet Access to the World Wide Web

Oracle 9i Enterprise Edition and Oracle 9i Personal Edition include the Oracle 9i database and SQL*Plus, as well as a core version of the Oracle 9iAS. Further information about installing and configuring the 9i Application Server is provided in Chapter 2, "Oracle 9iAS."

There are various ways to obtain Oracle software. The educational version of this book comes with CDs containing the Oracle software for educational purposes. Oracle also offers trial versions of Oracle 9i for evaluation purposes. These CDs can be purchased at the Oracle Store (http://oraclestore.oracle.com).

Readers are encouraged to join the Oracle Technology Network, or Technet, (http://technet.oracle.com) Oracle's free community for sharing technical information. There is no fee to register with TechNet and some software is available for free download from TechNet for registered users. All Oracle documentation can also be found at Oracle's document site (http://docs.oracle.com).

USING SQL*Plus

You should be familiar with using SQL*Plus to execute SQL statements (refer to the other book in the Prentice Hall Interactive Workbook series on this topic, Alice Rischert's Oracle SQL Interactive Workbook). You should also be familiar with compiling PL/SQL programs (refer to Rosenzweig/Silvestrova's Oracle PL/SQL Interactive Workbook).

BUILD AN APPLICATION

As you progress through the exercises in this book, you will build your own Oracle Web application. It serves as a helpful, real-world example and a context for the material covered. Employing a hands-on approach is an excellent way to truly learn new material. To gain the maximum benefit this book has to offer, readers are strongly urged to follow along with the exercises. Once you create the application, you should find you are ready to apply your knowledge to other projects.

STUDENT SCHEMA

The STUDENT schema (Appendix A) contains tables and other objects meant to keep information about a registration and enrollment system for a fictitious university. There are tables in the system that store data about students, courses, instructors, and so on. In addition to storing contact information (addresses and telephone numbers) for students and instructors, and descriptive information about courses (costs and prerequisites), the schema also keeps track of the sections for particular courses, as well as the sections in which students have enrolled.

The section and enrollment tables are two of the most important tables in the schema. The section table stores data about the individual sections that have been created for each course. Each section record also stores information about where and when the section will meet, and which instructor will teach the section. The section table is related to the course table and the instructor table.

The enrollment table is equally important, because it keeps track of which students have enrolled in which sections. Each enrollment record also stores information about the student's grade and enrollment date. The enrollment table is related to the student table and the section table.

Furthermore, the schema contains a number of other tables that manage grading for each student in each section.