Page 1
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.
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:
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.
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.
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 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 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.
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
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.
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
- Introduction to Oracle Tools9
- Designing Your Database15
- Acquiring Data by Using the Select Statement37
- Formatting Reports in SQL *PLUS83
- Combining Tables and Rows103
- Defining the Database Objects119
- Modifying Your Tables with DML Commands151
- Creating Your First PL/SQL Program165
- Increasing the Power of Your PL/SQL Blocks189
- Using SQL*LOADER221
- Using an Oracle Form249
- Using the Object Navigator to Create Your First Oracle Form265
- Formatting Your Form295
- Creating and Modifying Master-Detail Forms337
- Using Triggers and Modifying Classes381
- Calling Other Forms and Using Menus411
- Creating Your First Report445
- Customizing Reports and Advanced Report Functions475
- Moving Oracle Applications to the Web503
Page 6
- Developing a Template Employee Information System525
A Built-In Subprograms555
B Triggers579
C System Variables597
D Practice Database Installation Instructons601
E Answers to Practice Problems607
Index635
Page 7
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
Oracle the Company10
Oracle7Oracle's Database Product10
Oracle8The Next Generation 11
SQL*PLUSOracle's Database Language 12
Other Oracle Products 12
Designer 2000 12
Personal Oracle7 12
Conventions and Techniques Used Throughout
this Book 12Summary 13
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 24Database Terminology 24
Documenting the Design of the Database 26
Database Tables 27
Normalizing Your Data 29
From Here 32
Review Exercises 32
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
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
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 108Inaccurate 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
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
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
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
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
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
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
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
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
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
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
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 426Creating 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
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
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
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
Installment 1Data Normalization and Database Design 526
Installment 2Creating the Database Objects 529
Installment 3Loading the Data into the Tables 534
Installment 4Designing 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 5Developing and Adding Reports to the System 549
Summary 552
Importing the Database 602
Creating and Loading Your Tables 603
Page 21
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