Table of Contents | Next

developer.com - Reference Library

Page 1

Introduction

The idea for this book first occurred to me in the summer of 1996. I was searching for a textbook to use in an Oracle database development course I planned to teach in the fall at Iowa Western Community College (IWCC). This course would be the first regular semester Oracle database development course taught at an academic institution in the Omaha area, or in the Midwest as far as I knew. Of all the books available on Oracle system development, I couldn't find one that covered the areas I felt were necessary. I searched the local bookstores, the Internet, and talked to other Oracle developers. I finally found a book that covered the various Oracle products I was interested in, but the book was so general that it could not be used as a reference for student homework or doing real work in the workplace. I used the book, but was forced to prepare and distribute a large amount of supplementary material to my students. My students found the supplements extremely useful, but commented on the ineffectiveness of the book. The extra work and my students' complaints caused me to continue my search.n

Page 2

I've been developing Oracle systems for the past 10 years. During that period I've amassed many books and training manuals about the various Oracle products I use. Whenever I have a problem with PL/SQL syntax, or have trigger errors, or a question on data format, or myriad other problems, I need to search a number of books. Unfortunately, the books are usually on someone else's desk. Most of my reference books are instruction books purchased directly from Oracle. They mainly consist of the Help screen in paperbound form. Since these sets cost several hundred dollars each and are not user-friendly, I have been reluctant to buy a set for each of my employees. For some time, I've wanted a relatively low-cost reference manual that covers the proper areas of development and database commands.

With these two problems in mind and no solution in the marketplace, I decided to write my own book. This book would cover the development of an Oracle database from initial design to Web enabling the applications. I approached Que with my idea of developing a book that would cover the flagship Oracle database and system development products. The products would include the Oracle7 database (Oracle Server) and Oracle's Developer 2000 development tools.

More specifically, the book would also thoroughly cover SQL*PLUS. This product executes commands and instructions against the Oracle7 or Oracle 8 database. The book also would cover PL/SQL, which is Oracle's procedure language. This language is used in most of the other Oracle products to perform specialized functions. Another product the book would cover was SQL*LOADER, which is Oracle's product to load data into the database for ASCII files. A major portion of the book is coverage of Oracle Forms 4.5. This product is Oracle's main user interface development tool. It is used to develop graphic user interface (GUI) forms. Finally, the book would cover Oracle Reports 2.5, which is Oracle's report writer.

Que bought the idea and you hold the result in your hands. This book, as did my IWCC course, targets several groups, from novice to advanced users. I want the novice to be able to gain the skills required to successfully develop a relational database and applications. The book will also enable the experienced developer new to Oracle to acquire the skills necessary to develop systems by using the Oracle products. Finally, I want the book to serve as the single-source reference book for all areas of Oracle development. I want the book to be the source that experienced Oracle developers, such as myself or members of my staff, use in the future.

If you're a novice developer, you'll find that this book is strong in the concepts and techniques of database and application design. Each chapter presents concepts you need to develop a database. At the end of each chapter are problems for you to solve that help you practice the skills learned in the chapter. You also have the opportunity to develop an employee database from scratch. At the end of each Part, you'll be given a series of tasks to perform. The results of the tasks will be a completed system, which can be used as a prototype for other systems you may develop. I identified the template for this system 10 years ago. I've used this design in every system I've developed since then.

The book also contains much reference material. I identified the areas in which I often sought technical specifications, and then included those materials in the book. They should come in handy for the experienced developer. In fact, I used completed portions of this book as a

Page 3

source in my work before it was published. The book may not be the definitive source for obscure areas of the Oracle database, but I believe experienced programmers will find it a good source of information to keep close to their computers.

Finally, I've developed a number of systems in the past 12 years. In these projects, I have made a lot of mistakes and identified what users seem to like about a system. Interspersed throughout the book are discussions of the various problems I've encountered and system development techniques I've learned over the years. I've tried to include some of these personal observations and sincerely hope these add a special element to the book.

How This Book Is Organized

