About this book

Teach
Yourself
Oracle8^‰
Database
Development
in 21 days

Teach Yourself

Oracle8^‰
Database
Development
in 21 days

David Lockman

201 West 103rd Street
Indianapolis, Indiana 46290

Copyright © 1997 by Sams Publishing

FIRST EDITION

All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permission from the publisher. No patent liability is assumed with respect to the use of the information contained herein. Although every precaution has been taken in the preparation of this book, the publisher and author assume no responsibility for errors or omissions. Neither is any liability assumed for damages resulting from the use of the information contained herein. For information, address Sams Publishing, 201 W. 103rd St., Indianapolis, IN 46290.

International Standard Book Number: 0-672-31078-3

Library of Congress Catalog Card Number: 97-65457

2000 99 98 97 4 3 2 1

Interpretation of the printing code: the rightmost double-digit number is the year of the book's printing; the rightmost single-digit, the number of the book's printing. For example, a printing code of 97-1 shows that the first printing of the book occurred in 1997.

Composed in AGaramond and MCPdigital by Macmillan Computer Publishing

Printed in the United States of America

Trademarks

All terms mentioned in this book that are known to be trademarks or service marks have been appropriately capitalized. Sams Publishing cannot attest to the accuracy of this information. Use of a term in this book should not be regarded as affecting the validity of any trademark or service mark. Oracle8 is a trademark, and Oracle is a registered trademark of Oracle Corporation.

Acquisitions Editor

Steve Straiger

Development Editor

Marla Reece

Software Development Specialist

John Warriner

Production Editors

Deborah Frisby
Kristi Hart

Copy Editors

Margo Catts
Howard Jones
Bonnie Lawler

Indexer

Benjamin Slen

Technical Reviewers

Byron Pearce
Kelly Leigh

Editorial Coordinators

Mandi Rowell
Katie Wise

Technical Edit Coordinator

Lorraine E. Schaffer

Resource Coordinator

Deborah Frisby

Editorial Assistants

Carol Ackerman
Andi Richter
Rhonda Tinch-Mize

Cover Designer

Tim Amrhein

Book Designer

Gary Adair

Copy Writer

David Reichwein

Production Team Supervisors

Brad Chinn
Charlotte Clapp

Production

Jeanne Clark
Lana Dominguez
Shawn Ring
Becky Stutzman

President, Sams PublishingRichard K. Swadley

Publishing ManagerRosemarie Graham

Director of MarketingKelli S. Spencer

Product Marketing ManagerWendy Gilbride

Assistant Marketing ManagersJennifer Pock, Rachel Wolfe

Acknowledgments

Without the help of many people at Sams Publishing, I would not have been able to complete this book. Many thanks to Steve Straiger, acquisitions editor; Marla Reece, development editor; Kristi Hart, production editor, and Byron Pearce, technical editor.

I would also like to acknowledge some special colleagues: David Lerner, Anita Talbot, and George Krestyn.

Special thanks to Barry Cooper for keeping me informed about the progress of the Lakers during the season while I was too busy to pay attention.

To my family, what can I say? Thank you for not questioning my sanity during the past few months. Joanne, thank you for teaching Bo to sing while I was busy. And to Josh, Michael, and Daniel: I promise that we'll be out fishing for yellowtail and bonito in the near future.

Tell Us What You Think!

As a reader, you are the most important critic and commentator of our books. We value your opinion and want to know what we're doing right, what we could do better, what areas you'd like to see us publish in, and any other words of wisdom you're willing to pass our way. You can help us make strong books that meet your needs and give you the computer guidance you require.

Do you have access to CompuServe or the World Wide Web? Then check out our CompuServe forum by typing GO SAMS at any prompt. If you prefer the World Wide Web, check out our site at http://www.mcp.com.

As the team leader of the group that created this book, I welcome your comments. You can fax, e-mail, or write me directly to let me know what you did or didn't like about this book—as well as what we can do to make our books stronger. Here's the information:

At A Glance

During Week 1, you'll learn about many fundamental concepts that are used throughout the book. In fact, the first four days of the week focus on concepts. Beginning on Day 4, you'll begin to learn the specifics of using the Oracle database. Here's a day-by-day summary of Week 1.

• Day 1, "Exploring the World of Relational Databases"
• This lesson discusses the historical development of the relational database. You'll be introduced to some of the products developed by Oracle Corporation. You'll also learn about the architecture of client/server computing.
• Day 2, "Guidelines for Developing an Oracle Application"
• The focus of this lesson is on the standard methodology used to develop an Oracle application. In addition to technical issues, the lesson also addresses organizational questions.

5


6


7

