Welcome to Comnet Group Inc.


Oracle PL/SQL

Course number: CGIPLSQL40

The Oracle PL/SQL Course provides you with the complete skills needed to create, implement and manage robust database applications using the Oracle database tools. Some of the topics covered are: understanding of the Basic Procedural Language/Structured Query Language, subprogram, section and syntax query, DML, advanced DML, and scripting. The entire training is in line with the Oracle PL SQL certification.

  • There are no particular prerequisites to learn PL/SQL, although having prior knowledge of SQL can be beneficial.
Target Audience
  • Software Developers
  • Database Administrators
  • SQL and Analytics Professionals
  • BI and Data Warehousing Professionals
  • Those aspiring for a career in Oracle PL/SQL
Course Outline
Introduction to Oracle SQL
  • What is RDBMS?
  • Oracle versions
  • The architecture of Oracle Database Server
  • Installation of Oracle 12c
  • Install Oracle 12c
Using DDL Statements to Create and Manage Tables
  • Categorize the main database objects
  • Review the table structure, List the data types that are available for columns
  • Create a simple table
  • Explain how constraints are created at the time of table creation
  • Describe how schema objects work
  • Create a database table “Person” with two columns (Name, Age),  with a constraint on age not greater than 100; Insert records using an insert query
Retrieving Data Using the SQL SELECT Statement
  • List the capabilities of SQL SELECT statements
  • Execute a basic SELECT statement
  • Use a basic select statement to retrieve all the records in the “Person” table
Restricting and Sorting Data
  • Limit the rows that are retrieved by a query
  • Sort the rows that are retrieved by a query
  • Ampersand substitution to restrict and sort output at runtime
  • Write a select query to retrieve records where age is more than  60 yrs; Write a select query to sort the records by name
  • Write a select query to sort the records by age in descending order
  • Use ampersand substitution to restrict and sort output at runtime
General Functions
  • The general functions in SQL
  • Working with any data type and handling Null values, using COALESCE() and Null function
  • Constructing and executing SQL query that applies the NUL, NUL1, NUL2 and COALESCE()
  • Use the NULL function to deal with null values in data
Using Single-Row Functions to Customize Output
  • Describe various types of functions (character, number, date, string etc.) available in SQL
  • Create a table with columns of type char, number, and date
  • Use character, number, and date functions in SELECT statements
Large Object Functions
  • Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB
  • Aggregate or Group functions – COUNT,
  • COUNT(*),MIN,MAX,SUM,AVG,etc…,
  • Group BY Clause, HAVING Clause
  • Count records based on a condition; Use Count(*) to know the count of all records, Find Max, Min, Sum, Avg
OLAP Functions
  • The various OLAP functions, cube, model clause, roll up and grouping functions
  • Working with OLAP commands – Cube, Roll Up, etc.
Using Conversion Functions and Conditional Expressions
  • Describe various types of conversion functions that are available in SQL
  • Conditional expressions in a SELECT statement
  • Group data by using the GROUP BY clause; Include or exclude grouped rows by using the HAVING clause
Displaying Data from Multiple Tables
  • Joins, Inner Join, Outer Join, Left Join, Right Join, Equijoins and Non-equijoins
  • Write SELECT statements to access data from more than one table using equijoins and non-equijoins; Join a table to itself by using a self-join;  View data that generally does not meet a join condition by using outer joins; Generate a Cartesian product of all rows from two or more tables
Using Subqueries to Solve Queries
  • Define subqueries
  • Describe the types of problems that the subqueries can solve
  • List the types of subqueries
  • Write single-row and multiple- row subqueries
Using the set of Operators
  • Describe set operators, UNION [ALL], INTERSECT, MINUS Operators
  • Use a set operator to combine multiple queries into a single query; Control the order of rows returned
Manipulating Data using SQL
  • Describe data manipulation language (DML) statement, Insert, Update, Delete Statements, Control transactions
  • Insert rows into a table; Update rows in a table; Delete rows from a table
Database Transactions
  • What is a database transaction?
  • Properties of a transaction (Atomic, Consistent, Isolated, Durable – ACID)
  • Avoiding error/fault in manipulating database records using transaction
  • Begin a transaction; Execute queries to update or insert or delete records; If no error, commit the transaction Else to roll back the transaction and end it
Creating Other Schema Objects
  • Views – simple and complex, Sequences, Index, Synonym
  • Create simple and complex views; Retrieve data from views; Create, maintain, and use sequences, Create and maintain indexes, Create
Writing Cursor and Conditional Statement
  • SQL Cursor, SQL Cursor Attributes
  • Controlling PL/SQL flow of executions
  • IF Statement, Simple IF Statement, IF-THAN-ELSE Statement Execution Flow, IF-THAN-ELSE Statement, IF-THAN-ELSIF Statement
  • Logic Tables, Boolean Conditions
  • Iterative controlling loop statement, Nested Loops and Labels

Use Boolean condition on a select query; Use logic table;  Create nested loops and labels

