ORACLE PL-SQL TRAINING

ORACLE PL/SQL TRAINING

Oracle PL/SQL Training Course includes basic to advanced level and our Oracle PL/SQ course is designed to get the placement in good MNC companies in Chennai as quickly as once you complete the Oracle PL/SQL courses in Chennai with placement. Our Oracle PL/SQL trainers are Oracle PL/SQL certified experts and experienced working professionals with hands on real time multiple Oracle PL/SQL projects knowledge. We have designed our Oracle PL/SQL course content and syllabus based on students requirement to achieve everyone's career goal. In our Oracle PL/SQL training program, you will learn from the scratch to advanced level along with Oracle PL/SQL real time.

ORACLE SQL TRAINING COURSE SYLLABUS
This Oracle Database: SQL and PL/SQL training delivers the fundamentals of SQL and PL/SQL along with the benefits of the programming languages using Oracle Database technology. You'll explore the concepts of relational databases.

1. Basic DATABASE Concept and SQL

•  Basic history of database concept: DBMS, RDBMS, ORDBMS
•  Advantage of ORACLE database and version information
•  Interface tools usage: sqlplus, isqlplus, sqldeveloper, Toad
•  SQL Language overview : DQL, DML, DDL, DCL, TCL
•  What is the usage of ANSI standard.
•  SELECT Command - Column Alias Rules, String data,
•  Concatenations with various data
•  Null Value handling with number and characters,
•  Arithmetic Operator
•  Concatenation Operator,
•  Eliminating Duplicate Rows

2. Restricting and Sorting Data

•  WHERE Clause - Character Strings and Dates, number
•  General Comparison Conditions = > >= < <= <>
•  Other Comparison BETWEEN , IN , LIKE , NULL
•  Logical Conditions AND OR NOT
•  ORDER BY Clause, Sorting by Column Alias , Column Position, Multiple Columns

3. Single-Row Functions

•  Character Functions: UPPER, LOWER, INITCAP, LENGTH, SUBSTR, INSTR, LPAD, RPAD, CONCAT, LTRIM, RTRIM, TRIM, REPLACE, TRANSLATE, REVERSE
•  Number Functions: ROUND, TRUNC, MOD, POWER, CEIL , FLOOR, ABS
•  Dates Functions: SYSDATE, MONTHS_BETWEEN, NEXT_DAY, LAST_DAY, ADD_MONTHS, ROUND, TRUNC, Arithmetic on Date
•  Conversion Functions: Implicit Data-Type Conversion & Explicit Data-Type Conversion, TO_CHAR ,TO_NUMBER ,TO_DATE
•  General Functions: NVL , NVL2 , NULLIF, COALESCE
•  CASE Expression, DECODE
•  Nested function with real-time usage

4. JOINS

•  EQUI JOIN / SIMPLE JOIN / NORMAL JOIN
•  ANSI JOIN, LEFT OUTER, RIGHT OUTER, FULL OUTER
•  NATURAL JOIN, NATURAL OUTER JOINS
•  INNER JOIN, JOIN ... USING clause, JOIN ... ON clause,
•  CROSS JOIN, NON-EQUI JOIN, SELF JOIN
•  ORACLE STANDARD OUTER JOINS.
•  Multi table Joins, Complex Joins – How to simplified complex joins.

5. Multi-row Functions

•  Group Functions Rules, SUM, MIN, MAX, COUNT, AVG
•  Creating Groups of Data: GROUP BY Clause
•  Filtering Group Results: The HAVING Clause

6. Sub-queries

•  Single-Row Subqueries- Rules, Operators : = > >= < <= <>
•  Null Values in a Subquery
•  Multi-Row Subqueries- Rules, Operators : IN, ANY , ALL

7. Reporting data using interface commands

•  pagesize, linesize , column heading , column format , colsep
•  Title , bTitle , break on column, spool , CSV file generation, Text file generation

8. Data Manipulation Language DML and Transaction Control Language TCL

•  DML : INSERT, UPDATE, DELETE, MERGE
•  TCL : COMMIT, ROLLBACK, SAVEPOINT

