Oracle Programming

Time:  32 Hours

 

Prerequisite:

Programming knowledge is preferred.

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:

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/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

  • 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 testbed

  • 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

Calling 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

 

Who should go for this course?

 

Programmer, Database Developer,  Designer and Architect.

 

 

Special notes: 

 

  • Certification preparation 

  • Project work

  • Hands-on labs

1/4

Exploiting complex data types

  • PL/SQL tables, nested tables, VARRAYs

  • Collection types

  • 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

  • Auto generating 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

Database Design/Architect
  • Database Design  (ERD)

  • Normalization vs De-Normalization

  • Conceptual vs Logical vs Physical Design

  • Capacity  Planning and Forecasting 

  • OLTP vs OLAP design concepts

Database Administration

  • Install, Configure and Administer 

  • Implement Security

  • Backup and Restore procedure

  • Disaster Recovery Planning

  • Replication, Mirroring concepts

  • Performance Tuning 

Staffing Support
  • Resume Preparation

  • Mock Interview Preparation

  • Phone Interview Preparation

  • Face to Face Interview Preparation

  • Project/Technology Preparation

  • Internship with internal project work

  • Externship with client project work

Our Salient Features:
  • Hands-on Labs and Homework

  • Group discussion and Case Study

  • Course Project work

  • Regular Quiz / Exam

  • Regular support beyond the classroom

  • Students can re-take the class at no cost

  • Dedicated conf. rooms for group project work

  • Live streaming for the remote students

  • Video recording capability to catch up the missed class

Tel: 847 350 9034

1560 Wall Street, Suite #111,

Naperville, IL 60563

  • LinkedIn Social Icon
  • Facebook Social Icon

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

 'PMP' and 'PMI' are registered marks of the Project Management Institute, Inc.

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

InfoTekGuide - A Leading IT Training Provider in Schaumburg and Naperville