The book is organized in parts and each part covers a specific area of the database development cycle or specific Oracle tools. The order of the parts follows the normal development cycle of the database. At the end of each chapter are problems you can use to practice the techniques discussed. The answers to the problems are in Appendix E, "Answers to Practice Problems," and on the CD-ROM enclosed with the book. In addition, throughout the book, you'll see the various steps needed to develop an employee database system you can use as a template for other systems. At the end of each part, you'll find a series of tasks you need to complete this database. The completion of the tasks will bring you one step closer to developing the book's template system. The answers to the tasks are in Chapter 20, "Developing a Template Employee Information System." Finally, the book has appendixes that contain information on triggers, system variables, and built-in functions.

The following parts and chapters are included in this book:

Part I: Designing Your Database Objects

This part introduces you to the Oracle Corporation and its various products. You learn about the major relational database objects such as tablespaces, tables, indexes, views, and database design concepts and techniques such as data normalization. This part includes Chapter 1, "Introduction to Oracle Tools," Chapter 2, "Designing Your Database," and Chapter 3, "Acquiring Data by Using the Select Statement."

At the end of Part I are two employee project tasks. The first asks you to identify the data elements used in the database and their attributes, and the second asks you to normalize the data and document the tables in a table relationship diagram.

Part II: Using SQL*PLUS to Access the Database

This part discusses Oracle's SQL *PLUS product, which interfaces with the Oracle7 and Oracle 8 database products. The first several chapters cover the various aspects of the SELECT statement, which is used to acquire and display data from the database. You learn about the data definition language (DDL) statements used to create, alter, and delete database objects such as tables. The part also shows data manipulation (DML) commands used to add, update, and delete records. Finally, the part describes how SQL*PLUS acquires data and produces formatted reports. This part includes Chapter 4, "Formatting Reports in SQL*PLUS," Chapter

Page 4

5, "Combining Tables and Rows," Chapter 6, "Defining the Database Objects," and Chapter 7, "Modifying Your Tables with DML Commands."

The project tasks at the end of Part II set up the employee database objects. This includes creating the tablespace, tables, and indexes, and creating a user account and granting privileges.

Part III: PL/SQL Oracle's Procedure Language

This part covers PL/SQL Oracle's procedure language. PL/SQL is used in SQL*PLUS to modify tables. It is used by SQL Forms to control form behavior and process form data. It is also used by Oracle Reports to compute values. The final portion of PART III covers the SQL*LOADER, which loads ASCII text files into an Oracle table. This part includes Chapter 8, "Creating Your First PL/SQL Program," Chapter 9, "Increasing the Power of Your PL/SQL Blocks," and Chapter 10, "Using SQL*LOADER."

At the end of Part III, you are asked to load the tables created in Part II with data by using SQL*LOADER.

Part IV: Oracle's User Interface (Oracle Forms 4.5)

Part IV covers all of the components of the Oracle Forms 4.5 application development product, which develops forms or screens used to add, update, delete, and view database records. This part include Chapter 11, "Using an Oracle Form," Chapter 12, "Using Object Navigator to Create Your First Oracle Form," Chapter 13, "Formatting Your Form," Chapter 14, "Creating and Modifying Master-Detail Forms," Chapter 15, "Using Triggers and Modifying Classes," and Chapter 16, "Calling Other Forms and Using Menus."

The project tasks at the end of the part ask you to develop a security update application, employee update application, department update application, employee directory, and a system menu.

Part V: Oracle's Report Writer: Oracle Reports 2.5

Part V covers the Oracle Reports product, which is a powerful report writer used to develop highly formatted reports. This part includes Chapter 17, "Creating Your First Report," and Chapter 18, "Customizing Reports and Advanced Report Functions."

The project task asks you to develop an employee report and integrate it into the applications developed in Part IV.

Part VI: Moving Your Application to the Web

This Part covers Chapter 19, "Moving Oracle Applications to the Web," which discusses the Layout Editor and its menu options, tools, and object properties. It also discusses reports and summary and formula columns.

Page 5

Part VII: Complete Template System

This part covers Chapter 20, "Developing a Template Employee Information System," which discusses designing a normalized relational database, creating employee system database objects, loading database tables, designing forms menus, and designing various reports.

Appendixes

The book has five appendixes that cover the following topics: Appendix A, "Built-In Subprograms," discusses procedures developed for use in Oracle Forms. The procedures perform functions such as navigating to the next record. Appendix B, "Triggers," covers tools that cause Oracle Forms to execute PL/SQL statements at special times such as before inserting a record into the database. Appendix C, "System Variables," covers variables used to monitor and control Runtime Forms. Appendix D, "Practice Database Installation Instructions," covers the procedures to install the Oracle database used for the book's examples and exercises. Appendix E, "Answers to the Practice Problems," contains the answers to the exercises at the end of each chapter.l

