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