Oracle Database 11g: Advanced PL/SQL Programming & Tuning Course

Upon completion of this course, the participant will be able to:

> Invoking external procedures and integrating these into PL/SQL applications. These include external Java classes using the JDBC interface and external C programs contained within DLL libraries
> Exploiting and managing cursor sharing and bind variables to maximize embedded SQL execution performance
> Obtain the performance benefits that come from employing SQL and PL/SQL result caching
> Using dynamic SQL to extend the functionality and flexibility of database programs, including the DBMS_SQL() system-supplied package for maximum flexibility
> Identifying SQL injection attack vulnerabilities within an application and applying countermeasures to address security risks and protect against hacking
> Incorporating collections and other advanced types into application logic to increase efficiency and execution speed
> Working with LOBs, including piece-wise data manipulation and dynamic modification of SecureFile storage options
> Expanding functionality with system-supplied database utility packages
> Tuning with the DBMS_PROFILER() system-supplied package and debugging with the DBMS_TRACE() system-supplied package
> Writing efficient PL/SQL code and avoiding common coding mistakes
> Enabling native compilation and execution of all database-resident program units
> Controlling and managing PL/SQL compilation for high-efficiency execution
> Analyze PL/SQL code structure by means of the PL/Scope facility
> Analyze PL/SQL application performance and tune bottlenecks using the PL/SQL Hierarchical Profiler
> Recognizing the dangers of SQL injection attacks against PL/SQL applications and applying countermeasures to this potential security threat from hackers
> Implementing fine-grained security mechanisms as part of an advanced security model using application contexts and the Oracle virtual private database (VPD)

Exploiting & Managing Cursor Sharing
About Cursor Sharing
Bind Variables & Cursor Peeking
Using The Cursor_Sharing Parameter

Using The Database Result Cache
About The Result Cache
Configuring The Result Cache
Result_Cache_Max_Size
Result_Cache_Mode
Result_Cache_Max_Result
Result_Cache_Remote_Expiration
Using The Result Cache
Managing & Monitoring The Result Cache
Using V$Result_Cache_Statistics
V$Result_Cache_Memory
V$Result_Cache_Objects
V$Result_Cache_Dependency

Dynamic SQL
Advantages & Disadvantages Of Dynamic SQL
Native Dynamic SQL
Dynamic Update...Returning
Programmatic Dynamic SQL Using DBMS_SQL()

Using Collections
Bulk Bind Using Collections
About SQL%Bulk_Rowcount()
About SQL%Bulk_Exceptions()
Collection Methods
More About The Returning Clause
Returning Clause With Multiple Rows
Returning With A Summary Function
Advanced Collection Features
In Indices Of Clause
In Values Of Clause

System-Supplied Packages: DBMS_Metadata() – Part I
Why Retrieve Object Definitions?
Retrieving Default Metadata
Using Get_Ddl()
Retrieving Customized Metadata
Using Open() & Close()
Using Set_Filter()
Using Set_Count()
Using Add_Transform()
Using Fetch DDL()
About Sys.Ku$_DDL
About Sys.Ku$_DDLs
Calling Fetch_DDL()

System-Supplied Packages: DBMS_Metadata() – Part II
Set_Transform_Param()
Get_Query()

System-Supplied Packages: DBMS_Metadata() – Part III
Fetch Clob()
Using Set_Filter() For Dependent Objects
Set_Parse_Item()
Retrieving Primary & Dependent Object DDL

System-Supplied Packages: DBMS_Redefinition()
Using The DBMS_Redefinition() Package
DBA_Redefinition_Errors
Can_Redef_Table()
Start_Redef_Table()
Finish_Redef_Table()
Abort_Redef_Table()
Copy_Table_Dependents()
Sync_Interim_Table()

System-Supplied Packages:
DBMS_LOB()
Working With External Bfiles
Working With Internal LOBs
Substr()
Instr()
Dynamically Setting SecureFile Options

System-Supplied Packages: Others
Lob Compression with UTL_Compress()
LZ_Compress()
LZ_Uncompress()
DBMs_Describe()
UTL_Mail()

Advanced Interface Methods
About External Procedures
Calling Java Classes
Calling C Programs

PL/SQL Advanced Programming & Coding Techniques
Autonomous Transactions
Using Nocopy For Parameters
Choosing The Optimum Data Type
About PLS_Integer
Using Simple_Integer
Char vs. Varchar2
Useful PL/SQL Coding Techniques
Handling String Literals

Influencing Oracle PL/SQL Compilation
Pl/SQL Compiler Optimization
PlSQL_Optimize_Level
Controlling Compilation Messages
Pl/SQL Native Execution

Using PL/Scope
Configuring Pl/Scope
PLscope_Settings
Using PL/Scope Data
Workshop Section
Using Pl/Scope

Application Tuning With The Pl/SQL Hierarchical Profiler
What Is The Hierarchical Profiler?
Configuring The Profiler
Managing Profiler Runs
Analyzing Profiler Data
Interpreting The Results
DBMshp_Runs
DBMshp_Function_Info
DBMshp_Parent_Child_Info

PL/SQL Debugging With DBMs_Trace()
Using The Trace Facility
Using DBMs_Trace() To Manage Runs
Examining The Trace Data

Protecting Against SQL Injection Attacks
Understanding The Threat
Applying Countermeasures

Implementing Virtual Private Databases
Understanding VPDs
Preparing For A VPD
Configuring A VPD
Managing Application Contexts
Managing Policies & Security Rules