Oracle Database 12c: PL/SQL III - Advanced Programming and Tuning

Customized Onsite Training

5
Days
  • Customized Content
  • For Groups of 5+
  • Online or On-location
  • Expert Instructors

Live Online Training

$2,625.00
or 0 vouchers
  • Live Online Training
  • Expert Instructors
  • Guaranteed to Run
  • 100% Free Re-take Option
  • 1-minute Video

Upcoming Classes

  • See More Classes

Please select a class.
Overview

This Oracle Database 12c: PL/SQL III - Advanced Programming and Tuning training class presents the three fundamental pillars of effective implementation of PL/SQL applications. First, we will explore the advanced features of the language that allow powerful and adaptable database applications to be built. Next, we will discuss performance tuning techniques that allow these applications to run efficiently. Finally, we will consider critical security measures that should be implemented to counter hacker attacks and other security threats.

The target audience for this textbook is senior application developers and developers who will be building, debugging, and tuning PL/SQL program units.

Goals
  1. Learn external Java classes using the JDBC interface and external C programs contained within DLL.
  2. Learn to work with libraries.
  3. Learn to use dynamic SQL to extend the functionality and flexibility of database programs.
  4. Learn to work with the DBMS_SQL() system-supplied package for maximum flexibility.
  5. Learn to identify SQL injection attack vulnerabilities within an application.
  6. Learn countermeasures to address security risks and protect against hacking.
  7. Learn to incorporate collections and other advanced types into application logic to increase efficiency.
  8. Learn to work with LOBs, including piece-wise data manipulation and dynamic modification of SecureFiles storage options.
  9. Learn to expand database application functionality with advanced system-supplied database utility.
  10. Learn to create packages, integrating one's applications with external mail systems, database internals, and other facilities.
  11. Learn to tune with the DBMS_PROFILER() system-supplied package and debug with the.dBMS_TRACE() system-supplied package.
  12. Write efficient PL/SQL code and avoid common coding mistakes.
  13. Learn to enable native compilation and execute all database-resident program units.
  14. Learn to control and manage PL/SQL compilation for high-efficiency execution.
  15. Learn to analyze PL/SQL code structure by means of the PL/Scope facility.
  16. Learn to analyze PL/SQL application performance and tune bottlenecks using the PL/SQL Hierarchical.
  17. Learn to implement fine-grained security mechanisms as part of an advanced security model using application contexts and the Oracle virtual private database (VPD).
  18. Learn partitioning and DML parallelization using the system-supplied package dBMS_PARALLEL_EXECUTE().
  19. Learn to use the wrap utility to hide the source code of database-resident programs, even from the owner or authorized users of the programs.
