top of page

Database Module (Oracle)

​

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.

​

Database Administration      
Quality Analysis  
   E-Commerce
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

​​

  • 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 

bottom of page