9. Data Definition Language - DDL

•  DDL : CREATE, ALTER, RENAME, DROP, TRUNCATE
•  DEFAULT OPTION.
•  Constrain table copy

10. Constraints

•  NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK
•  Column Level Constraint, Table Level Constraint – Naming constraints and usage
•  Adding a Constraint, Dropping a Constraint,
•  Disabling Constraints, Enabling Constraints
•  Validating Constraints

11. Views

•  Simple Views and Complex Views – Create, Drop, Source Code
•  Rules for Performing DML Operations on a View
•  WITH CHECK OPTION , WITH READ ONLY
•  Inline Views
•  Materialized View – Create, Refresh, Drop - Usage

12. Other Database Objects

•  Sequence- NEXTVAL and CURRVAL
•  Index - When to Create an Index, When Not to Create an Index.
•  Synonyms

13. DCL COMMANDS

•  Creating Users
•  Granting / Revoking Privileges
•  Creating and Granting Privileges to a Role

14. Dictionary Tables

•  Tables, Views, Synonyms, Index, Sequence, Constrains, Source and other Dictionary

15. SET Operators

•  UNION ,
•  UNION ALL ,
•  INTERSECT ,
•  MINUS

16. Advanced Date-time Functions

•  TIME ZONES ,
•  SYSDATE, SYSTIMESTAMP,
•  CURRENT_DATE , CURRENT_TIMESTAMP
•  SESSIONTIMEZONE ,
•  Storing time zone data in Table
•  EXTRACT ,
•  TO_YMINTERVAL

17. Advanced GROUP BY Clause

•  Group by with ROLLUP,
•  Group by with CUBE,
•  GROUPING SETS

18. Advanced Subqueries

•  Pairwise Comparison Subquery ,
•  Nonpairwise Comparison Subquery
•  Correlated Subqueries,
•  Correlated UPDATE,
•  Correlated DELETE
•  EXISTS , NOT EXISTS Operator

19. Hierarchical Retrieval

•  Walking the Tree: From the Bottom Up , From the Top Down
•  LEVEL Pseudo column,
•  Connect by prior,

20. Multi-table Insert

•  Unconditional INSERT ALL
•  Conditional INSERT ALL
•  Conditional FIRST INSERT

21. DATA LOADER –

•  SQLLDR – Loading CSV file / Flat file into ORACLE table.

22. Analytic Functions

•  WM_CONCAT, LAG, LEAD, RANK, DENSE_RANK
•  Query_by partition_clause with sum, min, max, avg, count,
•  order_by_clause with sum, min, max, avg, count,
•  Psudo column : Rownum, Rowid, - Elimination duplicate data
•  Connect by rownum , Connect by Level – Generating random numbers, random dates,
•  Quote Operator syntax and usage

23. Backup

•  Export / Import SCHEMA

24. General discussion

•  What is migration?
•  Migration Estimating, Planning, Preparation – Simple Scenario / Complex Scenario.

 
ANALYTIC SQL FOR DATA WAREHOUSE DEVELOPERS COURSE
CONTENT

ORACLE DATABASE SQL AND DATA WAREHOUSING TRAINING
 
GROUPING AND AGGREGATING DATA USING SQL

•  Generating Reports by Grouping Related Data
•  Review of Group Functions
•  Reviewing GROUP BY and HAVING Clause
•  Using the ROLLUP and CUBE Operators
•  Using the GROUPING Function
•  Working with GROUPING SET Operators and Composite Columns
•  Using Concatenated Groupings with Example

HIERARCHICAL RETRIEVAL

•  Using Hierarchical Queries
•  Sample Data from the EMPLOYEES Table
•  Natural Tree Structure
•  Hierarchical Queries: Syntax
•  Walking the Tree: Specifying the Starting Point
•  Walking the Tree: Specifying the Direction of the Query
•  Using the WITH Clause
•  Hierarchical Query Example: Using the CONNECT BY Clause

WORKING WITH REGULAR EXPRESSIONS

