Advanced Oracle PL/SQL Developer's Guide - Second Edition.

Advanced Oracle PL/SQL Developer's Guide - Second Edition.

The OCP certification is the second milestone for the experienced associate level Oracle professionals which enhances one's credibility in work environment, and catalyzes your employment opportunities. The Packt's book Oracle Advanced PL/SQL Developer Professional Guide helps you to master...

Full description

Saved in:
Bibliographic Details
Main Author: Gupta, Saurabh K. (Author)
Format: eBook
Language:English
Published: Birmingham : Packt Publishing, Limited Feb. 2016.
Edition:2nd ed.
Subjects:
Oracle (Computer file)
PL/SQL.
Database design.
Database design
Online Access:Click for online access
Table of Contents:
  • Cover
  • Copyright
  • Credits
  • About the Author
  • About the Reviewers
  • www.PacktPub.com
  • Table of Contents
  • Preface
  • Chapter 1: Overview of PL/SQL Programming Concepts
  • Introduction to PL/SQL
  • PL/SQL program fundamentals
  • Cursors
  • an overview
  • The 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
  • Creating stored procedures
  • Executing a procedure
  • Functions
  • Functions
  • execution methods
  • Restrictions on calling functions from SQL expressions
  • A PL/SQL package
  • Oracle Database 12c enhancements to PL/SQL subprograms
  • Managing database dependencies
  • Displaying the direct and indirect dependencies
  • Dependency metadata
  • Dependency issues and enhancements
  • Reviewing Oracle-supplied packages
  • Oracle SQL Developer
  • Oracle SQL Developer for DBA, Developers, and Application Architects
  • SQL Developer 4.0
  • Summary
  • Practice exercise
  • Chapter 2: Oracle 12c SQL and PL/SQL New Features
  • Database consolidation and the new Multitenant architecture
  • The Oracle Database 12c Multitenant architecture
  • features
  • Multitenant for Consolidation
  • Plug/unplug
  • Manage Many as One
  • Rapid provisioning
  • CDB Resource Management
  • Common users and local users
  • Oracle 12c SQL and PL/SQL new features
  • IDENTITY columns
  • Default column value to a sequence in Oracle 12c
  • The DEFAULT ON NULL clause
  • Support for 32K VARCHAR2
  • Row limiting using FETCH FIRST
  • Invisible columns
  • Temporal databases
  • In-Database Archiving
  • Defining a PL/SQL subprogram in the SELECT query and PRAGMA UDF
  • Test setup
  • Comparative analysis
  • The PL/SQL program unit white listing
  • Granting roles to PL/SQL program units
  • Test setup.
  • Miscellaneous PL/SQL enhancements
  • The Oracle Database 12c (12.1.0.2) In-Memory option
  • The challenge
  • The problem statement and Oracle Database 12c In-Memory
  • Oracle Database 12c In-Memory option features
  • The Oracle Database 12c In-Memory Architecture
  • Controlling the In-Memory column store
  • The INMEMORY clause
  • Performance optimizations
  • In-Memory Advisor
  • Oracle Database In-Memory benefits
  • Summary
  • Chapter 3: Designing PL/SQL Code
  • Cursor structures
  • Cursor execution cycle
  • Cursor attributes
  • Implicit cursors
  • Explicit cursors
  • Cursor variables
  • Strong and weak ref cursor types
  • Working with cursor variables
  • SYS_REFCURSOR
  • Cursor variables as arguments
  • Cursor variables
  • restrictions
  • Cursor design considerations
  • Cursor design-guidelines
  • Implicit statement results in Oracle Database 12c
  • Subtypes
  • Subtype classification
  • Type compatibility with subtypes
  • Summary
  • Practice exercise
  • Chapter 4: Using Collections
  • Introduction to collections
  • Collection types
  • Associative arrays
  • Nested tables
  • Modify and drop a nested table object type
  • Design considerations of a nested table
  • Nested table storage
  • Nested table in an index
  • organized table
  • Nested table locators
  • Nested table as the schema object
  • Operations on a nested table type column
  • Nested table collection type in PL/SQL
  • Querying the nested table metadata
  • Nested table comparison functions
  • Multiset operations on nested tables
  • Varray
  • Varray as a schema object
  • Operations on varray type columns
  • Varray in PL/SQL
  • Comparing the collection types
  • Selecting the appropriate collection type
  • Oracle 12c enhancements to collections
  • PL/SQL collection methods
  • EXISTS
  • COUNT
  • LIMIT
  • FIRST and LAST
  • PRIOR and NEXT
  • EXTEND
  • TRIM
  • DELETE
  • Summary
  • Practice exercise.
  • Chapter 5: Using Advanced Interface Methods
  • Overview of External Procedures
  • External Procedures
  • Components of external procedure execution flow
  • The extproc agent
  • The Library object
  • Callout and Callback
  • Call Specification
  • How an External Procedure executes
  • Environment setup
  • TNSNAMES.ora
  • EXTPROC.ora
  • Executing external C programs from PL/SQL
  • Securing External Procedures with Oracle Database 12c
  • Executing Java programs from PL/SQL
  • Loading a Java class into a database
  • Steps to execute a Java class from an Oracle PL/SQL unit
  • Summary
  • Practice exercise
  • Chapter 6: Virtual Private Database
  • Oracle Database Security overview
  • Fine-Grained Access Control
  • How FGAC works
  • Virtual Private Database
  • How does Virtual Private Database work?
  • Column-level Virtual Private Database
  • Virtual Private Database with Oracle Database 12c Multitenant
  • Virtual Private Database components
  • Application Context
  • Virtual Private Database policy function
  • Policy types
  • The DBMS_RLS package
  • Demonstration
  • Virtual Private Database features and best practices
  • Virtual Private Database metadata
  • Policy utilities-refresh and drop
  • Oracle Database 12c Security enhancements
  • Oracle Database 12c Data Redaction
  • Data Redaction exemptions and miscellaneous features
  • Data Redaction function types
  • Demonstration
  • The Data Redaction metadata
  • Summary
  • Practice exercise
  • Chapter 7: Oracle SecureFiles
  • Introduction to Large Objects
  • Classification of Large Object datatypes
  • Internal LOB
  • External LOB
  • LOB restrictions
  • LOB data types in Oracle
  • BLOB and CLOB
  • BFILE
  • Some more related stuff
  • The LOB locator
  • LOB instance initialization
  • The DBMS_LOB package
  • LOB usage notes
  • Oracle SecureFiles
  • Deduplication and compression
  • Encryption
  • File System Logging.
  • Write Gather Cache
  • Free space management
  • BasicFiles and SecureFiles
  • The db_securefile parameter
  • Working with LOBs
  • LOB metadata
  • Enabling the advanced features of a SecureFile
  • Populating the LOB data
  • Temporary LOB operations
  • Managing temporary LOBs
  • Working with a temporary LOB
  • Migrating LONG to LOBs
  • Use the ALTER TABLE command
  • Using the TO_LOB function
  • Online Table Redefinition
  • Migrating BasicFiles to SecureFiles
  • Oracle Database 12c enhancements to SecureFiles
  • Summary
  • Practice exercise
  • Chapter 8: Tuning PL/SQL Code
  • The PL/SQL Compiler
  • Subprogram inlining in PL/SQL
  • PRAGMA INLINE
  • PLSQL_OPTIMIZE_LEVEL
  • Native and interpreted compilation techniques
  • Oracle Database 11g Real Native Compilation
  • Selecting the appropriate compilation mode
  • Setting the compilation mode
  • Querying the compilation settings
  • Compiling a program unit for native or interpreted compilation
  • Recompiling a database for a PL/SQL native or interpreted compilation
  • Tuning PL/SQL code
  • Build secure applications using bind variables
  • Call parameters by reference
  • Avoiding an implicit data type conversion
  • Understanding the NOT NULL constraint
  • Selection of an appropriate numeric data type
  • Bulk processing in PL/SQL
  • BULK COLLECT
  • FORALL
  • Summary
  • Practice exercise
  • Chapter 9: Result Cache
  • Oracle Database 11g Result Cache
  • What is the Server Result Cache?
  • Configuring the Server Result Cache
  • Result Cache versus Buffer Cache
  • Result Cache versus Oracle 12c Database In-Memory
  • Result Cache versus In-Memory Database Cache
  • SQL query Result Cache
  • Monitoring the SQL Result Cache
  • Invalidation of the SQL Result Cache
  • Read consistency of the SQL Result Cache
  • Limitations
  • PL/SQL Function Result Cache
  • Does it sound similar to deterministic functions?
  • Differences between Result Cache and other caching techniques
  • Illustration
  • Monitoring the PL/SQL Result Cache
  • Invalidation of the PL/SQL Result Cache
  • Limitation
  • OCI Client results cache
  • The DBMS_RESULT_CACHE package
  • Displaying the result cache memory report
  • Oracle Database 12c enhancements to the PL/SQL function Result Cache
  • Result cache in Real Application Clusters
  • Summary
  • Practice exercise
  • Chapter 10: Analyzing, Profiling, and Tracing PL/SQL Code
  • A sample PL/SQL program
  • Tracking PL/SQL coding information
  • USER_ARGUMENTS
  • USER_OBJECTS
  • USER_OBJECT_SIZE
  • USER_SOURCE
  • USER_PROCEDURES
  • USER_PLSQL_OBJECT_SETTINGS and USER_STORED_SETTINGS
  • USER_DEPENDENCIES
  • The DBMS_DESCRIBE package
  • Tracking the program execution subprogram call stack
  • Tracking propagating exceptions in PL/SQL code
  • Determining identifier types and usages
  • USER_IDENTIFIERS
  • The PL/Scope tool
  • The PLSCOPE_SETTINGS parameter
  • The DBMS_METADATA package
  • DBMS_METADATA data types and subprograms
  • Parameter requirements
  • The DBMS_METADATA transformation parameters and filters
  • Demonstration
  • Tracing PL/SQL programs using DBMS_TRACE
  • Installing the DBMS_TRACE package
  • DBMS_TRACE subprograms
  • Compiling a PL/SQL program for debugging
  • Viewing the PL/SQL trace information
  • Steps to trace PL/SQL program execution
  • Profiling PL/SQL code
  • The DBMS_HPROF package
  • Differences between DBMS_PROFILER and DBMS_HPROF
  • DBMS_HPROF subprograms
  • Collecting raw profile data
  • Interpreting the raw profiler data
  • Analyzing profiler data
  • Creating the profiler tables
  • Analyzing the profiler output
  • Querying the profiler tables
  • The plshprof utility
  • What do these reports reveal?
  • Summary
  • Practice exercise
  • Chapter 11: Safeguarding PL/SQL Code against SQL injection
  • What is SQL injection?

Similar Items