Page 6

Contents

Contents at a Glance

I Designing Your Database Objects
  1. Introduction to Oracle Tools9
  2. Designing Your Database15
  3. Acquiring Data by Using the Select Statement37
II Using SQL*PLUS to Access the Database
  1. Formatting Reports in SQL *PLUS83
  2. Combining Tables and Rows103
  3. Defining the Database Objects119
  4. Modifying Your Tables with DML Commands151
III PL/SQL Oracle's Procedure Language
  1. Creating Your First PL/SQL Program165
  2. Increasing the Power of Your PL/SQL Blocks189
  3. Using SQL*LOADER221
IV Oracle's User Interface (Oracle Forms 4.5)
  1. Using an Oracle Form249
  2. Using the Object Navigator to Create Your First Oracle Form265
  3. Formatting Your Form295
  4. Creating and Modifying Master-Detail Forms337
  5. Using Triggers and Modifying Classes381
  6. Calling Other Forms and Using Menus411
V Oracle's Report Writer: Oracle Reports 2.5
  1. Creating Your First Report445
  2. Customizing Reports and Advanced Report Functions475
VI Moving Your Application to the Web
  1. Moving Oracle Applications to the Web503

Page 6

VII Complete Templates System
  1. Developing a Template Employee Information System525
Appendixes

A Built-In Subprograms555

B Triggers579

C System Variables597

D Practice Database Installation Instructons601

E Answers to Practice Problems607


Index635

Page 7

Table of Contents

Introduction1

How This Book Is Organized 3

Part I: Designing Your Database Objects 3

Part II: Using SQL*PLUS to Access the Database 3

Part III: PL/SQL Oracle's Procedure Language 4

Part IV: Oracle's User Interface (Oracle Forms 4.5) 4

Part V: Oracle's Report Writer: Oracle Reports 2.54

Part VI: Moving Your Application to the Web 4

Part VII: Complete Template System 5

Appendixes 5

I Designing Your Database Objects
1 Introduction to Oracle Tools9

Oracle the Company10

Oracle7—Oracle's Database Product10

Oracle8—The Next Generation 11

SQL*PLUS—Oracle's Database Language 12

Other Oracle Products 12

Designer 2000 12

Personal Oracle7 12

Conventions and Techniques Used Throughout
this Book 12

Summary 13

2 Designing Your Database 15

What Is a Relational Database? 16

Database Components 18

Tablespaces 19

Tables and Fields 20

Indexes 21

Views 22

Synonyms 23

Grants and Roles 23


Page 8

Relational Database Design 24

Database Terminology 24

Documenting the Design of the Database 26

Database Tables 27

Normalizing Your Data 29

From Here… 32

Review Exercises 32

3 Acquiring Data by Using the Select Statement 37

Logging On SQL*PLUS38

The SQL*PLUS Editor39

Understanding the Select Command Format41

The Select Clause 42

The From Clause 42

The Where Clause 42

The Group By Clause 42

The Having Clause 43

The Order By Clause 43

Simple Select 43

Ordering Records 44

Conditional Select Statements 45

Equal Sign (=) 46

Inequality Sign (<> or !=) 47

Greater Than (>) and Less Than (<)48

Using the IN Operator 49

Using the BETWEEN Operator 49

Using the LIKE Operator 50

Using the NOT Operator 51

Understanding Multiple Conditions 51

Using Variables 53

Performing Column Arithmetic and Combination 54

Understanding Null Values and Expressions 56

Date Arithmetic 56

Using Character Functions 58

The Length Function 59

The Lpad Function 60

The Ltrim Function 60


Page 9

The Rpad Function 61

The Rtrim Function 61

The Substr Function 62

Using the Decode Function 62

Using Number Functions 63

The Nvl Function 64

The Round Function 65

The Trunc Function 65

Understanding Date Functions 66

The Add_Months Function 67

The Months_Between Function 67

The Next_Day Function 68

Using Date Pictures 68

Understanding Group Functions 71

The Average Function 72

The COUNT Function 72

Multiple Group Functions 73

