Oracle Advanced PL/SQL Developer Professional Guide. Master advanced PL/SQL concepts along with plenty of example questions for 1Z0-146 examination with this book and Saurabh K. Gupta

Spis treści książki

• Oracle Advanced PL/SQL Developer Professional Guide
  • Table of Contents
  • Oracle Advanced PL/SQL Developer Professional Guide
  • Credits
  • Foreword
  • About the Author
  • Acknowledgement
  • About the Reviewers
  • www.PacktPub.com
    • Support files, eBooks, discount offers, and more
      • Why Subscribe?
      • Free Access for Packt account holders
      • Instant Updates on New Packt Books
  • Preface
    • What this book covers
    • What you need for this book
    • Who this book is for
    • Conventions
    • Reader feedback
    • Customer support
      • Downloading the example code
      • Errata
      • Piracy
      • Questions
  • 1. Overview of PL/SQL Programming Concepts
    • PL/SQLthe procedural aspect
      • My first PL/SQL program
    • PL/SQL development environments
      • SQL Developer
        • SQL Developerthe history
        • Creating a connection
        • SQL Worksheet
        • Executing a SQL statement
        • Calling a SQL script from SQL Developer
        • Creating and executing an anonymous PL/SQL block
        • Debugging the PL/SQL code
        • Editing and saving the scripts
      • SQL*Plus
        • Executing a SQL statement in SQL*Plus
        • Executing an anonymous PL/SQL block
    • Procedures
      • Executing a procedure
    • Functions
      • Functionexecution methods
      • Restrictions on calling functions from SQL expressions
    • PL/SQL packages
    • Cursorsan overview
      • Cursor execution cycle
      • Cursor attributes
      • Cursor FOR loop
    • Exception handling in PL/SQL
      • System-defined exceptions
      • User-defined exceptions
        • The RAISE_APPLICATION_ERROR procedure
      • Exception propagation
    • Managing database dependencies
      • Displaying the direct and indirect dependencies
      • Dependency metadata
      • Dependency issues and enhancements
    • Reviewing Oracle-supplied packages
    • Summary
    • Practice exercise
  • 2. Designing PL/SQL Code
    • Understanding cursor structures
      • Cursor execution cycle
      • Cursor design considerations
      • Cursor designguidelines
      • Cursor attributes
      • Implicit cursors
      • Explicit cursors
    • Cursor variables
      • Ref cursor typesstrong and weak
        • SYS_REFCURSOR
      • Processing a cursor variable
      • Cursor variables as arguments
      • Cursor variablesrestrictions
    • Subtypes
      • Subtype classification
        • Oracles predefined subtypes
        • User-defined subtypes
      • Type compatibility with subtypes
    • Summary
    • Practice exercise
  • 3. Using Collections
    • Collectionsan overview
      • Categorization
      • Selecting an appropriate collection type
    • Associative arrays
    • Nested tables
      • Nested table collection type as the database object
        • DML operations on nested table columns
          • Inserting a nested table instance
          • Selecting a nested table column
          • Updating the nested table instance
        • A nested table collection type in PL/SQL
      • Additional features of a nested table
    • Varray
      • Varray in PL/SQL
      • Varray as a database collection type
        • DML operations on varray type columns
          • Inserting a varray collection type instance
          • Selecting a varray column
          • Updating the varray instance
    • Collectionsa comparative study
      • Common characteristics of collection types
      • Nested table versus associative arrays
      • Nested table versus varrays
    • PL/SQL collection methods
      • EXISTS
      • COUNT
      • LIMIT
      • FIRST and LAST
      • PRIOR and NEXT
      • EXTEND
      • TRIM
      • DELETE
    • Manipulating collection elements
    • Collection initialization
    • Summary
    • Practice exercise
  • 4. Using Advanced Interface Methods
    • Understanding external routines
      • Architecture of external routines
      • Oracle Net Configuration
        • TNSNAMES.ora
        • LISTENER.ora
        • Oracle Net Configuration verification
      • Benefits of external procedures
    • Executing external C programs from PL/SQL
      • Executing C program through external proceduredevelopment steps
    • Executing Java programs from PL/SQL
      • Calling a Java class method from PL/SQL
      • Uploading a Java class into the databasedevelopment steps
      • The loadjava utilityan illustration
      • Creating packages for Java class methods
    • Summary
    • Practice exercise
  • 5. Implementing VPD with Fine Grained Access Control
    • Fine Grained Access Control
      • Overview
      • Virtual Private Databasethe alias
      • How FGAC or VPD works?
        • Salient features of VPD
    • VPD implementationoutline and components
      • Application context
      • Policy function definition and implementation of row-level security
      • Associating a policy using the DBMS_RLS package
    • VPD implementationdemonstrations
      • Assignment 1implementing VPD using simple security policy
      • Assignment 2implementing VPD using an application context
    • VPD policy metadata
    • Policy utilitiesrefresh and drop
    • Summary
    • Practice exercise
  • 6. Working with Large Objects
    • Introduction to the LOB data types
      • Internal LOB
      • External LOB
    • Understanding the LOB data types
      • LOB value and LOB locators
      • BLOB or CLOB!
      • BFILE
      • Temporary LOBs
    • Creating LOB data types
      • Directories
      • Creating LOB data type columns in a table
    • Managing LOB data types
      • Managing internal LOBs
      • Securing and managing BFILEs
      • The DBMS_LOB packageoverview
        • Security model
        • DBMS_LOB constants
        • DBMS_LOB data types
        • DBMS_LOB subprograms
        • Rules and regulations
          • Internal LOBs
          • BFILEs
    • Working with the CLOB, BLOB, and BFILE data types
      • Initializing LOB data type columns
      • Inserting data into a LOB column
      • Populating a LOB data type using an external file
      • Selecting LOB data
      • Modifying the LOB data
      • Delete LOB data
      • Miscellaneous LOB notes
        • LOB column states
        • Locking a row containing LOB
        • Opening and closing LOBs
        • Accessing LOBs
        • LOB restrictions
    • Migrating from LONG to LOB
    • Using temporary LOBs
      • Temporary LOB operations
      • Managing temporary LOBs
      • Validating, creating, and freeing a temporary LOB
    • Summary
    • Practice exercise
  • 7. Using SecureFile LOBs
    • Introduction to SecureFiles
      • SecureFile LOBan overview
        • Architectural enhancements in SecureFiles
      • SecureFile LOB features
    • Working with SecureFiles
      • SecureFile metadata
      • Enabling advanced features in SecureFiles
        • Deduplication
        • Compression
        • Encryption
    • Migration from BasicFiles to SecureFiles
      • Online Reefinition method
    • Summary
    • Practice exercise
  • 8. Compiling and Tuning to Improve Performance
    • Native and interpreted compilation techniques
      • Real native compilation
      • Selecting the appropriate compilation mode
        • When to choose interpreted compilation mode?
        • When to choose native compilation mode?
      • Setting the compilation mode
      • Querying the compilation settings
      • Compiling a program unit for a native or interpreted compilation
      • Compiling the database for PL/SQL native compilation (NCOMP)
    • Tuning PL/SQL code
      • Comparing SQL and PL/SQL
      • Avoiding implicit data type conversion
      • Understanding the NOT NULL constraint
      • Using the PLS_INTEGER data type for arithmetic operations
      • Using a SIMPLE_INTEGER data type
      • Modularizing the PL/SQL code
      • Using bulk binding
        • Using SAVE_EXCEPTIONS
      • Rephrasing the conditional control statements
        • Conditions with an OR logical operator
        • Conditions with an AND logical operator
    • Enabling intra unit inlining
      • PLSQL_OPTIMIZE_LEVELthe Oracle initialization parameter
        • Case 1PLSQL_OPTIMIZE_LEVEL = 0
        • Case 2PLSQL_OPTIMIZE_LEVEL = 1
        • Case 3PLSQL_OPTIMIZE_LEVEL = 2
        • Case 4PLSQL_OPTIMIZE_LEVEL = 3
    • PRAGMA INLINE
    • Summary
    • Practice exercise
  • 9. Caching to Improve Performance
    • Introduction to result cache
      • Server-side result cache
        • SQL query result cache
        • PL/SQL function result cache
      • OCI client results cache
    • Configuring the database for the server result cache
      • The DBMS_RESULT_CACHE package
    • Implementing the result cache in SQL
      • Manual result cache
      • Automatic result cache
      • Result cache metadata
        • Query result cache dependencies
        • Cache memory statistics
      • Invalidation of SQL result cache
      • Displaying the result cache memory report
      • Read consistency of the SQL result cache
      • Limitation of SQL result cache
    • Implementing result cache in PL/SQL
      • The RESULT_CACHE clause
      • Cross-session availability of cached results
      • Invalidation of PL/SQL result cache
      • Limitations of PL/SQL function result cache
        • Argument and return type restrictions
        • Function structural restrictions
    • Summary
    • Practice exercise
  • 10. Analyzing PL/SQL Code
    • Track coding information
      • [DBA | ALL | USER]_ARGUMENTS
      • [DBA | ALL | USER]_OBJECTS
      • [DBA | ALL | USER]_SOURCE
      • [DBA | ALL | USER]_PROCEDURES
      • [DBA | ALL | USER]_DEPENDENCIES
    • Using SQL Developer to find coding information
      • The DBMS_DESCRIBE package
      • DBMS_UTILITY.FORMAT_CALL_STACK
      • Tracking propagating exceptions in PL/SQL code
    • Determining identifier types and usages
      • The PL/Scope tool
        • The PL/Scope identifier collection
        • The PL/Scope report
        • Illustration
        • Applications of the PL/Scope report
    • The DBMS_METADATA package
      • DBMS_METADATA data types and subprograms
      • Parameter requirements
      • The DBMS_METADATA transformation parameters and filters
      • Working with DBMS_METADATAillustrations
        • Case 1retrieve the metadata of a single object
        • Case 2retrieve the object dependencies on the F_GET_LOC function
        • Case 3retrieve system grants on the ORADEV schema
        • Case 4retrieve objects of function type in the ORADEV schema
    • Summary
    • Practice exercise
  • 11. Profiling and Tracing PL/SQL Code
    • Tracing the PL/SQL programs
      • The DBMS_TRACE package
        • Installing DBMS_TRACE
        • DBMS_TRACE subprograms
      • The PLSQL_DEBUG parameter and the DEBUG option
      • Viewing the PL/SQL trace information
      • Demonstrating the PL/SQL tracing
    • Profiling the PL/SQL programs
      • Oracle hierarchical profilerthe DBMS_HPROF package
        • View profiler information
        • Demonstrating the profiling of a PL/SQL program
      • The plshprof utility
        • Sample reports
    • Summary
    • Practice exercise
  • 12. Safeguarding PL/SQL Code against SQL Injection Attacks
    • SQL injectionan introduction
      • SQL injectionan overview
      • Types of SQL injection attacks
      • Preventing SQL injection attacks
    • Immunizing SQL injection attacks
      • Reducing the attack's surface
        • Controlling user privileges
        • Invoker's and definer's rights
      • Avoiding dynamic SQL
      • Bind arguments
      • Sanitizing inputs using DBMS_ASSERT
        • The DBMS_ASSERT package
          • Identifier formatting and verification process
          • DBMS_ASSERTusage guidelines
          • DBMS_ASSERTlimitations
    • Testing the code for SQL injection flaws
      • Test strategy
      • Reviewing the code
      • Static code analysis
      • Fuzz tools
      • Generating test cases
    • Summary
    • Practice exercise
  • A. Answers to Practice Questions
    • Chapter 1, Overview of PL/SQL Programming Concepts
    • Chapter 2, Designing PL/SQL Code
    • Chapter 3, Using Collections
    • Chapter 4, Using Advanced Interface Methods
    • Chapter 5, Implementing VPD with Fine Grained Access Control
    • Chapter 6, Working with Large Objects
    • Chapter 7, Using SecureFile LOBs
    • Chapter 8, Compiling and Tuning to Improve Performance
    • Chapter 9, Caching to Improve Performance
    • Chapter 10, Analyzing PL/SQL Code
    • Chapter 11, Profiling and Tracing PL/SQL Code
    • Chapter 12, Safeguarding PL/SQL Code against SQL Injection Attacks
  • Index

pokaż cały spis treści

ukryj recenzje