top of page

Oracle Programming

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

​​​

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

bottom of page