Oracle 10g Programming: A Primer
Rajshekhar Sunderraman
Georgia State University

CONTENTS

Preface.................................................xv

1 The Relational Data Model..............................1
1.1  The Relational Database.............................1
1.2  Integrity Constraints...............................2
1.3  Tabular View of a Relation..........................4
1.4  Sample Databases....................................4
1.5  Relational Algebra.................................12
1.5.1  Set-Theoretic Operations.........................12
1.5.2  Relation-Theoretic Operations....................13
1.5.3  Queries in Relational Algebra....................16

2 Oracle SQL............................................19
2.1 Oracle SQL*Plus.....................................19
2.1.1 Entering and Exiting SQL*Plus.....................19
2.1.2 Executing Commands in SQL*Plus....................20
2.1.3 Buffer Manipulation Commands......................24
2.1.4 Formatting Query Results..........................26
2.1.5 Screen Capture of an SQL*Plus Session.............27
2.2 Creating, Dropping, and Altering Tables.............29
2.2.1 drop table........................................29
2.2.2 create table......................................31
2.2.3 alter table.......................................37
2.3 Inserting Rows......................................37
2.4 Querying the Database...............................38
2.4.1 Simple select Statement...........................38
2.4.2 Sub-Selects.......................................43
2.4.3 Union.............................................50
2.4.4 Forall Queries....................................51
2.4.5 Aggregate Functions...............................52
2.4.6 The group by and having Clauses...................54
2.4.7 The Full select Statement.........................56
2.4.8 String, Number, and Date Functions................57
2.5 Views...............................................69
2.5.1 create view.......................................69
2.5.2 drop view.........................................70
2.6 Modifying the Database..............................71
2.6.1 insert............................................71
2.6.2 update............................................72
2.6.3 delete............................................73
2.6.4 commit and rollback...............................73
2.7 Sequences...........................................74
2.8 Oracle Data Dictionary..............................75
2.8.1 The user_catalog Table............................75
2.8.2 The user_objects Table............................75
2.8.3 The user_tables Table.............................76
2.8.4 The user_tab_columns Table........................76
2.8.5 The user_views Table..............................76
2.9 Oracle10g Object Features...........................76
2.9.1 The create type Statement.........................77
2.9.2 The Varying Array Collection Type.................78
2.9.3 Inserting Rows....................................80
2.9.4 User-Defined Methods..............................81
2.9.5 Nested Tables.....................................82
2.9.6 Object Tables.....................................83
2.9.7 Inserting Rows into Nested Tables.................84
2.9.8 Querying and Accessing Data.......................85
Exercises...............................................88

3  PL/SQL...............................................93
3.1  What Is PL/SQL?....................................93
3.2  Data Types and Variables...........................94
3.3  Program Control Statements.........................96
3.3.1  null Statement...................................96
3.3.2  Assignment Statement.............................96
3.3.3  Conditional Statements...........................97
3.3.4  Loops...........................................100
3.4  Program Structure.................................102
3.4.1  Anonymous Blocks................................102
3.4.2  Procedures and Functions........................103
3.4.3  Stored Procedures and Functions.................108
3.4.4  Packages........................................110
3.5  Triggers..........................................114
3.6  Database Access Using Cursors.....................119
3.6.1  Explicit Cursors................................119
3.6.2  Parameterized Cursors...........................123
3.6.3  select for update...............................124
3.6.4  Cursor Variables................................125
3.7  Records...........................................128
3.7.1  Table-Based Records.............................128
3.7.2  Cursor-Based Records............................129
3.7.3  Programmer-Defined Records......................130
3.8  PL/SQL Tables.....................................131
3.9  Built-in Packages.................................134
3.9.1  The dbms_output Package.........................134
3.9.2  The dbms_sql Package............................136
3.10  Error Handling...................................148
3.11  PL/SQL Access to Oracle10g Objects...............152
3.11.1  Declaring and Initializing Objects.............152
3.11.2  Object Type Method Implementation..............153
3.11.3  Accessing Database Objects in PL/SQL...........155
3.11.4  Accessing Varying Arrays in PL/SQL.............157
3.11.5  Built-in Methods for Collection Objects........159
Exercises..............................................160

4  Web Programming with PL/SQL.........................165
4.1  HTTP..............................................166
4.1.1  Resources and URLs..............................167
4.1.2  Format of HTTP Messages.........................167
4.2 A  Simple Example..................................170
4.3  Printing HTML Tables..............................173
4.4  Passing Parameters................................174
4.4.1  Flexible Parameter Passing......................175
4.5  Processing HTML Forms.............................176
4.6  Multivalued Parameters............................185
4.6.1  Procedure process_teacher_option................186
4.6.2  Procedure select_course.........................187
4.6.3  Procedure process_teacher_sub_option............189
4.6.4  Procedure add_scores............................191
4.6.5  Procedure process_scores........................193
4.6.6  Procedure insert_scores.........................196
4.7  PL/SQL Web Toolkit................................197
4.7.1  Table Printing Utility..........................199
4.7.2  Dynamic SQL.....................................201
4.8  PL/SQL Server Pages (PSP).........................204
4.8.1  Parameter Passing in PSP........................206
4.8.2  Other PSP Syntax................................209
Exercises..............................................211