Outline
  1. Advanced Programming: Why Needed and PL/SQL Execution: Internals
    1. Why Advanced Programming?
    2. SQL and PL/SQL Execution Internals
    3. SQL and PL/SQL PGA Internals
  2. Advanced Programming: Dynamic SQL
    1. Advantages Of Dynamic SQL
    2. Native Dynamic SQL
    3. Dynamic SQL Using DBMS_SQL()
  3. Advanced Programming: Using Collections
    1. Bulk Bind Using Collections
    2. About SQL%BULK_ROWCOUNT()
    3. About SQL%BULK_EXCEPTIONS()
    4. Collection Methods
    5. More About RETURNING Clause
    6. Advanced Collection Features
    7. IN INDICES OF Clause
    8. IN VALUES OF Clause
  4. Advanced Programming: Java and C Interface Methods
    1. Advanced Program Interfaces
    2. Calling Java Classes
    3. Calling C Programs
    4. System-Supplied Packages: DBMS_METADATA() – Part I
    5. Why Retrieve Object Definitions?
    6. Retrieving Default Metadata
    7. Retrieving Customized Metadata
    8. Using SET_COUNT()
    9. Using ADD_TRANSFORM()
    10. Using FETCH DDL()
    11. Calling FETCH_DDL()
  5. System-Supplied Packages: DBMS_METADATA() – Part II
    1. SET_TRANSFORM_PARAM()
    2. GET_QUERY()
  6. System-Supplied Packages: DBMS_METADATA() – Part III
    1. FETCH CLOB()
    2. SET_FILTER() Dependent Objects
    3. SET_PARSE_ITEM()
    4. Primary and Dependent Object DDL
  7. System-Supplied Packages: DBMS_REDEFINITION()
    1. About Table Redefinition
    2. Using DBMS_REDEFINITION()
    3. DBA_REDEFINITION_ERRORS
    4. CAN_REDEF_TABLE()
    5. START_REDEF_TABLE()
    6. FINISH_REDEF_TABLE()
    7. ABORT_REDEF_TABLE()
    8. COPY_TABLE_DEPENDENTS()
    9. SYNC_INTERIM_TABLE()
  8. System-Supplied Packages: DBMS_LOB()
    1. Working With External BFILES
    2. Working With Internal LOBS
    3. SUBSTR()
    4. INSTR()
    5. Dynamic SECUREFILE Options
  9. High-Performance: Advanced System-Supplied Packages
    1. COMPRESSION and UTL_COMPRESS()
    2. LZ_COMPRESS()
    3. LZ_UNCOMPRESS()
    4. DBMS_DESCRIBE()
    5. UTL_MAIL()
    6. DBMS_UTILITY()
    7. COMPILE_SCHEMA()
    8. DB_VERSION()
    9. GET_PARAMETER_VALUE()
    10. WAIT_ON_PENDING_DML()
    11. GET_TIME()
    12. GET_ENDIANNESS()
    13. DBMS_FILE_TRANSFER()
  10. High Performance: Programming and Coding Techniques
    1. Autonomous Transactions
    2. Using NOCOPY FOR Parameters
    3. Choosing The Optimum Data Type
    4. About NOT NULL
    5. Useful PL/SQL Coding Techniques
    6. Handling String Literals
    7. User-Defined SQL Functions
  11. High Performance: Influencing Oracle Pl/Sql Compilation
    1. PL/SQL Compiler Optimization
    2. PLSQL_OPTIMIZE_LEVEL
    3. Controlling Compilation Messages
    4. PL/SQL Native Execution
    5. Wrapping Source Code
  12. High Performance: Dynamic Partitioning and Parallelization
    1. Dynamic Partitioning (Chunks)
    2. Creating and Processing Chunks
    3. CREATE_TASK()
    4. CREATE_CHUNKS_BY_ROWID()
    5. CREATE_CHUNKS_BY_NUMBER_COL()
    6. EXECUTE_RUN_TASK()
    7. TASK_STATUS()
    8. DROP_TASK()
    9. Monitoring Chunk Processing
  13. High Performance: Using PL/SCOPE For Code Analysis
    1. Configuring PL/SCOPE
    2. PLSCOPE_SETTINGS
    3. Using PL/SCOPE Data
  14. High Performance: Tuning With The Hierarchical Profiler
    1. What Is The Hierarchical Profiler?
    2. Configuring The Profiler
    3. Managing Profiler Runs
    4. Analyzing Profiler Data
    5. Interpreting The Results
    6. DBMSHP_RUNS
    7. DBMSHP_FUNCTION_INFO
    8. DBMSHP_PARENT_CHILD_INFO
  15. High Performance: Debugging With DBMS_TRACE()
    1. Using The Trace Facility
    2. DBMS_TRACE() To Manage Runs
    3. Examining The Trace Data
    4. EVENT_KIND Values
  16. Application Security: SQL Injection Attacks
    1. Understanding The Threat
    2. Applying Countermeasures
  17. Application Security: Virtual Private Databases
    1. Understanding VPDS
    2. Preparing For A VPD
    3. Configuring A VPD
    4. Managing Application Contexts
    5. Managing Policies and Security Rules
Class Materials

Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Class Prerequisites

Experience in the following is required for this Oracle class:

  • Oracle SQL and PL/SQL
Prerequisite Courses

Courses that can help you meet these prerequisites:

Preparing for Class

No cancelation for low enrollment

Certified Microsoft Partner

Registered Education Provider (R.E.P.)

GSA schedule pricing

74,839

Students who have taken Live Online Training

15,233

Organizations who trust Webucator for their training needs

100%

Satisfaction guarantee and retake option

9.41

Students rated our trainers 9.41 out of 10 based on 5,189 reviews

Contact Us or call 1-877-932-8228