Oracle Database: Develop PL/SQL Program Units Ed 2 Training

Live Online & Classroom Enterprise Certification Training

The Oracle Database: Develop PL/SQL Program Units Ed 2 course helps students enhance their skills as PL/SQL developers by learning to develop stored procedures, functions, packages, and database triggers.

Looking for a private batch ?

REQUEST A CALLBACK

Certified Trainer
Authorized Courseware
Completion Certificate from ATP

Enterprise Reporting
Lifetime Access
CloudLabs
24x7 Support
Real-time code analysis and feedback

What is Develop PL/SQL Program Units Ed 2 Certification Training about?

This course is designed for developers with basic PL/SQL and SQL language skills. Students learn to develop, execute, and manage PL/SQL stored program units such as procedures, functions, packages, and database triggers. Students also learn to manage, PL/SQL subprograms and triggers.

Students are introduced to the utilization of some of the Oracle-supplied packages. Additionally students learn to use Dynamic SQL, understand design considerations when coding using PL/SQL, understand and influence the PL/SQL compiler, and manage dependencies. In this course, students learn and use Oracle SQL Developer as the main environment tool to develop these program units. SQL*Plus is introduced as optional tools. Demonstrations and hands-on practice reinforce the fundamental concepts.

What are the objectives of Develop PL/SQL Program Units Ed 2 Certification Training ?

Create triggers to solve business challenges
Manage dependencies between PL/SQL subprograms
Design PL/SQL code for predefined data types, local subprograms, additional pragmas and standardized constants and exceptions
Use the compiler warnings infrastructure
Create, use, and debug stored procedures and functions
Design and use PL/SQL packages to group and contain related constructs
Create overloaded package subprograms for more flexibility
Use the Oracle supplied PL/SQL packages to generate screen output, file output, and mail output
Write dynamic SQL for more coding flexibility
Use conditional PL/SQL compilation and obfuscate (hide) code

Who is Develop PL/SQL Program Units Ed 2 Certification Training for?

Database Administrators
System Analysts
Forms Developer
Technical Consultant
Portal Developer
PL/SQL Developer

What are the prerequisites for Develop PL/SQL Program Units Ed 2 Certification Training?

Suggested Prerequisite

Familiarity with programming languages
Familiarity with data processing concepts and technique

Required Prerequisite

Oracle Database 12c: Introduction for Experienced SQL Users

Available Training Modes

Live Online Training

24 Hours

Classroom Training

3 Days

Course Outline Expand All

Expand All

Introduction

PL/SQL development environments available in this course

Using the SQL Worksheet

Executing SQL Statements

Creating and Executing Anonymous Blocks

Course Objectives, Course Agenda and Appendixes Used in this Course

Full Human Resources (HR) Schema

Online Oracle Database 12c SQL and PL/SQL documentation

Working With Script Files

Working with Oracle Database Exadata Express Cloud Service

Connecting to Exadata Express Database using Database Clients

Using SQL Developer to work with Exadata Express Database

Using SQLcl to work with Exadata Express Database

Using SQL*Plus to work with Exadata Express Database

Overview of Oracle Database Exadata Express Cloud Service

Accessing Cloud Database using SQL Workshop

Creating Stored Procedures

Working with procedures

Using formal and actual parameters

Handling exceptions in procedures

Passing parameters using the positional, named, or combination techniques

Uses and benefits of procedures

Identify the available parameter-passing modes

Viewing the procedure information

PL/SQL blocks and subprograms

Creating Functions and Debugging Subprograms

Working with Functions

Creating Stored Functions

Using User-Defined Functions in SQL Statements

Defining and executing PL/SQL functions in SQL statements

Identifying the Advantages of Using Stored Functions in SQL Statements

The Difference Between Procedures and Functions

Using a PL/SQL Function in the SQL WITH Clause

Restrictions When Calling Functions from SQL statements

Creating Packages

Developing a PL/SQL Package

Invoking the Package Constructs

Removing a Package

Visibility of a Package’s Components