5 Oracle JDBC..........................................215
5.1 What Is JDBC?......................................215
5.2 A Simple JDBC Program..............................216
5.3 Developing JDBC Applications.......................217
5.3.1 Loading the JDBC Drivers.........................218
5.3.2 Connecting to the Database.......................218
5.3.3 The Connection Object............................219
5.4 Nonquery SQL Statements............................221
5.4.1 Using the Statement Object.......................222
5.4.2 Using the PreparedStatement Object...............225
5.4.3 Using the CallableStatement Object...............227
5.5 Executing SQL Queries..............................231
5.5.1 The ResultSet Class..............................232
5.5.2 ResultSet Methods................................232
5.5.3 An SQL Query Example.............................234
5.5.4 ResultSet Metadata...............................238
5.5.5 The Oracle REF CURSOR Type.......................244
5.5.6 Processing Multiple ResultSets...................246
5.6 Grade Book Application.............................249
5.6.1 Function select_course...........................250
5.6.2 Function add_enrolls.............................252
5.6.3 Function add_scores..............................253
5.6.4 Function modify_score............................255
5.7 Database Metadata..................................256
5.8 Errors and Warnings................................258
5.8.1 The SQLException Class...........................258
5.8.2 The SQLWarning Class.............................259
5.8.3 The DataTruncation Class.........................260
5.9 Scrollable ResultSets..............................261
Exercises..............................................266

6 Javascript...........................................273
6.1 Javascript Syntax and Basic Concepts...............273
6.2 The Web Browser Programming Environment............283
6.3 Event Handling Model and HTML Forms................286
6.4 A Simple Example...................................287
6.5 Form Data Validation...............................290
6.6 Creating HTML Form Elements at Run Time............294
6.7 Conditional Form Elements..........................300
Exercises..............................................305

7 Oracle Web Programming with Java Servlets............309
7.1 A Simple Servlet...................................310
7.2 HTTP Servlet API Basics............................312
7.2.1 The HttpServlet Class............................312
7.2.2 The HttpServletRequest Class.....................313
7.2.3 The HttpServletResponse Class....................313
7.3 Web Shopping Application...........................314
7.4 HTML Form Processing in Servlets...................317
7.5 SearchParts and AddToCart..........................322
7.6 Oracle's Dynamic HTML Generation Package...........330
7.6.1 A Simple Example.................................331
7.6.2 HTML Form Processing.............................333
7.6.3 Formatting HTML Tables...........................338
7.7 Session Tracking Using HttpSession Object..........340
7.7.1 HttpSession Methods related to State Management..341
7.7.2 HttpSession Methods related to Session Lifetime..342
7.7.3 An Example.......................................343
7.8 Java Server Pages..................................349
Exercises..............................................359

8 Oracle XML...........................................365
8.1 Basic Syntax.......................................366
8.1.1 XML Elements.....................................366
8.1.2 XML Attributes...................................367
8.1.3 Mixing Elements and Textual Data.................369
8.1.4 Miscellaneous Constructs.........................369
8.2 Document Type Definitions..........................369
8.3 XML Parsing in Java................................371
8.3.1 SAX Parsing......................................372
8.3.2 DOM Parsing......................................380
8.4 The Oracle XML-SQL Utility.........................389
8.4.1 Canonical SQL-to-XML Mapping.....................389
8.4.2 Canonical XML-to-SQL Mapping.....................391
8.4.3 The XSU Java API.................................394
8.5 XMLType............................................402
8.5.1 XMLType Columns in a Table.......................403
8.5.2 Querying XML Data................................405
8.5.3 Updating and Deleting XML Data...................410
8.6 Namespaces.........................................411
8.7 XML Schema.........................................412
8.7.1 Simple Types.....................................413
8.7.2 Complex Types....................................417
8.7.3 Validating XML Documents Against Schemas.........422
Exercises..............................................423

9 XML Querying and Transformation......................433
9.1 XPath..............................................433
9.1.1 Basic XPath Expressions..........................436
9.1.2 Advanced Navigation..............................437
9.2 XQuery.............................................440
9.2.1 Creating XML DB Resources Using the 
      DBMS_XDB PL/SQL Package..........................441
9.2.2 Executing XQuery Queries in SQL*Plus.............443
9.2.3 Simple XQuery Expressions........................444
9.2.4 FLWOR Expressions................................449
9.3 XSLT...............................................457
9.3.1 A Simple XSLT Program and Its Invocation.........458
9.3.2 The <xsl:value-of> XSLT Instruction..............459
9.3.3 The <xsl:for-each> XSLT Instruction..............461
9.3.4 The <xsl:sort> XSLT Instruction..................462
9.3.5 The <xsl:if> and <xsl:choose> XSLT Instructions..463
9.3.6 The <xsl:apply-templates> XSLT Instruction.......465
9.3.7 The Built-In Template Rules......................466
9.3.8 Creating Elements and Attributes.................467
9.3.9 XSL Variables and Parameters.....................469
Exercises..............................................475

10 Projects............................................479
10.1 Airline Flight Information System.................480
10.2 Library Database Application......................481
10.3 University Student Database.......................483
10.4 Video Chain Database..............................484
10.5 Banking Database..................................486
10.6 BibTEX Database...................................487
10.7 Music Store Database..............................489
10.8 Online Auctions Database..........................490
10.9 Oracle Data Dictionary Browser....................491
10.10 Oracle Data Browser on the Web...................492
10.11 QBE Interface on the Web.........................494
10.12 A Web Survey Management System...................496
10.13 Online Exam Management System....................499
10.14 Online Bulletin Board............................504
10.15 Data Input Forms.................................507

Suggested Readings.....................................513

Index..................................................517