Oracle Embedded Programming and Application Development

(1)

Oracle Embedded

Programming

and Application

Development

Lakshman Bulusu

(

r

oC) CRC Press

> V J Taylor &. Francis G r o u p ^*—"^^ Boca Raton London New York

CRC Press is an imprint of the

Taylor & Francis Group, an i n f o r m a business A N AUERBACH BOOK

(2)

2.3 New Feature and Solution Options in Oraclel Og 2.3.1 2.3.2 2.3.3 2.3.4 2.3.5 2.3.6 2.3.7 Regular Expressions DBMSJAVA.SETJDUTPUT Tracking the Error Line Number Enhanced FORALL Statement for Handling DML Array Processing with Sparse Collections Conditional Compilation (Oracle 10GR2) Compile-Time Warnings Integrated mod_plsql 17 18 19 19 23 23 23 23 31 32 33 34 35 35 35 36 36 39 40 41 42

Chapter 3 Programming Languages, Platforms, and Solutions 43

3.1 Introduction 43 3.2 Why and When Architecture Takes Precedence

over Feature Set 44 3.2.1 Suitability Criteria from a

Business/Customer Perspective 49 3.2.2 Suitability Criteria from a Solution

Architecture

(Тесптса1Яесппо-Functional) Perspective 50 3.3 Best Practices in Terms of Oracle Database

Interaction 53 3.3.1 Enforcing Business Rules for Efficiency 56

(4)

Contents ix

3.3.3 Database Interaction: More than Just GB

and Data Quality 58 3.4 Best Practices for using SQL and PL/SQL only

(with HTML, XML via PSP or Web Toolkit) 59 3.5 Extending PL/SQL to Use Emerging Dynamic

Scripting Languages Like Groovy in the Database 61 3.5.1 Extending PL/SQL to Use HTML/XML 61 3.5.2 Generating Web-Enabled PL/SQL

Applications Using Oracle APEX/Oracle

JDeveloper 65 3.6 Best Practices for Using .NET-Based Languages