Understanding GROUP BY Functions 73

Understanding the HAVING Clause 75

Understanding Subselects or Subqueries 75

Understanding Pseudo Columns, Dual Table, Describe, and

Tab Table 77

Summary 79

From Here… 79

Review Questions 79

II Using SQL*PLUS to Access the Database
4 Formatting Reports in SQL*PLUS 83

Creating Report Titles 84

Creating Report Footers 85

Formatting Report Columns 86

Formatting Numbers 90

Defining Page Breaks 91

Defining Computes 94


Page 10

Setting Up the Environment 96

Summary 100

From Here… 100

Review Questions 100

5 Combining Tables and Rows 103

Combining Data in Relational versus Object Database 104

Qualifying the Columns106

Working with Multiple Joins107

Unexpected Results with Joining Tables in a One-
to-Many Relationship 108

Inaccurate Calculations Caused by a Table Join 109

Problems that Can Occur when the Value Exists in Only One of the Joined Tables 109

Performance Problems Caused by Nonmatching Data

Formats 110

Outer Joins 110

Set Operators 112

Unions 112

Minus 114

Intersect114

Combining Data with Oracle8 115

Summary 116

From Here… 116

Review Questions 116

6 Defining the Database Objects 119

Defining the Table Items 120

Creating, Altering, and Dropping Tables 122

Altering the Table 124

Defining Table Constraints 125

The Check Constraint 126

The Default option 126


Page 11

The Foreign Key Constraint 127

The Not Null Constraint 128

The Unique Constraint 129

The Primary Key Constraint 129

Disabling Constraints 130

Defining the Index 131

Creating and Dropping Views132

Creating Synonyms 134

Creating Database Links 135

Creating User Accounts 135

Creating Database Privileges 136

Granting Access to Your Tables 140

Creating the Tablespace 142

Defining a Sequence 143

Defining Database Triggers 144

Oracle8 Database Objects 145

Altering the Partitions 146

Partitioned Indexes 146

Creating Types 147

Using Varray 148

Summary 148

From Here… 149

Review Exercises 149

EMPLOYEE DATABASE PROJECT INSTALLMENT 2 150

7 Modifying Your Tables with DML Commands 151

Adding Records to Your Tables 152

Updating Items in Your Tables 153

Deleting Records from Your Table 156

Saving the Changes to Your Tables 159

Summary 160

From Here… 160

Review Exercises 161


Page 12

III PL/SQL Oracle's Procedure Language
8 Creating Your First PL/SQL Program165

Understanding PL/SQL166

Understanding the PL/SQL Blocks 166

Nested Blocks and Block Labels 168

Defining Your PL/SQL Variables 169

Using the %Type and %Rowtype 170

Assigning Values to the Variables 171

Using If-Then-Else Logic 172

Using Nested If Statements 174

Using Elseif in Your If Construct 175

Using Loops and Exits 176

Understanding PL/SQL Cursors 178

Controlling Your Cursors with Cursor Commands 179

Identifying Exceptions 181

Logging and Displaying Your Errors 183

Renaming Standard Errors with Your Own Names 184

Using the Exception Section for Your Own Exceptions 185

Summary 187

From Here… 187

Review Exercises 187

9 Increasing the Power of Your PL/SQL Blocks189

Using Cursor Attributes 190

Using the %found Attribute 190

Using the %rowcount Attribute 192

Using the %isopen Attribute 193

Using Explicit and Implicit Cursors 193

Using the For Loop 196

Creating and Using the Numeric For Loop 196

Creating and Using the Cursor For Loop 198

Using the For Update Of 202

Using the Where Current Of Option 203


Page 13

Creating and Using Named Procedures 204

Creating and Using Functions 206

Creating and Using Packages 210

Package Structure 210

Creating a Package Specification 212

Creating a Package Body 213

Calling Package Objects from Your Applications 214

Summary 217

From Here… 218

Review Exercises 218

10 Using SQL*LOADER 221

Understanding SQL*LOADER 222

How Do You Start Executing SQL*LOADER? 223

Using the Various Load Files 227

Using the Log File 228

Using the Bad File 230

Using the Discard File 231

Defining Control Files 231

Defining a Simple Control File 232

Defining the Load Style 233

Defining the Data Format 234

Defining Condition Logic in Your Control Files 235

