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