•  Introducing Regular Expressions
•  Using the Regular Expressions Functions and Conditions in SQL and PL/SQL
•  Introducing Metacharacters
•  Using Metacharacters with Regular Expressions
•  Regular Expressions Functions and Conditions: Syntax
•  Performing a Basic Search Using the REGEXP_LIKE Condition
•  Finding Patterns Using the REGEXP_INSTR Function
•  Extracting Substrings Using the REGEXP_SUBSTR Function

ANALYZING AND REPORTING DATA USING SQL

•  Overview of SQL for Analysis and Reporting Functions
•  Using Analytic Functions
•  Using the Ranking Functions
•  Using Reporting Functions

PERFORMING PIVOTING AND UNPIVOTING OPERATIONS

•  Performing Pivoting Operations
•  Using the PIVOT and UNPIVOT Clauses
•  Pivoting on the QUARTER Column: Conceptual Example
•  Performing Unpivoting Operations
•  Using the UNPIVOT Clause Columns in an UNPIVOT Operation
•  Creating a New Pivot Table: Example

PATTERN MATCHING USING SQL

•  Row Pattern Navigation Operations
•  Handling Empty Matches or Unmatched Rows
•  Excluding Portions of the Pattern from the Output
•  Expressing All Permutations
•  Rules and Restrictions in Pattern Matching
•  Examples of Pattern Matching

MODELING DATA USING SQL

•  Using the MODEL clause
•  Demonstrating Cell and Range References
•  Using the CV Function
•  Using FOR Construct with IN List Operator, incremental values and Subqueries
•  Using Analytic Functions in the SQL MODEL Clause
•  Distinguishing Missing Cells from NULLs
•  Using the UPDATE, UPSERT and UPSERT ALL Options
•  Reference Models

ORACLE SQL STATEMENT TUNING COURSE CONTENT
EXPLORING THE ORACLE DATABASE ARCHITECTURE

•  Describe the major architectural components of Oracle Database server
•  Explain memory structures
•  Describe background processes
•  Correlate logical and physical storage structures

INTRODUCTION TO SQL TUNING

•  Describe what attributes of a SQL statement can make it perform poorly
•  Describe the Oracle tools that can be used to tune SQL
•  Explain the tuning tasks

INTRODUCTION TO THE OPTIMIZER

•  Describe the execution steps of a SQL statement
•  Explain the need for an optimizer
•  Explain the various phases of optimization
•  Control the behavior of the optimizer

INTERPRETING EXECUTION PLANS

•  Gather execution plans
•  Display execution plans, display xplan
•  Interpret execution plans

APPLICATION TRACING

•  Configure the SQL Trace facility to collect session statistics
•  Use the trcsess utility to consolidate SQL trace files
•  Format trace files using the tkprof utility
•  Interpret the output of the tkprof command

 OPTIMIZER OPERATIONS

•  Describe the SQL operations for tables and indexes
•  Describe the possible access paths for tables and indexes

OPTIMIZER: JOIN OPERATIONS

•  Describe the SQL operations for joins
•  Describe the possible access paths for joins

OTHER OPTIMIZER OPERATIONS

•  Describe Clusters, In-List, Sorts, Filters and Set Operations
•  Use Result Cache operations

CASE STUDY: STAR TRANSFORMATION

•  Define a star schema, a star query plan without transformation and a star query plan after transformation

OPTIMIZER STATISTICS

•  Gather optimizer statistics
•  Gather system statistics
•  Set statistic preferences
•  Use dynamic sampling
•  Manipulate optimizer statistics

USING BIND VARIABLES

•  Explain the benefits of using bind variables
•  Use bind peeking
•  Use adaptive cursor sharing

SQL TUNING ADVISOR

•  Describe statement profiling
•  Use SQL Tuning Advisor

USING SQL ACCESS ADVISOR

•  Use SQL Access Advisor

AUTOMATING SQL TUNING

•  Use Automatic SQL Tuning

 SQL PLAN MANAGEMENT

•  Manage SQL performance through changes
•  Set up SQL Plan Management
•  Set up various SQL Plan Management scenarios

 USING OPTIMIZER HINTS