Loading Multiple Tables Using the Same Load Procedure 236

Other Control File Keywords and Clauses 238

The Options and Recoverable/Unrecoverable Clauses 238

The Into Table Clause 239

The Concatenation and Continueif Clauses 241

Executing SQL*LOADER from the Command Line 243

Summary 244

From Here... 245

Review Exercises 245


Page 14

IV Oracle's User Interface (Oracle Forms 4.5)
11 Using an Oracle Form 249

What Is a Form? 250

A Form Example 250

Form Terminology 251

Developer 2000 Oracle Forms Applications 252

Launching a Form by Using Forms Runtime 254

Form Operating Modes 256

The Form Input Mode 256

The Form Query Mode 256

The Form Change Mode 257

Using Function Keys with an Active Form 258

Querying a Record on an Active Form 258

Retrieving Multiple Records with a Query 260

Other Form Procedures 261

Placing the Form in the Input Mode 261

Saving Changes 262

Displaying Errors 262

Summary 263

From Here… 264

Review Exercises 264

12 Using the Object Navigator to Create Your First Oracle Form 265

Launching Forms Designer 266

Forms Designer's Initial Screen 266

Using the Object Navigator 267

Expanding and Collapsing Objects 268

Defining Form Objects on the Object Navigator 268

The Object Navigator Tool Palette 270

Designer Menu Options 272

File Menu Options 272

Edit Menu Options 277

The Tools Menu Options 280

The Navigator Menu Options 285


Page 15

Creating Your First Form 288

Steps for Creating a Simple One-Block Form 288

Creating the Form 288

Summary 293

From Here...294

Review Exercises 294

13 Formatting Your Form 295

The Layout Editor 296

The Layout Edit Menu Bar 297

The View Pull-Down Menu 297

Using the Format Pull-Down Menu 305

Using the Arrange Pull-Down Menu 312

Using the Layout Editor Tool Palette 316

The Select Tool 317

The Rotate Tool 317

The Reshape tool 318

Graphic Shape Tools 318

Item Tools 319

Object Coloring Tools 321

Canvas Properties 323

Opening the Canvas Property Sheet 324

The Canvas Property Sheet 324

Reviewing and Setting Canvas Properties 327

Defining Window Properties 328

Formatting the Employee Form 331

Setting the Canvas and Window Properties 331

Summary 336

From Here... 336

Review Exercises 336

14 Creating and Modifying Master-Detail Forms 337

Adding Blocks to the Employee Update Form 338

Creating the Tools Detail Block 338

Block Coordination Objects and Settings 339

Formatting the Tools Block 341


Page 16

Creating the Glasses Block 342

Understanding Form Properties 343

The Coordinate Information Property 346

Using Current Record Attribute 347

Defining a Visual Attribute 348

Understanding Block Properties 349

Using the Order By and Where Clause Block Properties 351

Changing the Navigation Style 352

Understanding Item Properties 353

Using the Multi-Line Property 360

A Word on Some of the Useful Text Item Properties 361

Understanding Item Type s363

Check Boxes 363

Radio Group Buttons 364

List Items 366

Using Images 368

Creating List of Values 371

Calling the LOV 376

Displaying the LOV With a Button 376

Modifying the Record Group 378

Understanding Relation Properties 378

Summary 379

From Here... 380

Review Exercises 380

15 Using Triggers and Modifying Classes 381

What is a Trigger? 382

Trigger Events 382

Trigger Names 383

Trigger Failures 383

Trigger Levels 384

Creating a Trigger 384

Using the PL/SQL Editor 386

Using Built-in Subprograms 387

Using System and Global Variables 389


Page 17

Using Triggers and Program Units to Check User Security 391

Form Security Triggers 391

Using Form Procedures 393

Adding and Populating Non-Base Table Text Items 395

Using Alerts 398

Using Triggers To Change Form Canvases 401

Using Property Classes 405

Using the Property Sheet Tools 406

Creating, Modifying, and Using Property Classes 407

Viewing Two Property Sheets at the Same Time 408

Using the Intersection/Union Tool 409

Summary 409

From Here... 410

Review Exercises 410

16 Calling Other Forms and Using Menus 411

Calling Forms 412

Using call_form 412

Using new_form 413

Using the call_form Subprogram with the hide and no_hide

Options Specified 414

