Oracle DBA Training In Chennai

Defining Variables and Datatypes

• PL/SQL Block Structure, Block Type
• Using Variables in PL/SQL
• Types of Variables & Naming Rules Variable Initialization
• PLSQL variable, Non PLSQL variable
• Scalar variable, Reference Variable
• Composite variable
•  Recognizing PL/SQL Lexical Units
•  Recognizing Data Types
•  Using Scalar Data Types
•  Writing PL/SQL Executable Statements
•  Nested Blocks and Variable Scope
•  Good Programming Practices
•  Boolean Variables and HANDLING Non-PL/SQL Variables DBMS_OUTPUT.PUT_LINE%TYPE and %ROWTYPE

Program Structures to Control Execution Flow  

• Conditional Control: IF, IF ELSE, ELSE IF Statements
•  Conditional Control: CASE Statements
•  Iterative Control: Basic Loops
•  Nested loop and Lable
•  Iterative Control: Nested Loops
•  GOTO Statement
•  Iterative Control: WHILE and FOR Loops
•  Real time Example of program using Conditional statement and
•  Loops

 Introduction to Explicit Cursors

• Cursor Attributes, Which attributes cannot used in Implicit cursor
• Using Cursors for UPDATE and WHERE CURRENT OFF
• Cursor FOR Loop
• Cursors with Parameters
• ROWCOUNT using in DML operation inside the block.
• FOR CURSOR
• Using Multiple Cursors
• Declare the cursor, Open the Cursor, FETCH the Data from Cursor, Close the Cursor

Using Cursors and Parameters

• Defining the cursor
• When we go for Cursor.
• Type of Cursor
• Defining Static and Dynamic Cursor
• Implicit Cursor, Explicit Cursor, Parameterized Cursor

Exception Handling

• Understand the Exception
• Handle exception in PLSQL Blocks.
• Type of Exception
• Implementation of Exception
• Describe Raise_application_error Procedure
• Oracle Error range, User Error Range
• Pragma_exception_int Procedure

Create the Stored Procedure

• Syntax for creating, Dropping, Replace and Executing the Stored Procedure
• Procedure with parameter using (IN, OUT, IN OUT)
• Difference between Stored procedure and Anonymous block
• Invoking Procedure with another procedure
• Ways to Execute the procedure
• Implementation with business logic
• Handling the Exception, Cursor inside the Stored Procedure
• Fixed notataion, named notataion, Mixed notation
• View the Meta Data information about the Procedure in Data Dictionary table
• User_Procedures

Create a Stored Function

• Syntax for Create, Execute, Drop the Function
• Calling of user-defined function in SQL statement
• Invoking Stored function in another Stored procedure
• Advantage of using stored function
• Data Dictionary Table Stored Function
• How PLSQL Function returns more than one value
• Difference between Stored Procedure and Stored Function
• View the Meta Data Information about Function in Data Dictionary table.

Package

• Reason to use Package
• Create the package
• Identify the advantage of Package
• Describing the package
• Components in package
• Global variable
• Forward Declaration, and Procedure overloading
• Create The Package specification and Package Body
• Implement the Business logic in the Package
• Implements the Cursor and Exception in the Package efficiently
• View the source code using the data dictionary table
• Compile and Drop the Package

Trigger

• Describe the trigger
• Purpose of trigger
• Identify the trigger events type and body
• Type of triggers
• Syntax for creating Row level trigger and statement level trigger
• And difference
• Instead of trigger
• Create the DDL, Database(logon and logoff) trigger
• Create and Drop the trigger
• View the Meta data information about the trigger
• Mutating error
• How to avoid the mutation error
• Create the Compound trigger to avoid the mutating error
• Describe the Pragma_autonomous_transcation
• Using data dictionary ALL_TRIGGERS

Manage dependencies

• Overview of Schema object dependencies
• Finding out the Object Dependencies using the USER_DEPENDENCIES
• Invalidation of object dependencies.

Data Dictionary table usage

• USER_OBJECT, ALL_OBJECT, DBA_OBJECT
• USER_ERRORS,ALL_ERRORS,DBA_ERRORS
• USER_SOURCE
• USER_TABLES
• USER_VIEWS
• USER_SEQUENCES
• USER_SYNONYMNS
• USER_INDEXES

Oracle Supplied package

• Create UTL_FILE to import and export the file as CSV, TXT file Tab-separated file
• DBMS_PROFILER to debug the Procedure, Packages.

Overview of collection

• Composite data type, Collection data type
• Types of collection
• Associate array,
• Nested table
• Varray
• Real time implementation using the collection
• Collection Methods
• Multiset Operator
• Describing Assignment and Equality test
• Object ,Records High level data type
• Use collection efficiently

Bulk Collect

• Overview of Bulk collect
• Understanding how Context Switching reduce using the bulk collect
• Advantage of Bulk Collect
• Bulk collect and Bulk Bind using the FORALL
• Use the save exception to handle the runtime error in the collection
• Difference between FOR and FORALL
• Bulk inserting can archive using the bulk Bind

Ref cursor

• When do we need Ref Cursor with practical example
• Advantage of ref cursor
• Type of ref cursor
• Strong Ref cursor, Weak Ref Cursor and SYS_REFCURSOR

Performance tuning

• DBMS_UTILITY.FORMAT_ERROR_BACKTRACE;
• Creating the index
• Using the BULK collect
• DBMS_UTILITY.FORMAT_ERROR_STACK;