Advanced Oracle PL/SQL Developer's Guide : Master the Advanced Concepts of PL/SQL for Professional-Level Certification and Learn the New Capabilities of Oracle Database 12c.

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?.

SQL injection targets.

Description based on publisher supplied metadata and other sources.

Electronic reproduction. Ann Arbor, Michigan : ProQuest Ebook Central, 2024. Available via World Wide Web. Access may be limited to ProQuest Ebook Central affiliated libraries.