• India Flag
    Call Us:

    +91 769-409-5404

  • USA Flag
    Call Us:

    +1 786-629-6893

About Course:

The training is designed to cover the basic concept of writing PL/SQL Code to write professional PL/SQL code for complex business problems. The course also talks about writing optimized PL/SQL Code. The course is going to cover many real-time case studies in various scenarios. Implementing the PL/SQL code for business rules, Data Quality Checks, working in Enterprise Data Warehousing and data transformation rules.


Duration : 30 hours


Fee: 299


Job Trends


Who Should Learn?

Fresh Graduates

Prerequisite

Both SQL and PL/SQL is easy to learn, However having some basic idea about RDBMS and basic idea about some programming language will be an advantage.

Delivery Methodology

We are using an experiential delivering methodology that blends theoretical concepts with hands-on practical learning to ensure a holistic understanding of the subject or course.

Class Delivery

Live Interactive classes with expert

Training Calender

Date Time Type Attend
No Schedule Available
Contact Us

+91 769-409-5404

Includes
  • 24 hours on-demand video
  • Articles
  • Coding Exercises
  • Full lifetime access
  • Certificate of Completion

Curriculum

Oracle Fundamentals and PL SQL for beginners

Oracle Fundamentals and PL SQL for beginners

Course Overview

Brief Introduction to PL/SQL

  • What is PL/SQL
  • SQL Versus PL/SQL
  • PL/SQL Architecture
  • Advantages of PL/SQL

Working with a different type of Datatypes

  • Date, Time and Interval Types
  • Boolean Types
  • Characters, String and National Character Types
  • Number Types
  • LOB Types
  • Composite data types

Exception Handler

  • What is Exception Handling
  • How to Handle Exception
  • Structure of Exception Handling
  • Types of Exception Handling
  • SAVE Exception in Bulk Collect
  • PRAGMA init Exception

Cursors in PL/SQL

  • Why cursor is used
  • Declaring Cursors
  • Different Types of a cursor
  • Cursor Attributes
  • Sending parameter to a cursor
  • Different ways of using the cursor
  • Cursor Variable (Reference Cursor)
  • Advantage of using cursor variable over a normal cursor
  • Passing Cursor variable as the parameter
  • Restriction on Cursor Variables

Working with Procedures, Functions, and Packages

  • Basic of stored procedure
  • Basics of functions
  • Basics of packages
  • Advantage of using procedures, function, and packages
  • Passing cursor variables to procedures, function, and packages
  • Recompiling functions and stored procedures
  • Forward declaration
  • Declaring and using persistent global variables in packages
  • Creating table Function
  • Function overloading
  • Restrict Reference Pragma
  • Pinning packages in the SGA with dbms_shared_pool.keep

Overview of some advanced PL/SQL topics

  • Table Function
  • Pipelined table function
  • Error log tables
  • Hints in PL/SQL

Writing PL/SQL blocks using tools

  • PL/SQL Developers
  • Toad
  • SQL Plus
  • SQL Developers etc.

Control Structures

  • IF-THEN-ELSE Statement
  • Case Statement
  • GOTO Statement
  • NULL Statement

Working with Objects & Collection

  • Describing Object
  • Constructor method on objects
  • Nested Table
  • VARRAY
  • Associative Array
  • Collection Methods
  • Manipulate Collection
  • Distinguish between the different types of collections and when to use them

Dynamic SQL and PL/SQL

  • When to use
  • Declaring dynamic SQL and PL/SQL by using DBMS_SQL package
  • Declaring dynamic SQL and PL/SQL by using Execute Immediate
  • Using Dynamic SQL in the FORALL statement
  • A dynamic statement using binding variables
  • Advantage of using Dynamic SQL and PL/SQL

Debugging and Error tracking of PL/SQL code

  • Using Error log message tables
  • Using some built-in packages
  • Using tools like PL/SQL developers

Tuning of PL/SQL program

Language Fundamentals

  • identifiers
  • literals
  • Executable and Non Executable statement
  • Anonymous Block Structure
  • Nested Block
  • Named Block
  • PRAGMA
  • Scope & Visibility of variables

Different types of loops

  • Simple Loop
  • WHILE- Loop
  • FOR- Loop
  • Labels in Loop
  • Scope Rules
  • Exit Statement

Working with Records

  • Declaring Records
  • The benefit of using the record
  • Passing Values To and From Record
  • Comparing two records

Bulk processing of data using Bulk Collect

  • What is Bulk collect operation
  • How does it impact performance
  • Declaring BULK COLLECT operation with simple select statement
  • BULK COLLECT with cursors
  • BULK COLLECT using FORALL statement
  • Catching exception in Bulk collect using SAVE EXCEPTION

Working with Triggers.

  • Describe Triggers
  • Identify the Trigger Event Types and Body
  • Business Application Scenarios for Implementing Triggers
  • Create DML Triggers using the CREATE TRIGGER Statement
  • Identify the Trigger Event Types, Body, and Firing (Timing)
  • Differences between Statement Level Triggers and Row Level Triggers
  • Create Instead of and Disabled Triggers
  • How to Manage, Test and Remove Triggers?

 

 

 

Use Case

Use Case 1

Certificate

Certificate
Benefits of Certificate

Certification demonstrates your dedication, motivation and technical knowledge on a specific platform. Having a certification shows that you not only possess comprehensive knowledge of that technology but you also care enough about your own career to spend the time and money to get the certification.

We are welcoming our Students or professionals to participate in our professional online courses. We are offering great variety of online training programs and professional courses that you can always find as desired. After the completion of training program they will receive a certificate from BISP. As a Certified professional you can apply that knowledge in your future profession and enjoy with better salaries & career prospects.

Signup for free newsletter and
business tips

Any Questions?
Talk to our Course Co-ordinator

+91 769-409-5404
Want to see a live demo? We'll be in touch within 24 hours?