Oracle PL/SQL Programming - Advanced Level

Oracle PL/SQL Programming - Advanced Level

This course in the progressive level to learn about PL/SQL programming for Oracle databases. It provides more aspects to write PL/SQL block of codes in a more powerful protocols. Moreover, it delivers the needed knowledge of going beyond coding by tuning, maintaining and protecting PL/SQL programs

Course Outline

  • Database Triggers
  • Create triggers on DML and DDL statements
  • Statement-Level vs Row-Level Triggers
  • Create triggers on system events
  • Describe different types of triggers and their uses

 

Using the PL/SQL Compiler

  • Describe the PL/SQL compiler and features
  • Use the PL/SQL compiler initialization parameters
  • Use the PL/SQL compile time warnings

 

Managing PL/SQL Code

  • Describe and use conditional compilation
  • Code-based access control: granting roles to program units
  • Whitelist code access with the ACCESSIBLE BY clause
  • Mark code as deprecated

 

Designing PL/SQL Code

  • Identify guidelines for cursor design
  • Use cursor variables
  • Create subtypes based on existing types

 

Working with Collections

  • Create collections (nested table, array and associative arrays, PL SQL tables)
  • Use collections methods
  • Manipulate collections
  • Distinguish between the different types of collections and their uses

 

Manipulating Large Objects

  • Create and manage LOB data types
  • Use the DBMS_LOB PL/SQL package
  • Use of temporary LOBs
  • Administering SecureFile LOBs
  • Describe SecureFile LOB features
  • Enable SecureFile LOB deduplication, compression, and encryption
  • Migrate BasicFile LOBs to the SecureFile LOB format

 

Improving Performance with Caching

  • Improve memory usage by caching SQL result sets and using the DBMS_RESULT_CACHE package
  • Write queries that use the result cache hint
  • Set up PL/SQL functions to use PL/SQL result caching

 

Analyzing PL/SQL Code

  • Run reports on source code
  • Determine identifier types and usages
  • Use DBMS_METADATA to retrieve object definitions

 

SQL Injection Attacks

  • Describe SQL injections
  • Reduce attack surfaces
  • Use DBMS_ASSERT
  • Design immune code
  • Test code for SQL injection flaws

Course Enquiry