Tutorialspoint

Oracle PL/SQL Training Course

person icon Uplatz

Oracle PL/SQL Training Course

Learn PL/SQL architecture, programming, fine-tuning in depth from basics to advanced level. Become Oracle Developer/DBA.

updated on icon Updated on Sep, 2023

language icon Language - English

person icon Uplatz

architecture icon Development,Database Design & Development,PL/SQL

price-loader

30-days Money-Back Guarantee

Training 5 or more people ?

Get your team access to 19,000+ top Tutorialspoint courses anytime, anywhere.

Course Description

A warm welcome to the Oracle PL/SQL course by Uplatz.

PL/SQL is a procedural extension of SQL, making it extremely simple to write procedural code that includes/embraces SQL as if it were a single language thus combining the power of both the database Structured Query Language (SQL) as well as the high-level Procedural Language (PL).

This comprehensive Oracle PL/SQL training course by Uplatz will teach you all concepts of Oracle PL/SQL programming in great depth. You will build skills on programming, productivity, performance, scalability, portability, and security of your Oracle database. With this training, you will learn how to build and execute PL/SQL programs in Oracle, build stored procedures, triggers, functions, etc., design and execute modular applications, automate the Oracle PL/SQL packages, tuning of Oracle database, and increase the efficiency of data movement - essential skills for any Oracle developer.

Key features of PL/SQL are:

  • PL/SQL is a procedural extension of SQL, making it extremely simple to write procedural code that includes SQL as if it were a single language. In comparison, most other programming languages require mapping data types, preparing statements and processing result sets, all of which require knowledge of specific APIs.

  • The data types in PL/SQL are a super-set of those in the database, so you rarely need to perform data type conversions when using PL/SQL. Ask your average Java or .NET programmer how they find handling date values coming from a database. They can only wish for the simplicity of PL/SQL.

  • When coding business logic in middle tier applications, a single business transaction may be made up of multiple interactions between the application server and the database. This adds a significant overhead associated with network traffic. In comparison, building all the business logic as PL/SQL in the database means client code needs only a single database call per transaction, reducing the network overhead significantly.

  • Oracle is a multi-platform database, making PL/SQL and incredibly portable language. If your business logic is located in the database, you are protecting yourself from operating system lock-in.

  • Programming languages go in and out of fashion continually. Over the last 35+ years Oracle databases have remained part of the enterprise landscape. Suggesting that any language is a safer bet than PL/SQL is rather naive. Placing your business logic in the database makes changing your client layer much simpler if you like to follow fashion.

  • Centralizing application logic enables a higher degree of security and productivity. The use of Application Program Interfaces (APIs) can abstract complex data structures and security implementations from client application developers, leaving them free to do what they do best.

