Oracle PL SQL Training

I enjoyed the class and learned a lot in a short period of time. I'm looking forward to applying the knowledge that I have gained to our environment.

or call us on (866)-471-0059

Advanced Oracle SQL Queries

PL/SQL is Oracle’s extension language for standard SQL. In this PL/SQL training class, students who already know SQL, learn to use PL/SQL to write sophisticated queries against an Oracle database. Class Goals

Prerequisites and Class Materials

Prerequisites A basic uderstanding of Oracle SQL Class Materials Each student in our Live Online and our Onsite classes receives a comprehensive set of materials, including course notes and all the class examples.

Course Outline

  • The Environment for PL/SQL Development
    • Prerequisites
      • File Naming Conventions
    • Connecting to Oracle
      • SQL*Developer
      • SQL*Plus
    • Executing PL/SQL Code
    • SQL Developer Configuration
    • The PL/SQL Development Cycle
  • PL/SQL Basics
    • PL/SQL Block
    • Anonymous Block Structure
    • Named Block Structure
    • Executing Blocks
    • Calling PL/SQL Functions
    • Executing PL/SQL Blocks and Functions
  • Declaring Variables
    • Variable Usage
    • Variable Data Types
    • Variable Naming
    • Variable Assignment
      • Variable Display
    • Complex Variable Types
    • More Information
    • Variable Declaration, Initialization and Display
  • Within the Block
    • Conditional Processing
      • The IF Statement
      • The CASE Statement
    • Iterative Processing
      • The LOOP Statement
      • The WHILE Statement
      • The FOR Statement
    • Salary Classification using Conditional Statements
    • Salary Increases using Loops
  • Handling Exceptions
    • Overview of Exceptions
    • Causing System Generated Exceptions
    • Handling System Generated Exceptions
    • Identifying System Generated Exceptions
    • OTHERS Exception Handler
    • User Defined Exceptions
    • Exception Handling for Invalid Salary
  • Use of SQL in PL/SQL
    • Implicit Cursors
    • %TYPE and %ROWTYPE Attributes
    • EXECUTE IMMEDIATE statement
    • Cursors Attributes
    • Implicit Cursor
    • Explicit Cursors
    • Explicit Cursor
    • Cursor FOR loop
  • Nested Blocks
    • Nesting Blocks
    • Scope of Variables
    • Scope of Exceptions
    • Nested Subprograms
    • Exception Propagation in Nested Blocks
    • Block Variable VisibilityIntroducing Subprograms
  • Introducing Subprograms
    • Anonymous blocks
    • Types of Subprograms
    • Finding Subprograms through SQLDeveloper
    • Finding Subprograms in the Oracle Data Dictionary
    • Object Dependencies
    • Subprogram information in the Oracle Data Dictionary
  • Stored Procedures and Functions
    • Creating Subprograms
    • Modifying Subprograms
    • Removing Subprograms
    • Application Maintenance
    • Exercise Title
    • Parameters
    • Cursors as Parameters
    • Subprogram Development Techniques
    • Addressing Compilation Errors
    • Directives for Debugging
    • Issues with Booleans
    • Integrated Development Environments
    • PL/SQL Subprograms with Parameters
  • Packages
    • Structure of Packages
    • Purpose of Packages
    • Wrap Utility
    • Positional vs Named Parameter Notation
    • Subprograms Omitted from Specifications
    • Using PL/SQL Packages
  • Database Triggers
    • Purpose of Triggers
    • Invocation of Triggers
    • Coding Triggers
    • Validation Trigger
    • Modifying Triggers
    • Viewing Triggers
    • Enabling/Disabling Triggers
    • Trigger Errors
    • System and User Event Triggers
    • Logon Counter Trigger
  • Data Retrieval Techniques
    • Cursor Review
    • Cursor Parameters
    • Cursor Variables
    • Dynamic SQL
    • Employee Report by State/Province
  • Using Oracle Supplied Packages
    • Oracle Supplied Packages
    • Identifying Available Packages
    • Identifying Package Subprograms
    • Selected Oracle Supplied Package Demos
    • Packages related to Input/Output
    • Packages related to Networking
    • DBMS_SQL: Dynamic SQL
    • DBMS_URL: Working with URLS
    • The DBMS_APPLICATION_INFO
  • Specialized Topics
    • Application Partitioning
    • Subprograms in other Languages
    • Oracle Object Features
    • Statistical Analysis
    • Data Mining
    • Use Oracle Object Features

