SEARCH
Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.
BOOK PROFESSIONAL ORACLE PROGRAMMING
SUMMARY
-
Items Found: 546
- Chapter 1: Oracle Architecture and Storage 1
- Architecture 2
- The Instance 2
- Data Organization 6
- Data Types 9
- Character Data Types 9
- BFILE 10
- Numeric Data Types 11
- Date Data Types 12
- RAW and LONG RAW 12
- Other Data Types 13
- Summary 13
- Chapter 2: Using SQL 15
- The Processing Cycle for SQL Statements 15
- Connecting to a Database 16
- Establishing a Cursor 18
- Submitting SQL Statements 18
- Receiving Data 21
- Performance Considerations 21
- Retrieval Performance 22
- Using Bind Variables 23
- Parallel Operations 25
- Summary 26
- Chapter 3: Handling Multiple Users 27
- Goals 28
- Data Integrity 28
- Isolation 29
- Serialization 29
- Transactions 29
- Concurrent User Integrity Problems 30
- Locks 33
- Contention 34
- The Oracle Solution 35
- Multiversion Read Consistency 35
- Integrity Implications 37
- Performance Implications 37
- Isolation Levels 38
- Implementation Issues 39
- Write Contention 39
- Avoiding Changed Data 40
- Summary 41
- Chapter 4: Database Design Basics 43
- Database Design Phases 45
- Conceptual Database Design 45
- Logical Database Design 45
- Physical Design 46
- Practical Design 47
- Database Design Tools 48
- Database Design Techniques 49
- Database Design Case Study Example 53
- Normalization 54
- First Normal Form 56
- Second Normal Form 59
- Third Normal Form 60
- Other Keys 60
- Normalization Summary 62
- Defining Additional Entities (Tables) 62
- Denormalization 63
- Other Physical Design Options 64
- Object-Oriented Design Options of Oracle 66
- Summary 66
- Chapter 5: Oracle Security 67
- Security Concepts 67
- Confidentiality and Privacy 67
- Integrity 68
- Why Database-Level Security? 68
- Authentication 69
- Users and Passwords 69
- Smart Cards and Biometric Identification 70
- Oracle Syntax 70
- Authorization 73
- System Privileges 74
- Object Privileges 74
- Roles 75
- Stored Procedures 78
- Access Control 78
- Views 79
- Encryption 80
- Virtual Private Database 81
- Oracle Label Security Option 84
- Accountability 85
- Auditing 85
- Triggers 88
- Enterprise Security 88
- LDAP 89
- Enterprise Users 89
- Shared Schemas 89
- Database Security 90
- Network Security 90
- Security Practices 92
- Summary 92
- xiv
- Chapter 6: The Oracle Data Dictionary 93
- What Is the Data Dictionary? 93
- Structure of the Oracle Data Dictionary 94
- USER_TABLES 95
- ALL_TABLES 96
- DBA_TABLES 97
- TAB 98
- V_$FIXED_TABLE 99
- The Oracle Data Dictionary During Development 101
- Locating and Describing Data Dictionary Views 101
- Which Set of Views? 103
- Updating the Data Dictionary 107
- The Oracle Data Dictionary at Execution Time 108
- SQL to Generate SQL 108
- Dynamic SQL 110
- Summary 112
- Chapter 7: Installing Oracle 113
- Getting Oracle Software 113
- Installing Oracle Server Software 116
- System Requirements 116
- Installing the Software 117
- Accessing Your Oracle Database 122
- Installing Oracle Client Software 123
- Java Drivers 124
- Oracle Client Software 124
- Installing the Instant Client 125
- Which Driver? 126
- Summary 127
- Chapter 8: Introduction to SQL 129
- A Bit of History 129
- Standards 130
- SQL Statement Groupings 130
- Data Definition Language (DDL) 131
- CREATE 131
- ALTER 133
- DROP 134
- Caveat! 134
- Data Manipulation Language (DML) 135
- SQL: Set-Orientated and Nonprocedural 135
- SELECT Statement 135
- Multi-Table Access 147
- Subqueries 150
- Insert 153
- UPDATE 155
- DELETE 155
- COMMIT/ROLLBACK/SAVEPOINT 156
- Summary 156
- Chapter 9: Extended SQL 159
- Extended Conditions 159
- LIKE and REGEXP_LIKE 159
- IN and EXISTS 160
- ALL, SOME, and ANY 164
- Extended Set Operations 166
- UNION 166
- INTERSECT 168
- MINUS 169
- MERGE 171
- An Example 172
- Caveats 173
- TRUNCATE 174
- ROWNUM and Ranking 174
- Using ROWNUM 174
- Using Analytics 177
- Flashback Query 179
- How It Works 180
- Syntax 180
- An Example of Flashback Query 180
- Caveats 183
- Extended Flashback Capabilities 183
- Returning Data from Write Operations 183
- Additional Extensions 184
- Summary 184
- Chapter 10: Indexes 185
- Index Basics 186
- How an Index Works 186
- Keys 190
- Index Attributes 190
- B-Tree Indexes 193
- How It Works 193
- B-Tree Index Myths 194
- Reverse Key Indexes 196
- Function-Based Indexes 197
- Function-Based Indexes at Work 198
- Caveats 201
- Domain Indexes 202
- Bitmap Indexes 202
- The Structure of a Bitmap Index 203
- The Impact of a Bitmap Index 204
- Bitmap Join Index 206
- Index-Organized Tables 207
- Index Clusters 207
- Hash Cluster 209
- Design and Performance Tips 209
- Start Small 209
- Key Compression 210
- SQL Access Advisor 211
- Summary 211
- Chapter 11: Constraints 213
- Database Integrity Features 213
- Data Typing 214
- Constraints 215
- Triggers and Stored Procedures 215
- Types of Constraints 216
- NOT NULL 217
- PRIMARY KEY 219
- UNIQUE 221
- FOREIGN KEY 222
- CHECK 226
- DEFAULT 230
- REF Constraints 231
- Working with Constraints 232
- Alter Table 233
- Constraint States 234
- Constraints and Indexes 236
- Deferrable Constraints 238
- Direct Path Operations 238
- Constraints and Export/Import 239
- Effect on Mutating Table Triggers 239
- Constraints on Views 239
- Using Database Constraints for Application-Side Rule Checking 240
- Summary 240
- Chapter 12: Other Database Structures 241
- Tablespaces 241
- Temporary Tablespaces 242
- Undo Tablespaces 243
- Compression 243
- Partitioning 245
- Views 246
- Materialized Views 248
- Sequences 248
- Global Temporary Tables 250
- Recycle Bin 250
- Synonyms 253
- Why Use Synonyms? 253
- Working with Synonyms 253
- Database Links 255
- Advanced Queuing and Streams 255
- Streams AQ 255
- Streams 256
- Streams Summary 258
- Objects 258
- Stored Procedures, Functions, and Triggers 259
- Accessing Flat Files 260
- External Tables 260
- UTL_FILE 260
- SGA Buffer Pool Assignments 260
- Workflows 261
- Workspace Manager 262
- Summary 263
- Chapter 13: Functions 265
- What Is a Function? 265
- Defining Your Own Functions 265
- Permissions to Create Functions 266
- Where Can Functions Be Used? 266
- Defining a Java Function 267
- Viewing Information about Your Functions 267
- Deleting a Function 268
- Oracle’s Built-In SQL Functions 268
- Aggregate Functions 268
- Numeric Functions 275
- Character Functions 281
- Date and Time Functions 288
- Conversion Functions 295
- Collection Functions 303
- Other Oracle Functions 303
- XML Functions 309
- Summary 313
- Chapter 14: Distributed Queries, Transactions, and Databases 315
- Linking Distributed Oracle Databases 316
- Distributed Transactions and Two-Phase Commit 319
- Heterogeneous Services 320
- ODBC 321
- Transparent Gateways 322
- Summary 323
- Chapter 15: PL/SQL Basics 325
- Code Architecture 325
- The Block Header 326
- The Declaration Section 328
- The Execution Section 328
- The Exception Section 329
- How Exceptions Are Handled 329
- Error-Related Functions 330
- Scoping and Notation 330
- PL/SQL language 331
- Implementing Logic 335
- Conditional Logic 335
- Branching 338
- Iterative 339
- Doing Nothing 340
- Creating a PL/SQL Program 341
- The Purpose of the Sample Procedure 341
- Starting the Code 341
- Adding Variables 342
- Adding Logic 342
- Adding Exception Handling 343
- Using PL/SQL Code 344
- Preparing for Compilation 344
- Compiling PL/SQL Code 344
- Running PL/SQL Code 345
- Compilation, Source Code, and Dependencies 346
- Security 348
- Granting Access to PL/SQL units 348
- Program Unit Rights 348
- Native Compilation 349
- Uses of PL/SQL Units 350
- Summary 350
- Chapter 16: PL/SQL and SQL 351
- Basic SQL in PL/SQL 351
- Cursors 352
- Creating a Cursor 352
- Opening a Cursor 352
- Fetching Data 352
- Closing the Cursor 353
- Cursor Attributes 353
- Using a Single Cursor 353
- Declare Variables 354
- Declare Cursor 354
- Open a Cursor 354
- Fetch 355
- Clean Up 356
- Running the Procedure 356
- Special Data Types 357
- %TYPE 357
- %ROWTYPE 358
- FOR Cursor 359
- Syntax 359
- Modifying the Example 359
- Implicit Cursors 360
- REF CURSORs 362
- PL/SQL Records and Collections 363
- PL/SQL Records 363
- PL/SQL Records and SQL 364
- Associative Arrays 364
- Nested Tables 365
- Variable Arrays 366
- Working with Collections 366
- Collection Operations 367
- Which Collection Type? 369
- BULK COLLECT 369
- Using BULK COLLECT 370
- FORALL 373
- Exception Cases 373
- FORALL Enhancements 374
- Dynamic SQL 375
- EXECUTE IMMEDIATE 376
- Bulk Operations with Dynamic SQL 376
- More on Dynamic SQL 376
- Summary 376
- Chapter 17: PL/SQL Packages 379
- What Is a Package? 379
- Impact of Packages 380
- Organization 381
- Visibility 381
- Scope 381
- Dependencies 382
- Runtime Memory Usage 382
- Oracle’s Endorsement 383
- An Example 383
- The Package Specification 383
- The Package Body 384
- Running the Example 390
- Using Packages Together 391
- Your Second Package 392
- Package Code 392
- Changing the First Package 393
- Impact on the Second Package 394
- Summary 396
- Chapter 18: Introduction to Java Database Programming 397
- Java Architecture 398
- Java: The Language 398
- Java Virtual Machine and Bytecode 398
- What about Performance? 399
- J2SE and J2EE 400
- Oracle’s Java Architecture 406
- JDBC and Persistence Frameworks 408
- Java outside the Database 411
- Java inside the Database 412
- Building Sample Applications 413
- Setting the CLASSPATH 413
- Java Application Using JDBC—Basic Ingredients 414
- Java Application Using OracleAS TopLink 419
- Java Stored Procedures 427
- Summary 434
- Chapter 19: Triggers 437
- What Are Triggers? 437
- Types of Triggers 437
- Order of Execution 438
- Working with Triggers 438
- Naming Triggers 438
- Permissions for Creating Triggers 439
- Creating Triggers 439
- :new and :old 440
- Update Triggers 441
- Delete Triggers 441
- Controlling When the Trigger Fires 442
- Inserting or Updating? 443
- Special Circumstances 443
- INSTEAD OF Triggers 444
- Triggers for Referential Integrity 448
- Triggers in Other Languages 448
- Triggers and Mutating Tables 449
- Removing Triggers 451
- Deleting/Dropping Triggers 451
- Disabling Triggers 451
- Recompiling Triggers 452
- Viewing Triggers in the Data Dictionary 452
- Limitations of Triggers 453
- Summary 453
- Chapter 20: Regular Expressions and Expression Filter 455
- Regular Expressions 456
- Regular Expression Concepts 457
- Relevance to Database Applications 461
- Oracle Regular Expressions 462
- Usage Scenarios 470
- Architecture and Performance Considerations 475
- Expression Filter 476
- Expression Filter Concepts 478
- Relevance to Database Applications 484
- Summary 485
- Chapter 21: Object Relational Interactions with Oracle 487
- Object Relational 488
- ODBMS and RDBMS and ORDBMS 488
- Introducing Oracle Objects 489
- Object Types 490
- Object Type Methods 493
- Inheritance Support 497
- Schema Evolution 499
- Mapping to UML Concepts 500
- Using Oracle Object Types 501
- Existing Java Class as a Persistent Object Type 501
- Creating Instances of Object Types in the Database 504
- Using Java to Interact with Database Objects 506
- Object Views on Relational Data 511
- Oracle JPublisher (JPub) 515
- Configuration and Run Time 516
- Publishing SQL and DML Statements 517
- Publishing Java Stored Procedures 518
- Publishing Object Types 519
- Support for Web Services 520
- Summary 520
- Chapter 22: Oracle XML DB 521
- Overview of the XML DB Repository 521
- Protocol-Based Access to XML in the Oracle Repository 522
- SQL-Based Access to XML Data in the Oracle Repository 529
- Enabling a New Schema for Use with XDB Repositories 533
- ACL-Based Security on Oracle XML Repositories 533
- XMLType 536
- Creating XMLType Columns and Tables 537
- XML Views 538
- XML Schema 540
- SQL/XML and Query Rewrite 533
- SQL to XML 554
- Transforming XML 557
- A Content Management Example 558
- Creating the Content Management Application Front End 558
- Oracle XML Outside the Database—Oracle XDK 574
- Summary 577
- Chapter 23: HTML-DB 579
- What Is HTML-DB? 579
- The HTML-DB Environment 580
- Installing HTML-DB 581
- Preparing a Development Environment 588
- Creating a Workspace 588
- Creating an Application Developer 591
- Importing Data 593
- Creating an Application 599
- Functionality in Your HTML-DB Application 610
- Adding a New Page 618
- Modifying Page Attributes 622
- Running the Application 625
- Is That All There Is? 626
- The Next Step 627
- Summary 627
- Chapter 24: High-Speed Data Movement 629
- Export/Import and the “Data Pump” 630
- Transportable Tablespaces 632
- Usage Considerations 632
- Transportable Tablespace Examples 632
- Streams 634
- Streams Phases and Processes 634
- Configuring and Monitoring Advanced Queues 635
- Summary 638
- Chapter 25: Data Loading and Management 639
- SQL*Loader 640
- External Table Database Feature 641
- Defining External Tables 641
- Loading from External Tables 642
- Transformations 643
- Change Data Capture 644
- Choantpetenrt s#
- Partitioning and the Speed of ETL 645
- Oracle Warehouse Builder 647
- Typical Build and Deploy Steps Using OWB 647
- An Example Using OWB 648
- Nonrelational Targets 652
- Summary 652
- Chapter 26: Business Intelligence Query 653
- Schema for Business Intelligence 654
- Dimensions and Hierarchies 658
- Creating Dimensions 660
- Summary Tables and Materialized Views 661
- Using the SQL Access Advisor (Summary Advisor) 663
- Creating and Editing Materialized Views 664
- Aggregation through SQL: Rollup and Cube 665
- Proactive Management of Complex Query Workloads 667
- Summary 669
- Chapter 27: Business Intelligence Analysis 671
- SQL Analytics and Statistics 671
- Ranking and Percentiles 672
- Windows Functions for Cumulative and Moving Aggregates 675
- Lag/Lead Analysis 678
- First/Last Analysis 679
- Linear Regression and Other Advanced Functions 680
- OLAP 682
- Data Mining 686
- Summary 688
- Chapter 28: Optimization 689
- What Is Optimization? 689
- The Oracle Optimizer 690
- Optimizer Modes 691
- Desupported Optimizer Modes 691
- Setting Optimizer Modes 692
- Statistics 692
- Statistics and Cost 692
- Types of Statistics 693
- Collecting Statistics 694
- Statistics and Change 694
- Production and Test Environments 695
- Histograms 696
- Working with Histograms 696
- Execution Plans 699
- The Execution Plan 699
- Data Operations 700
- Execution Statistics 702
- Overall Execution Statistics 703
- Detailed Statistics 704
- Execution Plan and Statistics—An Example 704
- Viewing Execution Plans and Statistics 709
- AUTOTRACE 709
- EXPLAIN PLAN 710
- SQL Trace and TKPROF 712
- Enterprise Manager 715
- V$SQL_PLAN and V$SQL_PLAN_STATISTICS 715
- Hints 716
- Syntax of a Hint 716
- Available Hints 716
- Parallel Operations Hints 720
- Now Don’t Use Them 721
- Stored Outlines 722
- Creating Stored Outlines 722
- Using Stored Outlines 723
- Editing Stored Outlines 723
- Summary 723
- Index 725
