Oracle

Time:  40 Hours

 

Prerequisite:  

 

Prior database basic concepts such as store/read/update/delete records. Prior exposure to object-oriented programming concepts is not required, but definitely beneficial.

 

Course overview:

 

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.

 

Course Content:

Introduction and Overview

 

PL/SQL fundamentals

  • Declaring and anchoring variables to database definitions

  • Flow control constructs

  • DML commands : Select, Insert, Update, Delete

  • DDL commands: Create, Alter Tables/Views/Sequence

  • TCL commands: Grant, Revoke

  • Joins

  • Trigger

  • Stored procedure

  •  

 

Oracle 11g and 12c PL/SQL features

  • PL/SQL 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

 

Who should go for this course?

 

Programmers and others working with PL/SQL who have a working knowledge of SQL

 

Special notes:

 

  • Hands-on labs for each topic

  • Real life projects at the course completion

 

 

For more info contact ITEXPS

 IT Expert System, Inc. is approved to operate by the Private Business and Vocational Schools Division of the Illinois Board of Higher Education.

 IT Expert System, Inc. is not accredited by a US Department of Education recognized accrediting body. IBHE Mandatory Disclosure Reporting

 IT Expert System, Inc Training programs are approved by the Illinois State Approving Agency for the enrollment of qualified veterans and/or other eligible persons to receive Montgomery GI Bill® education benefits.

‘PMP’ and 'CAPM' are registered marks of the Project Management Institute, Inc.

‘GI Bill' is a registered trademark of the U.S. Department of Veterans Affairs (VA).

Office:

951 N. Plum Grove Rd.

Suite A, C
Schaumburg, IL, 60173

Ph: 847 350 9034

Email: info@itexps.com

Branch Office:

1560 Wall Street,

Suite #111,

Naperville, IL 60563 

Ph: 847 350 9034

Email: info@itexps.com

Disclaimer: At this time, we are not offering any training programs for Nebraska residents.