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