Oracle PL/SQL advanced programming

Knowledge of the basics of the SQL language in terms of queries, as well as DML, DDL and TCL commands, as well as the ability to interpret and create simple PL/SQL scripts.

Level
Advanced
Form
Lecture, Presentation, Workshop, Problem solving
Purpose of the training
The training is intended for programmers who know the basics of SQL and PL/SQL, but want to learn more advanced mechanisms of the language, and want to create database applications based on stored PL/SQL programs.
Benefits of completing the training
Acquisition of skills in creating database applications embedded in a database.

Create stored subroutines

• Compilation, object status, source codes
• Code encryption (wrap)
• Object dependency model
• Parameterization

Permissions and schemas in PL/SQL

• Owner and caller model
• Direct and role permissions
• Whitelist
• Assigning roles to procedures and inheriting permissions

Advanced Cursors

• Comparison of explicit cursors and cursor loops
• Pointers for weak and strong cursors
• Support for dynamic cursors
• FOR UPDATE cursors

Collections

• Associative tables (index by), nested tables and varrays
• Attributes, handling, initialization, memory allocation
• Bulk queries (BULK COLLECT) and bulk DML (FORALL)
• Bulk operations in dynamic SQL
• Exception handling in bulk operations
• Array and stream functions

Packages

• Package structure: specification and body
• Benefits of using packages
• Initialization section and global variables, memory management, package instance

Triggers

• DML triggers
• View triggers (INSTEAD OF)
• System Triggers
• Composite triggers

Application context and VPD

• Using built-in context
• Create your own private and global contexts
• Saving and reading context values
• Policies and Virtual Private Database

Appendix A - Examples of using embedded packages

• File handling (utl_file)
• Job scheduling (dbms_job and dbms_scheduler

Suggested pre-training
ORA_S1, ORA_P1
Suggested complementary training
ORA_SP3