Components of a PL/SQL Package

Creating and Using Bodiless Packages

Using PL/SQL Packages

Creating the Package Specification and Package Body

Working With Packages

Controlling Side Effects of PL/SQL Subprograms

Initializing Packages

Using Forward Declarations to Solve Illegal Procedure Reference

Using Package Functions in SQL and Restrictions

Persistent State of Package Variables and Cursors

Using PL/SQL Tables of Records in Packages

Overloading Subprograms

Persistent State of Packages

Using Oracle-Supplied Packages in Application Development

Using the UTL_MAIL Package

Using Oracle-Supplied Packages

Examples of Some of the Oracle-Supplied Packages

Using the UTL_FILE Package to Interact With Operating System Files

Working of DBMS_OUTPUT Package

Using Dynamic SQL

Introduction to Dynamic SQL

Dynamic SQL using DBMS_SQL package

Working With Dynamic SQL

Dynamic SQL with mock up application

The Execution Flow of SQL

Using Native Dynamic SQL (NDS)

When Do You Need Dynamic SQL?

Using BULK COLLECT and FORALL

Creating Triggers

Different types of triggers

Creating database triggers

Database triggers and their use

Removing database triggers

Database trigger firing rules

Creating Compound, DDL, and Event Database Triggers

Creating triggers on DDL statements

Displaying information about triggers

Creating triggers on system events

Compound triggers

Mutating tables

Design Considerations for PL/SQL Code

Grant Roles to PL/SQL Packages and Standalone Stored Subprograms

Writing PL/SQL code that uses local subprograms

Standardizing exceptions with an exception package

Using the PARALLEL ENABLE hint for optimization

Using the NOCOPY compiler hint to pass parameters by reference

Using the AUTONOMOUS TRANSACTION pragma

Standardizing constants with a constant package

Describing the differences between invoker rights and definer rights

Using PL/SQL compiler

Viewing the Compiler Warnings

Viewing the Current Setting of PLSQL_WARNINGS

Guidelines for using PLSQL_WARNINGS

Conditional Compilation

Using the PL/SQL Compile Time Warnings

Using the PL/SQL Compiler with initialisation parameters

Managing Dependencies

Managing local and remote procedural dependencies

Predicting the effect of changing a database object

Tracking procedural dependencies with dictionary views

Dependent and referenced objects

Who is the instructor for this training?

The Trainer is Oracle certified Instructor with extensive domain experience, including years of experience training & mentoring professionals in the industry.

Develop PL/SQL Program Units Ed 2 Certification Training - Certification & Exam

SpringPeople is the Authorized Training Partner of Oracle.
The training fees is exclusive of exam cost.
For any queries; feel free to reach us at oracle@springpeople.com

Reviews

My outlook on training changed completely after attending SpringPeople BPC training. The content, the trainer and infrastructure at SpringPeople were top notch and perfectly in tune with the industry requirements. Regardless to say, training is now something that I look forward to to. Kudos to everyone at SpringPeople!

Shweta Priya

Sony

I attended the 3-day AngularJs training at SpringPeople. The trainer was an industry veteran with vast experience in the subject. Notably, the hands-on training, and the Q&A session stood out. Overall, I found SpringPeople a great place to learn with excellent facilities and great trainers. Would recommend SpringPeople to my colleagues and friends.

Swati Singh

I attended the training on API Design for Mulesoft. The sessions were well planned and value-laden. I benefited immensely from the hands-on experience enabled through virtual labs. I would like to specifically commend the efficiency of the support team who were always available to resolve my concerns.

Nikhil Kohli

Stryker

I attended the jQuery training batch, conducted by Mr. Vijay, an SME who did a thorough coverage of all the essentials. He took us through concepts such as jQuery animations, event handlers, plugins, and jQuery-UI by small programs, very easily. The sessions were useful and well structured. By the end of the training, I was well equipped to develop a SPA on Product Management System. Overall, the learning experience at SpringPeople was great!

Heena Rajan

Mindtree