Type to search LearningTree.com

Do you mean "{{response.correctedQuery}}" ?

Sorry, no results were found for your query.

Please check your spelling and try your search again.

 

Oracle Databases Training









Preferred method of contact?

Oracle PL/SQL Programming

COURSE TYPE

Practitioner

Course Number

493

Duration

5 Days

Enroll

About This Course: The Oracle PL/SQL language is a flexible procedural extension to SQL and increases productivity, performance, scalability, portability and security. In this course, you will gain the practical knowledge to write PL/SQL programs. You will learn to build stored procedures, design and execute modular applications, and increase the efficiency of data movement.

You Will Learn How To

  • Develop efficient PL/SQL programs to access Oracle databases
  • Create stored procedures and functions for reuse and maintenance
  • Design modular applications using packages
  • Manage data retrieval for front-end applications
  • Invoke native dynamic SQL to develop high-level abstract code
  • Improve performance with collections and bulk operations

Course Outline

  • Introduction and Overview

PL/SQL fundamentals

  • Declaring and anchoring variables to database definitions
  • Flow control constructs

Oracle 11g and 12c PL/SQL features

  • PL/Scope in Oracle 11g
  • Returning implicit cursor results from stored procedures in Oracle 12c
  • Declaring local functions within SELECT statements in Oracle 12c
  • Data Manipulation Techniques

Maintaining data with DML statements

  • Employing the RETURNING INTO clause
  • Solving the fetch-across-commit problem

Managing data retrieval with cursors

  • Implications of explicit and implicit cursors
  • Simplifying cursor processing with cursor FOR LOOPs
  • Embedding cursor expressions in SELECT statements

Cursor variables

  • Strong vs. weak cursor variables
  • Passing cursor variables to other programs
  • Defining REF CURSORS in packages
  • Developing Well-Structured and Error-Free Code

Error handling using EXCEPTIONs

  • Propagation and scope
  • "Retrying" problem transactions with EXCEPTION processing

Debugging PL/SQL blocks

  • Simplifying testing and debugging with conditional compilation
  • Interpreting compiler messages
  • Applying structured testing techniques
  • Building and applying a test bed
  • Leveraging the debugging facilities in SQL Developer
  • Achieving Maximum Reusability

Writing stored procedures and functions

  • Calling and invoking server-side logic
  • Passing input and output parameters
  • Implementing an autonomous transaction

Coding user-written functions

  • Calling PL/SQL functions from SQL
  • Building table-valued functions

Developing safe triggers

  • Employing :OLD and :NEW variables
  • Avoiding unreliable trigger constructs
  • Exploiting schema and database triggers
  • Exploiting Complex Datatypes

Collection types

  • PL/SQL tables, nested tables, VARRAYs
  • Stepping through dense and nonconsecutive collections

Bulk binding for high performance

  • Moving data into and out of PL/SQL blocks
  • BULK COLLECT INTO and FORALL
  • BULK cursor attributes
  • BULK EXCEPTION handling
  • Invoking Native Dynamic SQL

Finessing the compiler

  • The EXECUTE IMMEDIATE statement
  • The RETURNING INTO clause

Types of dynamic SQL

  • Building SQL statements during runtime
  • Autogenerating standard code
  • Package Tips and Techniques
  • Package structure: SPEC and BODY
  • Eliminating dependency problems
  • Overloading for polymorphic effects
  • Evaluating application frameworks
  • Declaring and using persistent global variables
Show complete outline
Show Less

Course Schedule

Attend this live, instructor-led course In-Class or Online via AnyWare.

Hassle-Free Enrollment: No advance payment required.
Tuition due 30 days after your course.

Oct 31 - Nov 4 Rockville, MD/AnyWare Enroll Now

How would you like to attend?

Live, Online via Anyware
In-Class

Dec 5 - 9 Ottawa/AnyWare Enroll Now

How would you like to attend?

Live, Online via Anyware
In-Class

Jan 23 - 27 Ottawa/AnyWare Enroll Now

How would you like to attend?

Live, Online via Anyware
In-Class

Feb 13 - 17 New York/AnyWare Enroll Now

How would you like to attend?

Live, Online via Anyware
In-Class

Feb 27 - Mar 3 Herndon, VA/AnyWare Enroll Now

How would you like to attend?

Live, Online via Anyware
In-Class

Mar 27 - 31 Ottawa/AnyWare Enroll Now

How would you like to attend?

Live, Online via Anyware
In-Class

Apr 24 - 28 Rockville, MD/AnyWare Enroll Now

How would you like to attend?

Live, Online via Anyware
In-Class

Jul 17 - 21 Ottawa/AnyWare Enroll Now

How would you like to attend?

Live, Online via Anyware
In-Class

Jul 31 - Aug 4 Toronto/AnyWare Enroll Now

How would you like to attend?

Live, Online via Anyware
In-Class

Aug 28 - Sep 1 Herndon, VA/AnyWare Enroll Now

How would you like to attend?

Live, Online via Anyware
In-Class

Guaranteed to Run

Bring this Course to Your Organization and Train Your Entire Team
For more information, call 1-888-843-8733 or click here

Tuition

Standard

$3190

Government

$2833

Course Tuition Includes:

After-Course Instructor Coaching
When you return to work, you are entitled to schedule a free coaching session with your instructor for help and guidance as you apply your new skills.

Free Course Exam
You can take your course exam on the last day of your course and receive a Certificate of Achievement with the designation "Awarded with Distinction."

Prev
Next

Questions

Call 1-888-843-8733 or click here »

An experienced training advisor will happily answer any questions you may have and alert you to any tuition savings to
which you or your organization may be entitled.

Training Hours

Standard Course Hours: 9:00 am – 4:30 pm
*Informal discussion with instructor about your projects or areas of special interest: 4:30 pm – 5:30 pm


FREE Online Course Exam (if applicable) – Last Day: 3:30 pm – 4:30 pm
By successfully completing your FREE online course exam, you will:

  • Have a record of your growth and learning results.
  • Bring proof of your progress back to your organization
  • Earn credits toward industry certifications (if applicable)
  • Make progress toward one or more Learning Tree Specialist & Expert Certifications (if applicable)

Enhance Your Credentials with Professional Certification

Learning Tree's comprehensive training and exam preparation guarantees that you will gain the knowledge and confidence to achieve professional certification and advance your career.

This course qualifies for 29 CPE credits from the National Association of State Boards of Accountancy CPE program. Read more ...

“The Oracle PL/SQL course was informative and opened me up to tasks I hadn’t previously done in my job. My new knowledge and skills have created new opportunities for me. The instructor was very engaging and used lots of real-world examples, which was very useful.”

- M. Faridi, Senior Programmer/Analyst
GEICO

Prev
Next