Oracle Database 12C PL/SQL Introduction

Training Summary
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. 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. 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. The content of this course is applicable to Oracle 12c with relevance to versions back to 10g. This class provides partial preparation for the OCP Program with PL/SQL 1Z0-147 and Oracle Database: Program with PL/SQL 1Z0-144 tests. Oracle Corporation suggests that candidates also have six months of hands-on experience before testing.
Before taking this course, you should have skill with GUI interfaces and a data processing background. 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.
3 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
  • Technical managers needing Oracle expertise for project administration
Course Topics
  • PL/SQL language fundamentals
  • Creating anonymous PL/SQL 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
  • Advanced cursor topics
  • PL/SQL exception handling
  • PL/SQL block hierarchies
  • Advanced exception handling
  • 12c PL/SQL new features
  • Creating stored procedures
  • Writing stored procedures using SQL Developer

Related Scheduled Courses