•  Use hints when appropriate
•  Specify hints for Optimizer mode, Query transformation, Access path, Join orders, Join methods and Views

 PARALLEL QUERIES

 PARALLEL PROCESSING CONCEPTS

•  Explain what parallel processing is and why is it useful 

BASICS OF PARALLEL EXECUTION

•  Describe operations that can be parallelized
•  Explain parallel execution theory
•  Understand impact of initiali zation parameter on parallel execution

MANUAL DOP MANAGEMENT

•  Understand an explain plan of a parallel query
•  Understand an explain plan of parallel DML and DDL

SIMPLIFIED AUTO DOP

•  Understand the new parameters of Auto DOP
•  Explain when to use Auto DOP
•  Use Auto DOP

STATEMENT QUEUING

•  Explain statement queuing, concurrency and DBRM

IN-MEMORY PARALLEL EXECUTION

•  Use in-memory parallel execution

DATA WAREHOUSE ADMINISTRATION
PARTITIONING CONCEPTS

•  Explain the available partitioning strategies
•  Explain partition pruning
•  Implement partition enhancements in star query optimization

MATERIALIZED VIEWS

•  Use summaries to improve performance
•  Differentiate materialized view types

ORACLE PLSQL TRAINING COURSE CONTENT
INTRODUCTION

•  Course Objectives
•  Course Agenda
•  Human Resources (HR) Schema
•  Introduction to SQL Developer

INTRODUCTION TO PL/SQL

•  PL/SQL Overview
•  Benefits of PL/SQL Subprograms
•  Overview of the Types of PL/SQL blocks
•  Create a Simple Anonymous Block
•  Generate Output from a PL/SQL Block

PL/SQL IDENTIFIERS

•  List the different Types of Identifiers in a PL/SQL subprogram
•  Usage of the Declarative Section to define Identifiers
•  Use variables to store data
•  Identify Scalar Data Types
•  The %TYPE Attribute
•  What are Bind Variables?
•  Sequences in PL/SQL Expressions

WRITE EXECUTABLE STATEMENTS

•  Describe Basic PL/SQL Block Syntax Guidelines
•  Comment Code
•  Deployment of SQL Functions in PL/SQL
•  How to convert Data Types?
•  Nested Blocks
•  Identify the Operators in PL/SQL

INTERACTION WITH THE ORACLE SERVER

•  Invoke SELECT Statements in PL/SQL to Retrieve data
•  Data Manipulation in the Server Using PL/SQL
•  SQL Cursor concept
•  Usage of SQL Cursor Attributes to Obtain Feedback on DML
•  Save and Discard Transactions

CONTROL STRUCTURES

•  Conditional processing Using IF Statements
•  Conditional processing Using CASE Statements
•  Use simple Loop Statement
•  Use While Loop Statement
•  Use For Loop Statement
•  Describe the Continue Statement

COMPOSITE DATA TYPES

•  Use PL/SQL Records
•  The %ROWTYPE Attribute
•  Insert and Update with PL/SQL Records
•  Associative Arrays (INDEX BY Tables)
•  Examine INDEX BY Table Methods
•  Use INDEX BY Table of Records

 EXPLICIT CURSORS

•  What are Explicit Cursors?
•  Declare the Cursor
•  Open the Cursor
•  Fetch data from the Cursor
•  Close the Cursor
•  Cursor FOR loop
•  Explicit Cursor Attributes
•  FOR UPDATE Clause and WHERE CURRENT Clause

EXCEPTION HANDLING

•  Understand Exceptions
•  Handle Exceptions with PL/SQL
•  Trap Predefined Oracle Server Errors
•  Trap Non-Predefined Oracle Server Errors
•  Trap User-Defined Exceptions
•  Propagate Exceptions
•  RAISE_APPLICATION_ERROR Procedure

 STORED PROCEDURES AND FUNCTIONS

•  Understand Stored Procedures and Functions
•  Differentiate between anonymous blocks and subprograms
•  Create a Simple Procedure
•  Create a Simple Procedure with IN parameter
•  Create a Simple Function
•  Execute a Simple Procedure
•  Execute a Simple Function

 CREATE STORED PROCEDURES

