Oracle PL/SQL Programming

PL/SQL allows developers to extend the basic data query and manipulation of SQL into complete applications and shared program units. This class provides the technical expertise necessary to utilize this powerful component of Oracle. This class combines the content of the Oracle PL/SQL Introduction (ora-210) and Oracle PL/SQL Program Units (ora-220) classes. The content of this course applies to developing PL/SQL blocks for stand-alone use, to be embedded in other programming environments (Java, C, C#, etc.) or to augment the functionality of Oracle Application Express (APEX) applications or in Oracle Forms or Reports. One of the most powerful features of the Oracle RDBMS product is the ability to create named 'blocks' of PL/SQL code that are stored within the database. This allows for robust solutions to be developed that can be shared and re-used. The four types of these program units: procedures, functions, packages and triggers are each covered in depth. Students will learn to write, debug and manage each. Students use the Oracle PL/SQL IDE, SQL Developer, throughout the course as the presentation tool and as their development environment. Features of this tool that enhance productivity along with its integral source level debugger are presented. The performance of PL/SQL is highly dependent on the selection of suitable data types for local variables. In particular computationally intensive PL/SQL is highly influenced by the numeric types that are used. The differences in and the costs and benefits of the various PL/SQL numeric types are compared. PL/SQL supports quite complex composite datatypes. Understanding when such a variable is called for and understanding how to choose among them requires in depth knowledge of their characteristic, strengths and limitations. The implementation details of the various kinds of composites are explained carefully. Built-in exception handling is a feature of the PL/SQL programming language that provides for commonality in the approach to handle any sort of run-time errors, both those due to Oracle errors (for example, duplicate primary key indexes) and application specific (customer spending over a pre-assigned credit limit). Oracle provides dozens of PL/SQL packages with the core RDBMS product. Utilization of some samples of these presupplied PL/SQL packages is covered allowing developers to accomplish sophisticated tasks such as job scheduling, interprocess communication and utilizing Oracle’s FLASHBACK capabilities.
Prerequisites for this course include:Skill with GUI interfaces, data processing background, and a basic understanding of SQL is required to succeed in this class. The SQL used in this class is as simple as possible, but persons with absolutely no SQL experience will likely experience difficulty.
5 Days/Lecture & Lab
This course is designed for Oracle SQL developers and DBAs, Oracle PL/SQL developers and DBAs wishing to ‘back fill’ gaps in their expertise, Oracle APEX, Forms and Reports developers and Technical managers needing Oracle expertise for project administration
  • PL/SQL language fundamentals
  • Creating anonymous blocks using SQL Developer
  • The Petsaver database
  • PL/SQL scalar variables
  • SELECT statements in PL/SQL
  • DML statements in PL/SQL
  • Transaction control in PL/SQL
  • The SQL Developer interface
  • PL/SQL control structures
  • PL/SQL composite variables
  • PL/SQL cursors
  • PL/SQL exception handling
  • PL/SQL block hierarchies
  • Advanced exception handling
  • Basic Oracle supplied packages
  • Optimizing PL/SQL performance
  • Creating stored procedures
  • Writing stored procedures using SQL Developer
  • Creating stored functions
  • Advanced features of procedures and functions
  • 12c PL/SQL new features
  • Debugging PL/SQL using SQL Developer
  • Creating packages
  • Writing packages with SQL Developer
  • Advanced features of packages
  • Creating database triggers
  • Advanced features of triggers
  • Maintaining program units
  • Dynamic SQL in PL/SQL

Related Scheduled Courses