asdf

  1. The Environment for PL/SQL Development
    1. Prerequisites
      1. File Naming Conventions
    2. Connecting to Oracle
      1. SQL*Developer
      2. SQL*Plus
    3. Executing PL/SQL Code
    4. SQL Developer Configuration
    5. The PL/SQL Development Cycle
  2. PL/SQL Basics
    1. PL/SQL Block
    2. Anonymous Block Structure
    3. Named Block Structure
    4. Executing Blocks
    5. Calling PL/SQL Functions
    6. Executing PL/SQL Blocks and Functions
  3. Declaring Variables
    1. Variable Usage
    2. Variable Data Types
    3. Variable Naming
    4. Variable Assignment
      1. Variable Display
    5. Complex Variable Types
    6. More Information
    7. Variable Declaration, Initialization and Display
  4. Within the Block
    1. Conditional Processing
      1. The IF Statement
      2. The CASE Statement
    2. Iterative Processing
      1. The LOOP Statement
      2. The WHILE Statement
      3. The FOR Statement
    3. Salary Classification using Conditional Statements
    4. Salary Increases using Loops
  5. Handling Exceptions
    1. Overview of Exceptions
    2. Causing System Generated Exceptions
    3. Handling System Generated Exceptions
    4. Identifying System Generated Exceptions
    5. OTHERS Exception Handler
    6. User Defined Exceptions
    7. Exception Handling for Invalid Salary
  6. Use of SQL in PL/SQL
    1. Implicit Cursors
    2. %TYPE and %ROWTYPE Attributes
    3. EXECUTE IMMEDIATE statement
    4. Cursors Attributes
    5. Implicit Cursor
    6. Explicit Cursors
    7. Explicit Cursor
    8. Cursor FOR loop
  7. Nested Blocks
    1. Nesting Blocks
    2. Scope of Variables
    3. Scope of Exceptions
    4. Nested Subprograms
    5. Exception Propagation in Nested Blocks
    6. Block Variable Visibility

  1. Introducing Subprograms
    1. Anonymous blocks
    2. Types of Subprograms
    3. Finding Subprograms through SQLDeveloper
    4. Finding Subprograms in the Oracle Data Dictionary
    5. Object Dependencies
    6. Subprogram information in the Oracle Data Dictionary
  2. Stored Procedures and Functions
    1. Creating Subprograms
    2. Modifying Subprograms
    3. Removing Subprograms
    4. Application Maintenance
    5. Exercise Title
    6. Parameters
    7. Cursors as Parameters
    8. Subprogram Development Techniques
    9. Addressing Compilation Errors
    10. Directives for Debugging
    11. Issues with Booleans
    12. Integrated Development Environments
    13. PL/SQL Subprograms with Parameters
  3. Packages
    1. Structure of Packages
    2. Purpose of Packages
    3. Wrap Utility
    4. Positional vs Named Parameter Notation
    5. Subprograms Omitted from Specifications
    6. Using PL/SQL Packages
  4. Database Triggers
    1. Purpose of Triggers
    2. Invocation of Triggers
    3. Coding Triggers
    4. Validation Trigger
    5. Modifying Triggers
    6. Viewing Triggers
    7. Enabling/Disabling Triggers
    8. Trigger Errors
    9. System and User Event Triggers
    10. Logon Counter Trigger
  5. Data Retrieval Techniques
    1. Cursor Review
    2. Cursor Parameters
    3. Cursor Variables
    4. Dynamic SQL
    5. Employee Report by State/Province
  6. Using Oracle Supplied Packages
    1. Oracle Supplied Packages
    2. Identifying Available Packages
    3. Identifying Package Subprograms
    4. Selected Oracle Supplied Package Demos
    5. Packages related to Input/Output
    6. Packages related to Networking
    7. DBMS_SQL: Dynamic SQL
    8. DBMS_URL: Working with URLS
    9. The DBMS_APPLICATION_INFO
  7. Specialized Topics
    1. Application Partitioning
    2. Subprograms in other Languages
    3. Oracle Object Features
    4. Statistical Analysis
    5. Data Mining
    6. Use Oracle Object Features