•  Create a Modularized and Layered Subprogram Design
•  Modularize Development With PL/SQL Blocks
•  Describe the PL/SQL Execution Environment
•  Identity the benefits of Using PL/SQL Subprograms
•  List the differences Between Anonymous Blocks and Subprograms
•  Create, Call, and Remove Stored Procedures Using the CREATE Command and SQL Developer
•  Implement Procedures Parameters and Parameters Modes
•  View Procedures Information Using the Data Dictionary Views and SQL Developer

CREATE STORED FUNCTIONS

•  Create, Call, and Remove a Stored Function Using the CREATE Command and SQL Developer
•  Identity the advantages of Using Stored Functions in SQL Statements
•  List the steps to create a stored function
•  Implement User-Defined Functions in SQL Statements
•  Identity the restrictions when calling Functions from SQL statements
•  Control Side Effects when calling Functions from SQL Expressions
•  View Functions Information

 CREATE PACKAGES

•  Identity the advantages of Packages
•  Describe Packages
•  List the components of a Package
•  Develop a Package
•  How to enable visibility of a Package€™s components?
•  Create the Package Specification and Body Using the SQL CREATE Statement and SQL Developer
•  Invoke Package Constructs
•  View PL/SQL Source Code Using the Data Dictionary

 PACKAGES

•  Overloading Subprograms in PL/SQL
•  Use the STANDARD Package
•  Use Forward Declarations to Solve Illegal Procedure Reference
•  Implement Package Functions in SQL and Restrictions
•  Persistent State of Packages
•  Persistent State of a Package Cursor
•  Control Side Effects of PL/SQL Subprograms
•  Invoke PL/SQL Tables of Records in Packages

 IMPLEMENT ORACLE-SUPPLIED PACKAGES IN APPLICATION DEVELOPMENT

•  What are Oracle-Supplied Packages?
•  Examples of Some of the Oracle-Supplied Packages
•  How Does the DBMS_OUTPUT Package Work?
•  Use the UTL_FILE Package to Interact With Operating System Files
•  Invoke the UTL_MAIL Package
•  Write UTL_MAIL Subprograms

DYNAMIC SQL

•  The Execution Flow of SQL
•  What is Dynamic SQL?
•  Declare Cursor Variables
•  Dynamically executing a PL/SQL Block
•  Configure Native Dynamic SQL to Compile PL/SQL Code
•  Invoke DBMS_SQL Package
•  Implement DBMS_SQL with a Parameterized DML Statement
•  Dynamic SQL Functional Completeness

 DESIGN CONSIDERATIONS FOR PL/SQL CODE

•  Standardize Constants and Exceptions
•  Understand Local Subprograms
•  Write Autonomous Transactions
•  Implement the NOCOPY Compiler Hint
•  Invoke the PARALLEL_ENABLE Hint
•  The Cross-Session PL/SQL Function Result Cache
•  The DETERMINISTIC Clause with Functions
•  Usage of Bulk Binding to Improve Performance

 TRIGGERS

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

 CREATE COMPOUND, DDL, AND EVENT DATABASE TRIGGERS

•  What are Compound Triggers?
•  Identify the Timing-Point Sections of a Table Compound Trigger
•  Compound Trigger Structure for Tables and Views
•  Implement a Compound Trigger to Resolve the Mutating Table Error
•  Compare Database Triggers to Stored Procedures
•  Create Triggers on DDL Statements
•  Create Database-Event and System-Event Triggers
•  System Privileges Required to Manage Triggers

THE PL/SQL COMPILER

•  What is the PL/SQL Compiler?
•  Describe the Initialization Parameters for PL/SQL Compilation
•  List the New PL/SQL Compile Time Warnings
•  Overview of PL/SQL Compile Time Warnings for Subprograms
•  List the benefits of Compiler Warnings
•  List the PL/SQL Compile Time Warning Messages Categories
•  Setting the Warning Messages Levels: Using SQL Developer, PLSQL_WARNINGS Initialization Parameter, and the DBMS_WARNING Package Subprograms
•  View Compiler Warnings: Using SQL Developer, SQL*Plus, or the Data Dictionary Views

 MANAGE PL/SQL CODE