Creating and Using a Directory 416

Creating a Directory 416

Creating the Employee Directory 418

Operating the Employee Directory 422

Calling Other Forms from the Employee Directory 422

Creating and Using Menus to Combine Applications into a
System 426

Creating a Menu 426

Creating and Implementing an Employee System Menu429

Developing a System Startup Application 432

Creating a Toolbar 436

Summary 441

From Here… 441


Page 18

V Oracle's Report Writer: Oracle Reports 2.5
17 Creating Your First Report 445

Launching Oracle Reports 2.5446

Launching Reports Runtime Without Specifying the Report Name 446

Executing a Report From a Form 448

Launching Reports Designer 450

Creating Your First Report 450

Steps to Create a Simple Report 450

Creating an Employee Listing 451

Running the Employee Listing 456

The Designer Menu Options 458

File, Edit, and Navigator Menu Options 458

Examining the Object Navigator 464

Object Navigator Objects 465

Object Navigator Tool Bar 469

Understanding the Data Model 469

Understanding Data Model Editor Tools 471

Summary 472

From Here...473

Review Exercises 473

18 Customizing Reports and Advanced Report Functions 475

Description of Layout Editor Objects 476

Layout Editor Menu Options 477

Edit Menu Options 477

View Menu Options 478

Format Menu Options 480

Arrange Menu Options 481

Layout Editor Tools 483

The Toolbar 483

The Tool Palette 484

Layout Object Properties 485

Common Tabs 485

Special Tabs 488


Page 19

Making a Report Presentable 490

Changing the Fonts of Different Objects 490

Changing the Text of the Column Headings 490

Removing the Dotted Line or Changing the Line 490

Adding a Report Title 490

Adding Page Numbers or a Report Date 490

Changing the Positioning of Fields 491

Formatting Fields and Resizing Fields 491

Add Header or Trailer Pages 491

Creating a Report Layout from Scratch 492

Adding a Repeating Frame 493

Adding Fields to the Layout 493

Adding Boilerplate Text to a Layout 494

Perfecting the Layout 495

Adding Formula and Summary Columns496

Summary Columns 496

Formula Columns 497

Summary 498

From Here… 498

Review Exercises 499

VI Moving Your Application to the Web
19 Moving Oracle Applications to the Web 503

How Does the Web Implementation Work?504

The Three-Tiered Web Approach Described 504

Advantages of the Web Approach 504

Required Software and Where It May Be Installed505

Software for the Front-End 505

Software for the Middle or Application Server 506

Software for the Back-End or Database Server 506

Software Configuration Used for This Article 506

Setting Up Oracle Forms on the Web 507

Cartridge versus Non-Cartridge Implementation 507

Setup Common to Both Implementations 508

Cartridge Implementation Requirements 510


Page 20

Non-Cartridge Implementation Requirements 516

Running the Forms from the Web 516

Setting Up Oracle Reports on the Web 518

Configuring the Web Server for Web Reports 518

Running the Reports 520

Summary 522

From Here… 522

VII Complete Template System
20 Developing a Template Employee Information System 525

Installment 1—Data Normalization and Database Design 526

Installment 2—Creating the Database Objects 529

Installment 3—Loading the Data into the Tables 534

Installment 4—Designing and Building the User Interface 540

The Department Update Form (Deptupd.fmb) 540

The Employee Update Form (Empupd.fmb) 541

The Employee Directory Form (Empdir.fmb) 546

The Security Update Form (Empsec.fmb) 546

The Employee System Startup Form (Empstup.fmb) and System Menu (Esysmenu.mmb) 548

Installment 5—Developing and Adding Reports to the System 549

Summary 552

VIII Appendixes
A Built-In Subprograms 555

B Triggers 579

C System Variables 597

D Practice Database Installation Instructions 601

Importing the Database 602

Creating and Loading Your Tables 603


Page 21

E Answers to Practice Problems607

Chapter 2 608

Chapter 3 609

Chapter 4 611

Chapter 5 613

Chapter 6 614

Chapter 7 616

Chapter 8 617

Chapter 9 619

Chapter 10 624

Chapter 11 627

Chapter 12 627

Chapter 13 628

Chapter 14 629

Chapter 15 631

Chapter 17 633


Index635

Page 22

Page 23

Table of Contents | Next