Oracle: Programming with PL/SQL I

• Basic proficiency in any programming language (structured or object-oriented)
• Familiarity with SQL fundamentals and the Oracle database environment

Audience

• Developers
• Database administrators

PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's proprietary procedural extension to SQL. It is a powerful programming language designed to work seamlessly with the Oracle Database, enabling users to create sophisticated database-driven applications.

This instructor-led, live training (online or onsite) is aimed at beginner-level developers and database administrators who wish to learn PL/SQL to automate database tasks, implement business logic, and develop robust database applications.

By the end of this training, participants will be able to:

• Develop efficient PL/SQL programs to automate database operations and integrate SQL commands effectively.
• Create reusable program units, including procedures, functions, packages, and triggers, for modular and scalable applications.
• Implement advanced data structures like associative arrays and manage query results using cursors.
• Handle errors robustly and secure code with encryption, obfuscation, and conditional compilation techniques.
• Apply PL/SQL in real-world scenarios, leveraging built-in packages for file handling, email automation, and other advanced functionalities.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Introduction to PL/SQL

• Overview of the PL/SQL runtime environment
• Structure and types of PL/SQL blocks
• Declaring and utilizing variables
• Control structures: conditionals and loops

Incorporating SQL within PL/SQL

• Executing DML commands
• Utilizing DDL and dynamic SQL
• Transaction control commands (TCL)
• Performing data retrieval with SELECT statements

Procedures and Functions

• Creation and deletion of procedures and functions
• Parameterization techniques
• Passing parameters by value and by reference; understanding NOCOPY

Error and Exception Handling

• Strategies for managing errors and exceptions in PL/SQL

Cursor Management

• Understanding records and static cursors
• Parameterizing cursors
• Implementing cursors with FOR UPDATE

Associative Arrays

• Declaring and using associative arrays in PL/SQL

Packages

• Structure of packages: specifications and bodies
• Initialization sections, global variables, and memory management
• Concepts of encapsulation, overloading, and forward declarations

Triggers

• Creating DML triggers
• Implementing INSTEAD OF triggers
• Understanding system triggers
• Exploring advanced trigger features

Practical Applications of Built-in Packages

• Writing to files using UTL_FILE
• Sending emails from PL/SQL

Code Management and Compilation

• Techniques for code encryption and obfuscation
• Utilizing conditional compilation
• Managing object dependencies
• Interpreting compiler warnings

Summary and Next Steps