•  What Is Conditional Compilation?
•  Implement Selection Directives
•  Invoke Predefined and User-Defined Inquiry Directives
•  The PLSQL_CCFLAGS Parameter and the Inquiry Directive
•  Conditional Compilation Error Directives to Raise User-Defined Errors
•  The DBMS_DB_VERSION Package
•  Write DBMS_PREPROCESSOR Procedures to Print or Retrieve Source Text
•  Obfuscation and Wrapping PL/SQL Code

 MANAGE DEPENDENCIES

•  Overview of Schema Object Dependencies
•  Query Direct Object Dependencies using the USER_DEPENDENCIES View
•  Query an Object€™s Status
•  Invalidation of Dependent Objects
•  Display the Direct and Indirect Dependencies
•  Fine-Grained Dependency Management in Oracle Database 11g
•  Understand Remote Dependencies
•  Recompile a PL/SQL Program Unit

 DESIGNING PL/SQL CODE

•  Describe the predefined data types
•  Create subtypes based on existing types for an application
•  List the different guidelines for cursor design
•  Cursor variables

USING COLLECTIONS

•  Overview of collections
•  Use Associative arrays
•  Use Nested tables
•  Use VARRAYs
•  Compare nested tables and VARRAYs
•  Write PL/SQL programs that use collections
•  Use Collections effectively

MANIPULATING LARGE OBJECTS

•  Describe a LOB object
•  Use BFILEs
•  Use DBMS_LOB.READ and DBMS_LOB.WRITE to manipulate LOBs
•  Create a temporary LOB programmatically with the DBMS_LOB package
•  Introduction to SecureFile LOBs
•  Use SecureFile LOBs to store documents
•  Convert BasicFile LOBs to SecureFile LOB format
•  Enable reduplication and compression

USING ADVANCED INTERFACE METHODS

•  Calling External Procedures from PL/SQL
•  Benefits of External Procedures
•  C advanced interface methods
•  Java advanced interface methods

PERFORMANCE AND TUNING

•  Understand and influence the compiler
•  Tune PL/SQL code
•  Enable intra unit inlining
•  Identify and tune memory issues
•  Recognize network issues

IMPROVING PERFORMANCE WITH CACHING

•  Describe result caching
•  Use SQL query result cache
•  PL/SQL function cache
•  Review PL/SQL function cache considerations

ANALYZING PL/SQL CODE

•  Finding Coding Information
•  Using DBMS_DESCRIBE
•  Using ALL_ARGUMENTS
•  Using DBMS_UTILITY.FORMAT_CALL_STACK
•  Collecting PL/Scope Data
•  The USER/ALL/DBA_IDENTIFIERS Catalog View
•  DBMS_METADATA Package

PROFILING AND TRACING PL/SQL CODE

•  Tracing PL/SQL Execution
•  Tracing PL/SQL: Steps

IMPLEMENTING VPD WITH FINE-GRAINED ACCESS CONTROL

•  Understand how fine-grained access control works overall
•  Describe the features of fine-grained access control
•  Describe an application context
•  Create an application context
•  Set an application context
•  List the DBMS_RLS procedures
•  Implement a policy
•  Query the dictionary views holding information on fine-grained access

SAFEGUARDING YOUR CODE AGAINST SQL INJECTION ATTACKS

•  SQL Injection Overview
•  Reducing the Attack Surface
•  Avoiding Dynamic SQL
•  Using Bind Arguments
•  Filtering Input with DBMS_ASSERT
•  Designing Code Immune to SQL Injections
•  Testing Code for SQL Injection Flaws

USE XML FOR SQL AND PL/SQL DEVELOPERS

•  Use Oracle XML DB repository.
•  Search and Retrieve XML data.
•  Manage XML storage in Oracle XML DB.
•  Manipulate and generate XML data.
•  Use XQuery.
•  Use PL/SQL APIs for XML.

ORACLE PL/SQL TRAINER PROFILE

• More than 9+ Years of experience in Oracle Technologies
• Has worked on 9 realtime Oracle projects
• Working in a MNC company in Chennai