Introduction to Explicit Cursor
  • Writing Explicit cursors, About cursors
  • Explicit cursor functions, Controlling explicit cursor, Opening the cursor
  • Fetching Data cursor, Cursor, and records, Cursor for loop using sub queries
  • Write an explicit cursor; Use cursor function; Fetch data cursor
Advance Concept of Explicit Cursor
  • Advance Explicit cursor
  • A cursor with parameters, For update Clause
  • Where current of Clause, Cursor with sub queries
  • Use cursor with a subquery; Use Where Current clause to retrieve data
Exception Handling
  • Handling Exception, Handling Exception with PL/SQLPredefined Exceptions
  • User Defined Exceptions, Non-Predefined Error, Function for trapping Exception
  • Trapping user-defined Exception
  • Raise Application Error Procedure
  • Use Predefined exception; Write user-defined exception; Generate and handle the exception; Use a function for trapping an exception
Writing Subprogram, Procedure and passing parameters
  • Overview of subprograms, PL/SQL Subprograms
  • What is Procedure?
  • The syntax for creating Procedure
  • Creating Procedure with a parameter, Example of Passing parameters
  • Referencing a public variable from a standalone procedure
  • Declaring Subprogram
  • Create a parameterized procedure, Pass parameters in a procedure call
Creating PL/SQL Package
  • PL/SQL records, Using Pl/SQL Table method and example
  • Creating PL/SQL Table
  • Packages – Objective, overview, component, developing, removing, advantages
  • Creating the package specification/example
  • Declaring Public construct, Public and private construct, Invoking package construct
  • Guide lines for deploying packages
  • Create a package; Deploy the created package
Advance Package Concepts and Functions
  • Overloading, Using forward declaration, One time only procedure, Package functions
  • User define package function, Persistent state of package function, Persistent state of a package variable
  • Controlling the persistent state of package cursor
  • Purity end, Using supplied package
  • Using native dynamic SQL, Execution flow
  • Using DBMS-SQL package, Using DBMS-DDL package, Submitting jobs
  • Interacting with operating system links
  • Use supplied package, Use native dynamic SQL, Use DBMS- SQL package
  • Use DBMS-DDL package, Submit a job
Introduction and writing Triggers
  • Triggers – Definition, objective and its event type, Application & database triggers
  • Business application scenarios for implementing triggers
  • Define DML triggers, Define Non – DML triggers, Triggers event type & body
  • Creating DML triggers using the create triggers statement
  • Define statement level triggers v/s low-level triggers
  • Triggers firing sequence: single row manipulation
  • Creating a DML statement triggers
  • Using old and new qualifiers
  • Old and new qualifiers, Instead of triggers
  • Managing triggers using the alter & drop SQL statement, Testing triggers
  • Create a DML statement trigger; Use old and new qualifiers; Manage a trigger using the alter & drop SQL statement; Test the created triggers
Compound Triggers
  • Viewing trigger information
  • Describe user triggers
  • What is a compound trigger and working with it?
  • Compound trigger structure for tables, Timing-point sections of a table compound trigger
  • Compound trigger structure for views
  • Trigger restrictions on mutating tables
  • Compound trigger restrictions, Using a compound trigger to resolve the mutating table error
  • Creating triggers on system events, LOGON and LOGOFF triggers example
  • Call statements in triggers
  • Benefits of database-event triggers
  • System privileges required to manage triggers
  • View a trigger’s information; Use a compound trigger structure for views
  • Use a compound trigger to resolve the mutating table error
  • Create triggers on system events; Use LOGON and LOGOFF triggers; Call statements in triggers
Working with Dynamic SQL
  • Dynamic SQL-objectives
  • Describe execution flow of SQL statements
  • Dynamic SQL with a DDL statement-example
  • Working with dynamic SQL
  • Native Dynamic SQL(NDS), Using the executive immediate statement-example, Using native dynamic SQL to compile PL/SQL code, Using DBMS-SQL with a DML statement, Using the DBMS-SQL package subprograms, Parameterized DML statement
  • Use the executive immediate statement
  • Use native dynamic SQL to compile PL/SQL code
  • Create DBMS-SQL with a DML statement, Create a DBMS-SQL package subprograms
Advance Level Scripting
  • Managing Dependencies, Objectives, overview of schema object dependencies, Direct local dependencies
  • Querying direct object dependencies, Displaying direct and indirect dependencies
  • Fine-Grained dependency management, Changes to synonym dependencies
  • Maintaining valid PL/SQL program units and views, Object re-validation
  • Concepts of remote dependencies
  • Setting the remote dependencies mode parameter
  • Recompiling PL/SQL program unit
  • Packages and Dependencies, Successful and unsuccessful recompilation
  • Recompiling procedures
  • Query direct object dependencies
  • Display direct and indirect dependencies; Set the remote dependencies mode parameter
  • Recompile PL/SQL program unit; Edit a procedure and recompile it
  • Performing DML operations on the database
  • Streamlining banking data with PL/SQL
  • Telecom Company wants to learn more about customers
  • An airline company wants to optimize routes & seats

Available Formats

Live Online