Oracle PL-SQL course curriculum

  • Overview of PL/SQL

  1. What is PL/SQL? What are the benefits?

  2. PL/SQL Block Structure & Types of Blocks

  3. Writing simple anonymous block

  4. Using SQL*plus to run anonymous block

  5. Creating Script and run it

  6. Quiz

  • Declaring PL/SQL Variables

  1. About Variables and the syntax

  2. Defining Variables exercise

  3. Guidelines for declaring & initializing variables

  4. Delimiters in string literals (the q' Notation)

  5. Types of variables and data types 1

  6. Types of variables and data types 2

  7. Data Types exercise

  8. Define Variables Using %TYPE

  9. Understating the SELECT statement in PL/SQL

  10. Bind Variables

  11. Quiz

  • Writing Executable Statements

  1. Inside PL/SQL Block

  2. Inside PL/SQL Block (exercise)

  3. Nested Blocks

  4. SQL & PL/SQL programming Guidelines

  5. Quiz

  • Interacting with Oracle DB Server

  1. SELECT statement & DML in PL/SQL

  2. SELECT statement & DML in PL/SQL exercise

  3. Understanding Implicit SQL cursor/ SQL Attributes

  4. Implicit SQL cursor exercise

  5. Quiz

  • Writing Control Structure

  1. IF Statement Part 1

  2. IF Statement Part 2

  3. Handling nulls in IF statement

  4. CASE expression & CASE Statement

  5. Basic Loop

  6. While Loop

  7. FOR Loop

  8. Nested Loops

  9. Nested Loops and Labels

  10. Continue Statement

  • Working with Composite Data Types

  1. PL/SQL Records: Programmer method part 1

  2. PL/SQL Records: Programmer method part 2

  3. PL/SQL Records: %ROWTYPE method

  4. Nested PL/SQL Records

  5. INDEX BY tables (Associative arrays)

  6. INDEX BY tables Methods

  7. INDEX BY tables of Records

  8. Nested table

  9. VARRAY

  • Using Explicit Cursors

  1. Understanding Explicit Cursor

  2. Explicit Cursor exercise

  3. Explicit Cursor Attributes

  4. FOR LOOP Cursor

  5. Cursor with Parameters

  6. FOR UPDATE Clause & CURRENT OF Clause

  • Handling Exceptions

  1. Understanding Exceptions

  2. Predefined Oracle Server Error Exception

  3. Common mistakes when dealing with exceptions

  4. Non-Predefined Oracle Server Error Exception

  5. User-Defined Error Exception

  6. Group Functions and exceptions

  7. Many blocks and many exceptions

  • Creating Procedures

  1. Understanding Procedures and subprograms

  2. Understanding Procedures Syntax

  3. Creating Procedure (IN parameter)

  4. Creating Procedure (OUT parameter)

  5. Creating Procedure (IN OUT parameter)

  6. Comparing the Parameter Modes

  7. Available notations for passing parameters

  8. Using the default option for Parameters

  9. Exception handling in multiple blocks

  10. Using Boolean and PL/SQL records as parameters

  • Creating Functions

  1. Understanding user-defined functions

  2. Difference between procedures & functions

  3. Creating function example

  4. No_data_found and functions

  5. Advantages of user-defined functions in SQL Statements

  6. Restrictions when calling functions from SQL part 1

  7. Restrictions when calling functions from SQL part 2

  • Creating Packages

  1. Understanding packages by nice example

  2. More about packages

  3. Creating package practice

  4. Creating package specification without body

  5. Guidelines for writing packages

  6. The visibility of a package’s components

  7. When & Why to recompile the package?

  • Working with Packages

  1. Understanding Overloading Subprograms in PL/SQL

  2. Overloading Subprograms practices (procedures)

  3. Overloading Subprograms practices (functions)

  4. Overloading Standard package

  5. Forward Declaration

  6. Persistent State of packages

  7. Using PRAGMA SERIALLY_REUSABLE

  8. Persistent State and cursor

  9. using PL/SQL tables in packages

  • Using Oracle-supplied Packages in Application Development

  1. About oracle-supplied Packages

  2. DBMS_OUTPUT part 1

  3. DBMS_OUTPUT part 2

  4. UTL_FILE Package Part 1 / create directory

  5. UTL_FILE Package Part 2 / get_line example 1

  6. UTL_FILE Package Part 3 / get_line example 2

  7. UTL_FILE Package Part 4 / put_line

  8. UTL_FILE Package Part 5 / exceptions

  9. Mail

  • Using Dynamic SQL

  1. Execution flow of SQL

  2. What is Dynamic SQL?

  3. Execute immediate example 1 / delete any table

  4. Execute immediate example 2 / DDL in PLSQL

  5. Execute immediate example 3 / USING Clause

  6. Execute immediate example 4 / INTO Clause

  7. Execute immediate example 5 / single row query

  8. What is REF Cursor?

  9. Dynamic SQL with multi row query

  10. Execute immediate/ more examples

  11. DBMS_SQL Package VS Execute immediate

  • Design Consideration for PL/SQL Code

  1. Standardizing Part 1

  2. Standardizing Part 2

  3. Definer’s Rights

  4. Invoker’s Rights

  5. Autonomous Transactions

  6. Features Autonomous Transactions

  7. The (IN) parameters always passed by reference

  8. The (out/in out) parameters can be passed by value or reference

  9. Passing by reference example (IN parameters)

  10. Passing by value VS passing by reference using nocopy

  11. When Does NOCOPY hint has no effects?

  12. NOCOPY Performance Exercise

  13. Using the PARALLEL_ENABLE hint

  14. Using the RESULT_CACHE hint

  15. Using the DETERMINISTIC hint

  16. Understanding Bulk Binding

  17. Bulk Binding Example

  18. Bulk binding with save exception part 1

  19. Bulk binding with save exception part 2

  20. Understanding Bulk collect

  21. Bulk collect another example

  22. Bulk collect and cursors

  23. Using returning in bulk collect

  24. INDICES OF

  • Creating Triggers

  1. Understanding Triggers

  2. Tigger Types/ Trigger Event Types

  3. Statement Level trigger VS Row Level (Part 1)

  4. Statement Level trigger VS Row Level (Part 2)

  5. Triggers common cases

  6. Statement Level trigger Example 1

  7. Statement Level trigger Example 2

  8. Understanding the NEW & OLD qualifiers

  9. Row level trigger example

  10. Row level trigger (create Audit table)

  11. Trigger Firing sequence

  12. Compile/enable/ disable/ drop trigger

  13. Using the triggers for populating a default value

  14. Instead of triggers (on Views)

  • Creating Compound, DDL, and Event Database Triggers

  1. Follows Statement in triggers

  2. Understanding Compound triggers

  3. Understanding Mutating Table

  4. Solving Mutating Table issue using compound trigger

  5. Using Compound trigger & array to solve Mutating

  6. Mutating table & ON DELETE CASCADE

  7. Database Triggers VS Stored procedures

  8. Creating DDL triggers (Schema or Database)

  9. Create system event trigger

  10. Final comments about the triggers

  • Using the PL/SQL Compiler

  1. Introduction about PL/SQL compiler

  2. Understanding (plsql_code_type) parameter

  3. Understanding (plsql_optimize_level) parameter

  4. Understanding Warnings in PL/SQL

  5. Understanding (plsql_warnings) parameter

  6. More examples for plsql_warnings

  7. Using the package dbms_warning

  • Managing PL/SQL Code

  1. Understanding conditional compilation

  2. Understanding (dbms_db_version ) package

  3. conditional compilation examples

  4. Understanding plsql_ccflags part 1

  5. Understanding plsql_ccflags part 2

  6. Understanding plsql_ccflags part 3

  7. plsql_ccflags Real example

  8. create_wrapped

  9. The PL/SQL wrapper utility

  • Managing Dependencies

Who this course is for:

  • PL/SQL & Database Developers
  • Oracle Developers
  • Analyst Programmers / Developers on PL/SQL
  • Data Engineers
  • Application Support Analysts
  • Full Stack Developers
  • Oracle PL/SQL and Datawarehouse Developers
  • Database Administrators (DBAs)
  • Database Programmers
  • Business & Technical Analysts
  • BI & Analytics Professionals
  • Anyone looking forward to start a career in Oracle or other Databases


Goals

What will you learn in this course:

  • Learn end-to-end PL/SQL concepts, programming, and application

  • Oracle PL/SQL architecture and building blocks

  • Data Types, Variables, Literals

  • PL/SQL programming - writing executable statements and programs from scratch

  • PL/SQL programming components - stored procedures, functions, packages, triggers

  • Working with Cursors

  • Understanding and writing control structures - IF, FOR, WHILE loops, Nested loops, etc.

  • Composite Data Types, Arrays, Sub-queries

  • Dynamic SQL

  • Handling Exceptions, Error handling, troubleshooting

  • Design Considerations and Tuning of PL/SQL

  • Automation of PL/SQL programming constructs and blocks

  • Prepare for Oracle PL/SQL Certification

  • Become an Oracle Developer / Database Administrator / Consultant / Data Analyst

Prerequisites

What are the prerequisites for this course?

  • Enthusiasm and determination to make your mark on the world!







Oracle PL/SQL Training Course

Curriculum

Check out the detailed breakdown of what’s inside the course

Overview of PL/SQL
1 Lectures
  • play icon Overview of PL/SQL 06:47 06:47
Declaring PL/SQL Variables
1 Lectures
Tutorialspoint
Writing Executable Statements
1 Lectures
Tutorialspoint
Interacting with Oracle DB Server
1 Lectures
Tutorialspoint
Writing Control Structure
1 Lectures
Tutorialspoint
Working with Composite Data Types
1 Lectures
Tutorialspoint
Using Explicit Cursors
1 Lectures
Tutorialspoint
Handling Exceptions
1 Lectures
Tutorialspoint
Creating Procedure
1 Lectures
Tutorialspoint
Creating Functions
1 Lectures
Tutorialspoint
Creating Packages
1 Lectures
Tutorialspoint
Working with Packages
1 Lectures
Tutorialspoint
Using oracle-supplied Packages in Application Development
1 Lectures
Tutorialspoint
Using Dynamic SQL
1 Lectures
Tutorialspoint
Design Consideration for PL/SQL Code
1 Lectures
Tutorialspoint
Creating Triggers and Creating Compound, DDL, Event Database Triggers
1 Lectures
Tutorialspoint
Using the PL/SQL Compiler
1 Lectures
Tutorialspoint
Managing PL/SQL Code
1 Lectures
Tutorialspoint
Managing Dependencies
1 Lectures
Tutorialspoint

Instructor Details

Uplatz

Uplatz

Uplatz is UK-based leading IT Training provider serving students across the globe. Our uniqueness comes from the fact that we provide online training courses at a fraction of the average cost of these courses in the market. 

Over a short span of 3 years, Uplatz has grown massively to become a truly global IT training provider with a wide range of career-oriented courses on cutting-edge technologies and software programming.

Course Certificate

User your certification to make a career change or to advance in your current career. Salaries are among the highest in the world.

sample Tutorialspoint certificate

Our students work
with the Best

Feedbacks

A

AJAY KUMAR

understood

E

Evrard Marcel KASSY

Tres bon cours avec des exemples concrets

Related Video Courses

View More

Annual Membership

Become a valued member of Tutorials Point and enjoy unlimited access to our vast library of top-rated Video Courses

Subscribe now
People having fun around a laptop

Online Certifications

Master prominent technologies at full length and become a valued certified professional.

Explore Now
People having fun around a laptop

Talk to us

1800-202-0515