• Day 3, "Logical Database Design"
• In this lesson, you will learn about the theory behind relational databases. The lesson also discusses the database design for the sample database that is discussed throughout the book.
• Day 4, "Implementing Your Logical Model: Physical Database Design"
• Using what you learned during Day 3, this lesson discusses how to construct an Oracle database. You'll learn how to create tables, indexes, and other database objects.
• Day 5, "Introduction to Structured Query Language (SQL)"
• The focus of this lesson is the use of SQL in retrieving data from an Oracle database.
• Day 6, "Using SQL to Modify Data"
• In this lesson, you'll learn how to use SQL to modify the contents of a table.
• Day 7, "Taking Advantage of SQL Built-In Functions"
• This lesson discusses many of the built-in functions that can be used in an SQL statement. You'll see many examples of string, numeric, and other functions that provide tremendous flexibility.

At A Glance

During Week 2, you'll learn more about SQL, including the use of built-in functions. The use of PL/SQL, Oracle's procedural language extension to SQL, is discussed during this week as well. Finally, the last three lessons of the week address Oracle Forms, one of the components of Developer/2000. Here's a day-by-day summary of Week 2.

• Day 8, "More Sophisticated Queries with SQL"
• In this lesson, you'll learn about retrieving information from two or more related tables, grouping rows by column, and other set operations supported by SQL.
• Day 9, "Programming an Oracle Database with PL/SQL"
• This is the first of three lessons that focus on the use of
• PL/SQL, Oracle's programmatic extension to SQL. You'll learn about the basic elements of a PL/SQL subprogram and some of the flow-of-control statements that are commonly used in
• PL/SQL.

• Day 10, "Program Development in PL/SQL"
• Continuing from the previous day, this lesson discusses additional PL/SQL datatypes, such as %TYPE and %ROWTYPE. You'll also learn how to create a PL/SQL package.
• Day 11, "More Programming Techniques with PL/SQL"
• Many important topics are addressed in this lesson, including the use of cursors in retrieving rows, using PL/SQL in database triggers, and exception handling.
• Day 12, "Developer/2000: Introduction to Oracle Forms"
• This is the first of five lessons that discuss the components of Developer/2000. Three of these lessons—Days 12, 13, and 14—focus on Oracle Forms.
• Day 13, "Devloper/2000: Developing a User Interface with Oracle Forms"
• In another lesson on Oracle Forms, you'll learn how to build a master-detail form, change the appearance of a form, and set the properties for form objects.
• Day 14, "Developer/2000: Application Development with Oracle Forms"
• This is the last lesson on Oracle Forms. This lesson covers the use of Oracle Forms triggers to validate user input, the use of menus in a forms application, and other topics.

At A Glance

This is your final week, excluding the two Bonus Days that follow it. During Week 3, you'll continue to learn about the components of Developer/2000—Oracle Reports, Oracle Graphics, and Procedure Builder. During this week, two of the lessons focus on Oracle Power Objects. Here's a day-by-day summary of Week 3.

• Day 15, "Developer/2000: Developing Reports with
• Oracle Reports"
• In this lesson, you'll learn about another component of Developer/2000: Oracle Reports. Step by step, you'll construct a simple report. You'll also learn about the various objects that compose an Oracle Report.

19


20


21

• Day 16, "Developer/2000: Using Oracle Graphics and Procedure Builder"
• This lesson focuses on the other two components of Developer/2000: Oracle Graphics and Procedure Builder. You'll learn how to create a chart that reflects the values contained in a table. You'll also learn how to use Procedure Builder to construct a database trigger.
• Day 17, "Introduction to Oracle Power Objects"
• This is the first of two lessons that talk about Power Objects. You'll gain a basic understanding of the properties used to create a simple form.
• Day 18, "Developing an Application with Oracle Power Objects"
• Many important topics are addressed in this lesson, including using method code to validate input, creating a new class, inheritance, and report construction.
• Day 19, "An Overview of Oracle Database Security and Tuning"
• In this lesson, you'll learn about Oracle users, roles, and privileges and how to manage them. The lesson briefly discusses the issue of performance tuning.
• Day 20, "Using Oracle Database Designer and Oracle Designer/2000 in Application Development"
• During this lesson, you'll learn about a fairly new Oracle tool, Database Designer, and its use in the application development process. This lesson also presents Designer/2000 and some of its components.
• Day 21, "Oracle: The Next Generation"
• In the last lesson for the week, you'll learn about the Oracle Web Application Server, including two special packages that are used to generate HTML tags from PL/SQL subprograms. You'll learn about the architecture and configuration of Developer/2000 for the Web. The lesson concludes with a discussion of Oracle's Network Computing Architecture.