SEARCH
Disclaimer: Authors have full rights over their works. Reproduction of any part of the content is prohibited without prior authorization.
BOOK ORACLE ADVANCED PLSQL DEVELOPER PROFESSIONAL GUIDE
SUMMARY
-
Items Found: 636
- Preface 1
- Chapter 1: Overview of PL/SQL Programming Concepts 9
- PL/SQL—the procedural aspect 10
- My irst PL/SQL program 11
- PL/SQL development environments 13
- SQL Developer 13
- SQL Developer—the history 15
- Creating a connection 15
- SQL Worksheet 16
- Executing a SQL statement 18
- Calling a SQL script from SQL Developer 19
- Creating and executing an anonymous PL/SQL block 21
- Debugging the PL/SQL code 21
- Editing and saving the scripts 23
- SQL*Plus 24
- Executing a SQL statement in SQL*Plus 26
- Executing an anonymous PL/SQL block 26
- Procedures 27
- Executing a procedure 28
- Functions 29
- Function—execution methods 31
- Restrictions on calling functions from SQL expressions 32
- PL/SQL packages 33
- Cursors—an overview 35
- Cursor execution cycle 35
- Cursor attributes 36
- Cursor FOR loop 38
- Exception handling in PL/SQL 39
- System-deined exceptions 39
- User-deined exceptions 41
- The RAISE_APPLICATION_ERROR procedure 43
- Exception propagation 46
- Managing database dependencies 48
- Displaying the direct and indirect dependencies 49
- Dependency metadata 50
- Dependency issues and enhancements 50
- Reviewing Oracle-supplied packages 51
- Summary 52
- Practice exercise 52
- Chapter 2: Designing PL/SQL Code 55
- Understanding cursor structures 55
- Cursor execution cycle 56
- Cursor design considerations 57
- Cursor design—guidelines 58
- Cursor attributes 59
- Implicit cursors 60
- Explicit cursors 62
- Cursor variables 66
- Ref cursor types—strong and weak 69
- SYS_REFCURSOR 69
- Processing a cursor variable 70
- Cursor variables as arguments 71
- Cursor variables—restrictions 73
- Subtypes 74
- Subtype classiication 75
- Oracles predeined subtypes 75
- User-deined subtypes 76
- Type compatibility with subtypes 77
- Summary 78
- Practice exercise 78
- Chapter 3: Using Collections 81
- Collections—an overview 82
- Categorization 83
- Selecting an appropriate collection type 84
- Associative arrays 84
- Nested tables 88
- Nested table collection type as the database object 90
- DML operations on nested table columns 91
- A nested table collection type in PL/SQL 93
- Additional features of a nested table 94
- Varray 98
- Varray in PL/SQL 99
- Varray as a database collection type 100
- DML operations on varray type columns 100
- Collections—a comparative study 103
- Common characteristics of collection types 103
- Nested table versus associative arrays 104
- Nested table versus varrays 105
- PL/SQL collection methods 105
- EXISTS 105
- COUNT 106
- LIMIT 107
- FIRST and LAST 108
- PRIOR and NEXT 109
- EXTEND 109
- TRIM 111
- DELETE 112
- Manipulating collection elements 113
- Collection initialization 115
- Summary 117
- Practice exercise 117
- Chapter 4: Using Advanced Interface Methods 121
- Understanding external routines 122
- Architecture of external routines 122
- Oracle Net Coniguration 125
- TNSNAMES.ora 125
- LISTENER.ora 126
- Oracle Net Coniguration veriication 129
- Beneits of external procedures 130
- Executing external C programs from PL/SQL 131
- Executing C program through external procedure—development steps 131
- Executing Java programs from PL/SQL 136
- Calling a Java class method from PL/SQL 137
- Uploading a Java class into the database—development steps 137
- The loadjava utility—an illustration 137
- Creating packages for Java class methods 140
- Summary 141
- Practice exercise 141
- Chapter 5: Implementing VPD with Fine Grained Access Control 145
- Fine Grained Access Control 146
- Overview 146
- Virtual Private Database—the alias 147
- How FGAC or VPD works? 147
- Salient features of VPD 148
- VPD implementation—outline and components 149
- Application context 150
- Policy function deinition and implementation of row-level security 153
- Associating a policy using the DBMS_RLS package 155
- VPD implementation—demonstrations 156
- Assignment 1—implementing VPD using simple security policy 157
- Assignment 2—implementing VPD using an application context 159
- VPD policy metadata 163
- Policy utilities—refresh and drop 164
- Summary 165
- Practice exercise 165
- Chapter 6: Working with Large Objects 169
- Introduction to the LOB data types 170
- Internal LOB 171
- External LOB 171
- Understanding the LOB data types 172
- LOB value and LOB locators 172
- BLOB or CLOB! 172
- BFILE 173
- Temporary LOBs 173
- Creating LOB data types 173
- Directories 173
- Creating LOB data type columns in a table 175
- Managing LOB data types 177
- Managing internal LOBs 178
- Securing and managing BFILEs 178
- The DBMS_LOB package—overview 179
- Security model 179
- DBMS_LOB constants 180
- DBMS_LOB data types 180
- DBMS_LOB subprograms 181
- Rules and regulations 182
- Working with the CLOB, BLOB, and BFILE data types 183
- Initializing LOB data type columns 184
- Inserting data into a LOB column 185
- Populating a LOB data type using an external ile 185
- Selecting LOB data 189
- Modifying the LOB data 190
- Delete LOB data 192
- Miscellaneous LOB notes 192
- LOB column states 193
- Locking a row containing LOB 193
- Opening and closing LOBs 193
- Accessing LOBs 193
- LOB restrictions 194
- Migrating from LONG to LOB 194
- Using temporary LOBs 196
- Temporary LOB operations 196
- Managing temporary LOBs 197
- Validating, creating, and freeing a temporary LOB 198
- Summary 200
- Practice exercise 200
- Chapter 7: Using SecureFile LOBs 205
- Introduction to SecureFiles 206
- SecureFile LOB—an overview 207
- Architectural enhancements in SecureFiles 208
- SecureFile LOB features 210
- Working with SecureFiles 211
- SecureFile metadata 213
- Enabling advanced features in SecureFiles 214
- Deduplication 214
- Compression 215
- Encryption 216
- Migration from BasicFiles to SecureFiles 220
- Online Redeinition method 221
- Summary 224
- Practice exercise 225
- Chapter 8: Compiling and Tuning to Improve Performance 227
- Native and interpreted compilation techniques 228
- Real native compilation 229
- Selecting the appropriate compilation mode 230
- When to choose interpreted compilation mode? 230
- When to choose native compilation mode? 231
- Setting the compilation mode 231
- Querying the compilation settings 232
- Compiling a program unit for a native or interpreted compilation 233
- Compiling the database for PL/SQL native compilation (NCOMP) 235
- Tuning PL/SQL code 238
- Comparing SQL and PL/SQL 239
- Avoiding implicit data type conversion 239
- Understanding the NOT NULL constraint 241
- Using the PLS_INTEGER data type for arithmetic operations 243
- Using a SIMPLE_INTEGER data type 245
- Modularizing the PL/SQL code 246
- Using bulk binding 248
- Using SAVE_EXCEPTIONS 252
- Rephrasing the conditional control statements 254
- Conditions with an OR logical operator 254
- Conditions with an AND logical operator 254
- Enabling intra unit inlining 255
- PLSQL_OPTIMIZE_LEVEL—the Oracle initialization parameter 256
- Case 1—PLSQL_OPTIMIZE_LEVEL = 0 256
- Case 2—PLSQL_OPTIMIZE_LEVEL = 1 258
- Case 3—PLSQL_OPTIMIZE_LEVEL = 2 259
- Case 4—PLSQL_OPTIMIZE_LEVEL = 3 260
- PRAGMA INLINE 262
- Summary 265
- Practice exercise 265
- Chapter 9: Caching to Improve Performance 269
- Introduction to result cache 270
- Server-side result cache 271
- SQL query result cache 272
- PL/SQL function result cache 272
- OCI client results cache 273
- Coniguring the database for the server result cache 273
- The DBMS_RESULT_CACHE package 276
- Implementing the result cache in SQL 277
- Manual result cache 277
- Automatic result cache 279
- Result cache metadata 281
- Query result cache dependencies 283
- Cache memory statistics 283
- Invalidation of SQL result cache 284
- Displaying the result cache memory report 286
- Read consistency of the SQL result cache 287
- Limitation of SQL result cache 287
- Implementing result cache in PL/SQL 288
- The RESULT_CACHE clause 288
- Cross-session availability of cached results 292
- Invalidation of PL/SQL result cache 292
- Limitations of PL/SQL function result cache 294
- Argument and return type restrictions 294
- Function structural restrictions 294
- Summary 295
- Practice exercise 295
- Chapter 10: Analyzing PL/SQL Code 299
- Track coding information 299
- [DBA | ALL | USER]_ARGUMENTS 301
- [DBA | ALL | USER]_OBJECTS 304
- [DBA | ALL | USER]_SOURCE 306
- [DBA | ALL | USER]_PROCEDURES 307
- [DBA | ALL | USER]_DEPENDENCIES 308
- Using SQL Developer to ind coding information 310
- The DBMS_DESCRIBE package 313
- DBMS_UTILITY.FORMAT_CALL_STACK 316
- Tracking propagating exceptions in PL/SQL code 318
- Determining identiier types and usages 319
- The PL/Scope tool 320
- The PL/Scope identiier collection 320
- The PL/Scope report 322
- Illustration 322
- Applications of the PL/Scope report 325
- The DBMS_METADATA package 326
- DBMS_METADATA data types and subprograms 327
- Parameter requirements 330
- The DBMS_METADATA transformation parameters and ilters 330
- Working with DBMS_METADATA—illustrations 332
- Case 1—retrieve the metadata of a single object 332
- Case 2—retrieve the object dependencies on the F_GET_LOC function 335
- Case 3—retrieve system grants on the ORADEV schema 335
- Case 4—retrieve objects of function type in the ORADEV schema 336
- Summary 337
- Practice exercise 337
- Chapter 11: Proiling and Tracing PL/SQL Code 339
- Tracing the PL/SQL programs 340
- The DBMS_TRACE package 341
- Installing DBMS_TRACE 341
- DBMS_TRACE subprograms 341
- The PLSQL_DEBUG parameter and the DEBUG option 343
- Viewing the PL/SQL trace information 344
- Demonstrating the PL/SQL tracing 347
- Proiling the PL/SQL programs 350
- Oracle hierarchical proiler—the DBMS_HPROF package 351
- View proiler information 352
- Demonstrating the proiling of a PL/SQL program 352
- The plshprof utility 357
- Sample reports 359
- Summary 361
- Practice exercise 361
- Chapter 12: Safeguarding PL/SQL Code against SQL
- Injection Attacks 365
- SQL injection—an introduction 366
- SQL injection—an overview 366
- Types of SQL injection attacks 369
- Preventing SQL injection attacks 369
- Immunizing SQL injection attacks 370
- Reducing the attacks surface 370
- Controlling user privileges 371
- Invokers and deiners rights 371
- Avoiding dynamic SQL 375
- Bind arguments 378
- Sanitizing inputs using DBMS_ASSERT 379
- The DBMS_ASSERT package 380
- Testing the code for SQL injection laws 386
- Test strategy 386
- Reviewing the code 386
- Static code analysis 387
- Fuzz tools 387
- Generating test cases 387
- Summary 388
- Practice exercise 388
- Appendix: Answers to Practice Questions 391
- Chapter 1, Overview of PL/SQL Programming Concepts 391
- Chapter 2, Designing PL/SQL Code 392
- Chapter 3, Using Collections 393
- Chapter 4, Using Advanced Interface Methods 394
- Chapter 5, Implementing VPD with Fine Grained Access Control 395
- Chapter 6, Working with Large Objects 396
- Chapter 7, Using SecureFile LOBs 397
- Chapter 8, Compiling and Tuning to Improve Performance 398
- Chapter 9, Caching to Improve Performance 400
- Chapter 10, Analyzing PL/SQL Code 401
- Chapter 11, Proiling and Tracing PL/SQL Code 401
- Chapter 12, Safeguarding PL/SQL Code against SQL Injection Attacks 402
- Preface 1
- Chapter 1: Overview of PL/SQL Programming Concepts 9
- PL/SQL—the procedural aspect 10
- My irst PL/SQL program 11
- PL/SQL development environments 13
- SQL Developer 13
- SQL Developer—the history 15
- Creating a connection 15
- SQL Worksheet 16
- Executing a SQL statement 18
- Calling a SQL script from SQL Developer 19
- Creating and executing an anonymous PL/SQL block 21
- Debugging the PL/SQL code 21
- Editing and saving the scripts 23
- SQL*Plus 24
- Executing a SQL statement in SQL*Plus 26
- Executing an anonymous PL/SQL block 26
- Procedures 27
- Executing a procedure 28
- Functions 29
- Function—execution methods 31
- Restrictions on calling functions from SQL expressions 32
- PL/SQL packages 33
- Cursors—an overview 35
- Cursor execution cycle 35
- Cursor attributes 36
- Cursor FOR loop 38
- Exception handling in PL/SQL 39
- System-deined exceptions 39
- User-deined exceptions 41
- The RAISE_APPLICATION_ERROR procedure 43
- Exception propagation 46
- Managing database dependencies 48
- Displaying the direct and indirect dependencies 49
- Dependency metadata 50
- Dependency issues and enhancements 50
- Reviewing Oracle-supplied packages 51
- Summary 52
- Practice exercise 52
- Chapter 2: Designing PL/SQL Code 55
- Understanding cursor structures 55
- Cursor execution cycle 56
- Cursor design considerations 57
- Cursor design—guidelines 58
- Cursor attributes 59
- Implicit cursors 60
- Explicit cursors 62
- Cursor variables 66
- Ref cursor types—strong and weak 69
- SYS_REFCURSOR 69
- Processing a cursor variable 70
- Cursor variables as arguments 71
- Cursor variables—restrictions 73
- Subtypes 74
- Subtype classiication 75
- Oracles predeined subtypes 75
- User-deined subtypes 76
- Type compatibility with subtypes 77
- Summary 78
- Practice exercise 78
- Chapter 3: Using Collections 81
- Collections—an overview 82
- Categorization 83
- Selecting an appropriate collection type 84
- Associative arrays 84
- Nested tables 88
- Nested table collection type as the database object 90
- DML operations on nested table columns 91
- A nested table collection type in PL/SQL 93
- Additional features of a nested table 94
- Varray 98
- Varray in PL/SQL 99
- Varray as a database collection type 100
- DML operations on varray type columns 100
- Collections—a comparative study 103
- Common characteristics of collection types 103
- Nested table versus associative arrays 104
- Nested table versus varrays 105
- PL/SQL collection methods 105
- EXISTS 105
- COUNT 106
- LIMIT 107
- FIRST and LAST 108
- PRIOR and NEXT 109
- EXTEND 109
- TRIM 111
- DELETE 112
- Manipulating collection elements 113
- Collection initialization 115
- Summary 117
- Practice exercise 117
- Chapter 4: Using Advanced Interface Methods 121
- Understanding external routines 122
- Architecture of external routines 122
- Oracle Net Coniguration 125
- TNSNAMES.ora 125
- LISTENER.ora 126
- Oracle Net Coniguration veriication 129
- Beneits of external procedures 130
- Executing external C programs from PL/SQL 131
- Executing C program through external procedure—development steps 131
- Executing Java programs from PL/SQL 136
- Calling a Java class method from PL/SQL 137
- Uploading a Java class into the database—development steps 137
- The loadjava utility—an illustration 137
- Creating packages for Java class methods 140
- Summary 141
- Practice exercise 141
- Chapter 5: Implementing VPD with Fine Grained Access Control 145
- Fine Grained Access Control 146
- Overview 146
- Virtual Private Database—the alias 147
- How FGAC or VPD works? 147
- Salient features of VPD 148
- VPD implementation—outline and components 149
- Application context 150
- Policy function deinition and implementation of row-level security 153
- Associating a policy using the DBMS_RLS package 155
- VPD implementation—demonstrations 156
- Assignment 1—implementing VPD using simple security policy 157
- Assignment 2—implementing VPD using an application context 159
- VPD policy metadata 163
- Policy utilities—refresh and drop 164
- Summary 165
- Practice exercise 165
- Chapter 6: Working with Large Objects 169
- Introduction to the LOB data types 170
- Internal LOB 171
- External LOB 171
- Understanding the LOB data types 172
- LOB value and LOB locators 172
- BLOB or CLOB! 172
- BFILE 173
- Temporary LOBs 173
- Creating LOB data types 173
- Directories 173
- Creating LOB data type columns in a table 175
- Managing LOB data types 177
- Managing internal LOBs 178
- Securing and managing BFILEs 178
- The DBMS_LOB package—overview 179
- Security model 179
- DBMS_LOB constants 180
- DBMS_LOB data types 180
- DBMS_LOB subprograms 181
- Rules and regulations 182
- Working with the CLOB, BLOB, and BFILE data types 183
- Initializing LOB data type columns 184
- Inserting data into a LOB column 185
- Populating a LOB data type using an external ile 185
- Selecting LOB data 189
- Modifying the LOB data 190
- Delete LOB data 192
- Miscellaneous LOB notes 192
- LOB column states 193
- Locking a row containing LOB 193
- Opening and closing LOBs 193
- Accessing LOBs 193
- LOB restrictions 194
- Migrating from LONG to LOB 194
- Using temporary LOBs 196
- Temporary LOB operations 196
- Managing temporary LOBs 197
- Validating, creating, and freeing a temporary LOB 198
- Summary 200
- Practice exercise 200
- Chapter 7: Using SecureFile LOBs 205
- Introduction to SecureFiles 206
- SecureFile LOB—an overview 207
- Architectural enhancements in SecureFiles 208
- SecureFile LOB features 210
- Working with SecureFiles 211
- SecureFile metadata 213
- Enabling advanced features in SecureFiles 214
- Deduplication 214
- Compression 215
- Encryption 216
- Migration from BasicFiles to SecureFiles 220
- Online Redeinition method 221
- Summary 224
- Practice exercise 225
- Chapter 8: Compiling and Tuning to Improve Performance 227
- Native and interpreted compilation techniques 228
- Real native compilation 229
- Selecting the appropriate compilation mode 230
- When to choose interpreted compilation mode? 230
- When to choose native compilation mode? 231
- Setting the compilation mode 231
- Querying the compilation settings 232
- Compiling a program unit for a native or interpreted compilation 233
- Compiling the database for PL/SQL native compilation (NCOMP) 235
- Tuning PL/SQL code 238
- Comparing SQL and PL/SQL 239
- Avoiding implicit data type conversion 239
- Understanding the NOT NULL constraint 241
- Using the PLS_INTEGER data type for arithmetic operations 243
- Using a SIMPLE_INTEGER data type 245
- Modularizing the PL/SQL code 246
- Using bulk binding 248
- Using SAVE_EXCEPTIONS 252
- Rephrasing the conditional control statements 254
- Conditions with an OR logical operator 254
- Conditions with an AND logical operator 254
- Enabling intra unit inlining 255
- PLSQL_OPTIMIZE_LEVEL—the Oracle initialization parameter 256
- Case 1—PLSQL_OPTIMIZE_LEVEL = 0 256
- Case 2—PLSQL_OPTIMIZE_LEVEL = 1 258
- Case 3—PLSQL_OPTIMIZE_LEVEL = 2 259
- Case 4—PLSQL_OPTIMIZE_LEVEL = 3 260
- PRAGMA INLINE 262
- Summary 265
- Practice exercise 265
- Chapter 9: Caching to Improve Performance 269
- Introduction to result cache 270
- Server-side result cache 271
- SQL query result cache 272
- PL/SQL function result cache 272
- OCI client results cache 273
- Coniguring the database for the server result cache 273
- The DBMS_RESULT_CACHE package 276
- Implementing the result cache in SQL 277
- Manual result cache 277
- Automatic result cache 279
- Result cache metadata 281
- Query result cache dependencies 283
- Cache memory statistics 283
- Invalidation of SQL result cache 284
- Displaying the result cache memory report 286
- Read consistency of the SQL result cache 287
- Limitation of SQL result cache 287
- Implementing result cache in PL/SQL 288
- The RESULT_CACHE clause 288
- Cross-session availability of cached results 292
- Invalidation of PL/SQL result cache 292
- Limitations of PL/SQL function result cache 294
- Argument and return type restrictions 294
- Function structural restrictions 294
- Summary 295
- Practice exercise 295
- Chapter 10: Analyzing PL/SQL Code 299
- Track coding information 299
- [DBA | ALL | USER]_ARGUMENTS 301
- [DBA | ALL | USER]_OBJECTS 304
- [DBA | ALL | USER]_SOURCE 306
- [DBA | ALL | USER]_PROCEDURES 307
- [DBA | ALL | USER]_DEPENDENCIES 308
- Using SQL Developer to ind coding information 310
- The DBMS_DESCRIBE package 313
- DBMS_UTILITY.FORMAT_CALL_STACK 316
- Tracking propagating exceptions in PL/SQL code 318
- Determining identiier types and usages 319
- The PL/Scope tool 320
- The PL/Scope identiier collection 320
- The PL/Scope report 322
- Illustration 322
- Applications of the PL/Scope report 325
- The DBMS_METADATA package 326
- DBMS_METADATA data types and subprograms 327
- Parameter requirements 330
- The DBMS_METADATA transformation parameters and ilters 330
- Working with DBMS_METADATA—illustrations 332
- Case 1—retrieve the metadata of a single object 332
- Case 2—retrieve the object dependencies on the F_GET_LOC function 335
- Case 3—retrieve system grants on the ORADEV schema 335
- Case 4—retrieve objects of function type in the ORADEV schema 336
- Summary 337
- Practice exercise 337
- Chapter 11: Proiling and Tracing PL/SQL Code 339
- Tracing the PL/SQL programs 340
- The DBMS_TRACE package 341
- Installing DBMS_TRACE 341
- DBMS_TRACE subprograms 341
- The PLSQL_DEBUG parameter and the DEBUG option 343
- Viewing the PL/SQL trace information 344
- Demonstrating the PL/SQL tracing 347
- Proiling the PL/SQL programs 350
- Oracle hierarchical proiler—the DBMS_HPROF package 351
- View proiler information 352
- Demonstrating the proiling of a PL/SQL program 352
- The plshprof utility 357
- Sample reports 359
- Summary 361
- Practice exercise 361
- Chapter 12: Safeguarding PL/SQL Code against SQL
- Injection Attacks 365
- SQL injection—an introduction 366
- SQL injection—an overview 366
- Types of SQL injection attacks 369
- Preventing SQL injection attacks 369
- Immunizing SQL injection attacks 370
- Reducing the attacks surface 370
- Controlling user privileges 371
- Invokers and deiners rights 371
- Avoiding dynamic SQL 375
- Bind arguments 378
- Sanitizing inputs using DBMS_ASSERT 379
- The DBMS_ASSERT package 380
- Testing the code for SQL injection laws 386
- Test strategy 386
- Reviewing the code 386
- Static code analysis 387
- Fuzz tools 387
- Generating test cases 387
- Summary 388
- Practice exercise 388
- Appendix: Answers to Practice Questions 391
- Chapter 1, Overview of PL/SQL Programming Concepts 391
- Chapter 2, Designing PL/SQL Code 392
- Chapter 3, Using Collections 393
- Chapter 4, Using Advanced Interface Methods 394
- Chapter 5, Implementing VPD with Fine Grained Access Control 395
- Chapter 6, Working with Large Objects 396
- Chapter 7, Using SecureFile LOBs 397
- Chapter 8, Compiling and Tuning to Improve Performance 398
- Chapter 9, Caching to Improve Performance 400
- Chapter 10, Analyzing PL/SQL Code 401
- Chapter 11, Proiling and Tracing PL/SQL Code 401
- Chapter 12, Safeguarding PL/SQL Code against SQL Injection Attacks 402