(c++, at, j#) 66 3.6.1 ODP.NET: MorethanJustADO.NET

Rehashed 66 3.6.2 Leverage J2EE Inherent Server-Specific

Benefits with Minimal Cost and

Time-to-Deliver 67 3.7 Best Practices for Using Java-Based Languages

(Java Libraries, Java via JDBC, Java via SQLJ, Java

via Java Stored Procedures) 69 3.7.1 Using Java via JDBC, SQLJ, Java Stored

Procedures or Java Libraries 73 3.8 Best Practices for Using PHP-Based Languages

(PHP-only, PHP and Ajax, PHP Web Pages) 75 3.9 Perl in the Picture: Best Practices for the Perfect

Fit and Use of Perl in an Embedded Scenario 3.10 For Better or Worse: Application-centric and

Business-centric Key Performance Indicators 78

3.10.1 Focus on the Customer 78 3.10.2 Application-Centric 79

Chapter 4 Best Practices for Data Structure Management 83

4.1 Introduction 83 4.2 Data Representation: Database versus

Programming Language Perspectives 85 4.2.1 Database Implementation 93 4.2.2 Programming Language Implementation 94

4.3 Best Practices for Using Heterogeneous Data Structures: Arrays, Array Lists, Lists, Maps,

(5)

x Contents

4.3.1 Design of a Code Accelerator that Uses Optimized Data Structures for Efficient Data as Well as Data

Structure Management 98 4.4 Best Practices for Using LINQ to Objects

(inOracle11g) 100 4.4.1 How does LINQ fit in the

Oracle11g-.NET space? 101

4.4.2 Best Uses of ODP.NET LINQ to Objects

inOracle11g 103 4.5 Best Practices for Using Objects as Data Structures 104

4.5.1 Code Accelerator for the Consolidation

of Data based on Type Granularity 105 4.6 Best Practices for Metaprogramming: Use or

Eliminate 107

Chapter 5 Best Practices for Robust Error Detection and

Handling Management 111

5.1 Introduction 111 5.2 Best Design Practices to Reduce the Probability

of Errors and Enable Quick Resolution 113 5.2.1 Design Pattern for a Typical

Error-Handling Code Accelerator 118 5.2.2 Best Practices in Terms of Tracking and

Trapping Errors 122 5.3 Techniques to Obtain Complete Error Information 128

5.3.1 Tracking Error Information for Database

Errors (Occurring in Any Tier) 130 5.4 Techniques to Customize the Error Information

Returned 131 5.4.1 Database Errors 131

5.4.2 Programming Language-Specific Errors (Not Related to Oracle

Server Errors) 132 5.5 Techniques for Halting Further Processing on

Error Occurrences 133

Chapter 6 Best Practices for Data Management 135

(6)

Contents xi

6.2 Database Management Using Embedded Programming Languages: Design Practices for

the Best Degree of Fit 140 6.3 Best Practices in Terms of Connectivity to and

Interaction with the Oracle Database 144 6.4 Techniques to Connect to Multiple Databases

Simultaneously Using the Same Metadata 148 6.5 Best Fit for Oracle in the ADO.NET Framework—

ODP.NETandODE.NET 150

6.6 Best Practices for Data Retrieval 150 6.7 Best Practices for Data Manipulation 153 6.8 Best Practices for Data Validation 155 6.9 Best Practices for using LINQ to SQL 158

6.10 Best Practices for Using XML 158 6.11 Best Practices for Handling Unstructured Data 162

6.12 Best Practices to Protect Data Integrity and Special Cases Involved 1 65

6.12.1 User Interaction with Data (and hence with the Database) 1 66

6.12.2 Special Cases Involved 171 6.12.3 Application Interaction with Data (and

hence with the Database) 1 71 6.12.4 Design Pattern for a Typical Code

Accelerator 176

Chapter 7 Best Practices for Application Management 181

7.1 Introduction 181 7.2 Code Accelerators for Rapid Application

Development: Design and Coding Aspects 183 7.2.1 Code Accelerator Design Pragmatics 187 7.3 Best Practices for Application Web Interaction:

Receiving, Routing, Responding, Redirecting,

Rendering, Linking 196 7.3.1 Personalized A4_a-Carte of

Customer/End-User Service Requests: A Real-World Use Case of Application

Web Interaction 199 7.4 Best Practices for Application Integrity 200

7.4.1 A Design Pattern for a Common Information Integrity Framework at

(7)

xii Contents

7.5 Best Practices for Application Consistency 204 7.5.1 Multitenancy of Data Services Calls,

Web Services Calls, and Java

Services Calls 205 7.5.2 A Real-World Scenario that

Demonstrates the Use of These Best

Practices for Implementation 208 7.6 Best Practices for Security Beyond User

Authentication: End-to-End Application Security 209 7.6.1 Real-World Use Cases for Implementing

Web Security Policies 214

7.7 Best Fit for Oracle in the ASP.NET Framework 216

7.8 Best Practices for Templates: Use or Eliminate? 220 7.9 Best Practices Beyond HTML: Auto-generate

Dynamic Visual Content, Web Services, and

Mobile Applications 221 7.9.1 Auto-generate Dynamic Visual Content 221

7.9.2 Web Services 225 7.9.3 Mobilizing Applications 226

7.10 Best Practices for Creative Reporting 227

Chapter 8 Application Development Frameworks 237

8.1 Introduction 237 8.2 Application Development Framework: A

Pragmatic "Best-Possible" Solution 238 8.2.1 For a Transactional Solution 240 8.2.2 For a Reporting and Analysis Solution 243

8.3 Master Error Management Framework 251 8.3.1 Error-Logging Framework 251 8.3.2 DML Auditing Framework 261 8.4 Performance Tuning Framework 273

8.4.1 Framework for Monitoring and Tuning

SQL 274 8.4.2 Framework for Monitoring and Tuning

PL/SQL 277 8.5 Debugging Framework 280

8.5.1 Putting a Robust Error-Handling

Mechanism in Place 281 8.5.2 Introducing Debug Messages as Part of

Code Asynchronously 281 8.5.3 Testing of the Application Solution 294

(8)

Contents xiii

Chapter 9 Miscellaneous Best Practices 297

9.1 Simulating Oracle-Based Datasets: Best

Practices for Design and Coding 297 9.1.1 The In-Memory Processing Framework 298

9.1.2 Simulating Oracle-Based Data Sets

Using Transformation Pipeline Chain 302

9.2 Building a Highly Visible Web Site 307

Chapter 10 Best Practices in Terms of Coding Standards and

Troubleshooting 315

10.1 Introduction 315 10.2 Coding Standards from an Oraclel 1 g Embedded

Programming Perspective 316 10.3 Tuning SQL and PL/SQL Code for Optimality 321

10.4 Tuning Embedded Language Code for Optimality:

Data Structures and Procedural Code 330 10.5 Fine-Tuning Solution Methodology for Optimality 333