wtf

  1. The Environment for PL/SQL Development
    1. Prerequisites
      1. File Naming Conventions
    2. Connecting to Oracle
      1. SQL*Developer
      2. SQL*Plus
    3. Executing PL/SQL Code
    4. SQL Developer Configuration
    5. The PL/SQL Development Cycle
  2. PL/SQL Basics
    1. PL/SQL Block
    2. Anonymous Block Structure
    3. Named Block Structure
    4. Executing Blocks
    5. Calling PL/SQL Functions
    6. Executing PL/SQL Blocks and Functions
  3. Declaring Variables
    1. Variable Usage
    2. Variable Data Types
    3. Variable Naming
    4. Variable Assignment
      1. Variable Display
    5. Complex Variable Types
    6. More Information
    7. Variable Declaration, Initialization and Display
  4. Within the Block
    1. Conditional Processing
      1. The IF Statement
      2. The CASE Statement
    2. Iterative Processing
      1. The LOOP Statement
      2. The WHILE Statement
      3. The FOR Statement
    3. Salary Classification using Conditional Statements
    4. Salary Increases using Loops
  5. Handling Exceptions
    1. Overview of Exceptions
    2. Causing System Generated Exceptions
    3. Handling System Generated Exceptions
    4. Identifying System Generated Exceptions
    5. OTHERS Exception Handler
    6. User Defined Exceptions
    7. Exception Handling for Invalid Salary
  6. Use of SQL in PL/SQL
    1. Implicit Cursors
    2. %TYPE and %ROWTYPE Attributes
    3. EXECUTE IMMEDIATE statement
    4. Cursors Attributes
    5. Implicit Cursor
    6. Explicit Cursors
    7. Explicit Cursor
    8. Cursor FOR loop
  7. Nested Blocks
    1. Nesting Blocks
    2. Scope of Variables
    3. Scope of Exceptions
    4. Nested Subprograms
    5. Exception Propagation in Nested Blocks
    6. Block Variable Visibility
  1. Introducing Subprograms
    1. Anonymous blocks
    2. Types of Subprograms
    3. Finding Subprograms through SQLDeveloper
    4. Finding Subprograms in the Oracle Data Dictionary
    5. Object Dependencies
    6. Subprogram information in the Oracle Data Dictionary
  2. Stored Procedures and Functions
    1. Creating Subprograms
    2. Modifying Subprograms
    3. Removing Subprograms
    4. Application Maintenance
    5. Exercise Title
    6. Parameters
    7. Cursors as Parameters
    8. Subprogram Development Techniques
    9. Addressing Compilation Errors
    10. Directives for Debugging
    11. Issues with Booleans
    12. Integrated Development Environments
    13. PL/SQL Subprograms with Parameters
  3. Packages
    1. Structure of Packages
    2. Purpose of Packages
    3. Wrap Utility
    4. Positional vs Named Parameter Notation
    5. Subprograms Omitted from Specifications
    6. Using PL/SQL Packages
  4. Database Triggers
    1. Purpose of Triggers
    2. Invocation of Triggers
    3. Coding Triggers
    4. Validation Trigger
    5. Modifying Triggers
    6. Viewing Triggers
    7. Enabling/Disabling Triggers
    8. Trigger Errors
    9. System and User Event Triggers
    10. Logon Counter Trigger
  5. Data Retrieval Techniques
    1. Cursor Review
    2. Cursor Parameters
    3. Cursor Variables
    4. Dynamic SQL
    5. Employee Report by State/Province
  6. Using Oracle Supplied Packages
    1. Oracle Supplied Packages
    2. Identifying Available Packages
    3. Identifying Package Subprograms
    4. Selected Oracle Supplied Package Demos
    5. Packages related to Input/Output
    6. Packages related to Networking
    7. DBMS_SQL: Dynamic SQL
    8. DBMS_URL: Working with URLS
    9. The DBMS_APPLICATION_INFO
  7. Specialized Topics
    1. Application Partitioning
    2. Subprograms in other Languages
    3. Oracle Object Features
    4. Statistical Analysis
    5. Data Mining
    6. Use Oracle Object Features
View instant course pricing
Ready to get started? Get instant pricing for this award-winning boot camp. View course pricing

New Exam Pass Guarantee!

Our new Live Online format has produced excellent results. We trust the course quality will help you pass on your first exam attempt and this exam-pass guarantee backs up that trust.