Thank you for sending your enquiry! One of our team members will contact you shortly.
Thank you for sending your booking! One of our team members will contact you shortly.
Course Outline
Introduction
- Aims and Objectives
- Course Schedule
- Introductions
- Pre-requisites
- Responsibilities
SQL Tools
- Objectives
- SQL Developer
- SQL Developer - Connection
- Viewing Table Information
- Using SQL, SQL Developer - Query
- SQL*Plus Login
- Direct Connection
- Using SQL*Plus
- Ending the Session
- SQL*Plus Commands
- SQL*Plus Environment
- SQL*Plus Prompt
- Finding Information about Tables
- Getting Help
- Using SQL Files
- iSQL*Plus, Entity Models
- The ORDERS Tables
- The FILM Tables
- Course Tables Handout
- SQL Statement Syntax
- SQL*Plus Commands
What is PL/SQL?
- What is PL/SQL?
- Why Use PL/SQL?
- Block Structure
- Displaying a Message
- Sample Code
- Setting SERVEROUTPUT
- Update Example, Style Guide
Variables
- Variables
- Datatypes
- Setting Variables
- Constants
- Local and Global Variables
- %Type Variables
- Substitution Variables
- Comments with &
- Verify Option
- && Variables
- Define and Undefine
SELECT Statement
- SELECT Statement
- Populating Variables
- %Rowtype Variables
- CHR Function
- Self Study
- PL/SQL Records
- Example Declarations
Conditional Statement
- IF Statement
- SELECT Statement
- Self Study
- Case Statement
Trapping Errors
- Exception
- Internal Errors
- Error Code and Message
- Using No Data Found
- User Exceptions
- Raise Application Error
- Trapping Non-defined Errors
- Using PRAGMA EXCEPTION_INIT
- Commit and Rollback
- Self Study
- Nested Blocks
- Workshop
Iteration - Looping
- Loop Statement
- While Statement
- For Statement
- Goto Statement and Labels
Cursors
- Cursors
- Cursor Attributes
- Explicit Cursors
- Explicit Cursor Example
- Declaring the Cursor
- Declaring the Variable
- Open, Fetching the First Row
- Fetching the Next Row
- Exit When %Notfound
- Close
- For Loop I
- For Loop II
- Update Example
- FOR UPDATE
- FOR UPDATE OF
- WHERE CURRENT OF
- Commit with Cursors
- Validation Example I
- Validation Example II
- Cursor Parameters,
- Workshop
- Workshop Solution
Procedures, Functions and Packages
- Create Statement
- Parameters
- Procedure Body
- Showing Errors
- Describe a Procedure
- Calling Procedures
- Calling Procedures in SQL*Plus
- Using Output Parameters
- Calling with Output Parameters
- Creating Functions
- Example Function
- Showing Errors
- Describe a Function
- Calling Functions
- Calling Functions in SQL*Plus
- Modular Programming
- Example Procedure
- Calling Functions
- Calling Functions In An IF Statement
- Creating Packages
- Package Example
- Reasons for Packages
- Public and Private Sub-programs
- Showing Errors
- Describe a Package
- Calling Packages in SQL*Plus
- Calling Packages From Sub-Programs
- Dropping a Sub-Program
- Finding Sub-programs
- Creating a Debug Package
- Calling the Debug Package
- Positional and Named Notation
- Parameter Default Values
- Recompiling Procedures and Functions
- Workshop
Triggers
- Creating Triggers
- Statement Triggers
- Row Level Triggers
- WHEN Restriction
- Selective Triggers - IF
- Showing Errors
- Commit in Triggers
- Restrictions
- Mutating Triggers
- Finding Triggers
- Dropping a Trigger
- Generating an Auto-number
- Disabling Triggers
- Enabling Triggers
- Trigger Names
Sample Data
- ORDER Tables
- FILM Tables
- EMPLOYEE Tables
Dynamic SQL
- SQL in PL/SQL
- Binding
- Dynamic SQL
- Native Dynamic SQL
- DDL and DML
- DBMS_SQL Package
- Dynamic SQL - SELECT
- Dynamic SQL - SELECT Procedure
Using Files
- Using Text Files
- UTL_FILE Package
- Write/Append Example
- Read Example
- Trigger Example
- DBMS_ALERT Packages
- DBMS_JOB Package
COLLECTIONS
- %Type Variables
- Record Variables
- Collection Types
- Index-By Tables
- Setting Values
- Nonexistent Elements
- Nested Tables
- Nested Table Initialisation
- Using the Constructor
- Adding to a Nested Table
- Varrays
- Varray Initialization
- Adding Elements to a Varray
- Multilevel Collections
- Bulk Bind
- Bulk Bind Example
- Transactional Issues
- BULK COLLECT Clause
- RETURNING INTO
Ref Cursors
- Cursor Variables
- Defining REF CURSOR Types
- Declaring Cursor Variables
- Constrained and Unconstrained
- Using Cursor Variables
- Cursor Variable Examples
Requirements
The course is only suitable for those who have some knowledge of SQL.
Previous experience with an interactive computer system is desirable but not essential.
21 Hours
Testimonials (7)
The topic was interesting, and the trainer was very friendly and has adept knowledge of the subject.
Jacqueline - Tribal Grou[
Course - Developing Applications with Oracle Application Express (APEX)
The way trainer demonstrated everything
Manveen - Tribal Grou[
Course - Developing Applications with Oracle Application Express (APEX)
During the training, all modules were discussed in great detail - for two days of training - and that was what I cared about the most.
Bernadetta - Urząd Komisji Nadzoru Finansowego
Machine Translated
- I really liked that the entire training was tailored to the level of all users and to different environments. It was cool to see how queries differ in different environments. - A huge advantage here is that the presenter's goal was to share his knowledge with us, and not to show the participants how smart he is:) - I also consider learning from my own and other people's mistakes to be very valuable because it significantly broadens your horizons.
Kasia
Course - SQL Advanced
Machine Translated
Exercises are entertaining and the topics were nicely introduced.
Adrian-Madalin Chirita - DB Global Technology SRL
Course - Oracle SQL Advanced
The trainer knowledge and style of explication
Vlad-Mihai Nicolae - DB Global Technology SRL
Course - Oracle SQL Advanced
I appreciated Folio's wide breadth of knowledge. Not only was he familiar with the course content, but he also knew of constructs in languages we were familiar with to make examples more meaningful to us. During intervals he shared his knowledge of technologies and solutions outside the training scope to provide insights into other solutions we could use in future (and future training).
