Course Price$ 299.00
The training modules are designed in a way that it provides enlightenment from the basic introduction to SQL to implementing it on the real time scenarios.
PLSQL skills are highly sought after in web development, programming, data analysis and reporting environments
AutomationTraining4u is a leading training and consultancy group which impart knowledge on the particular program with the best trainers in IT having more than 10+ years of experience. We focus on real time based training & practical knowledge.
Training with Automation Training4U will benefit on the following ways in one’s career:
- Build Resume
- Multiple Interview Session
- Job Assistance
- Theory Session
- Practical Assignments
- Real Time Example
- Class Videos
Introduction – PL/SQL Architecture
* Declaring Variables
* Writing Executable Statements
* Interacting with Oracle Server
* Writing Control Structures
* Comments in PLSQL
* Variable Scope
* Using GOTO
* Using the CASE Statement
* Using the If-Then-Else
* Using the If-Then-Else
* Working with LOOPS
* Continue Statement
* Working with Composite Data Types
* Cursors Implicit
* Understanding the Concept and Purpose of Explicit Cursors
* Learn about Cursor Attributes
* Creating and Using Cursor Parameters
* Cursors with Records
* Looping with Cursors
* Difference between Global vs Local Objects and Exceptions
* Handling Exceptions
* What Are the Types of Exceptions
* Creating User-Defined Exceptions
* Using SQLCODE and SQLERRM Objects
Procedures & Functions
* Creating Procedures
* Input and Output of procedures
* Understanding the Parameter Specification
* Standalone procedures
* Creating Functions
* Managing Subprograms
* Overloading the Subprograms
* What are Functions & Procedures and Why We Use
* Creating & Using & Modifying & Removing the Packages
* Visibility of Package Objects
* Illegal Object Reference & Forward Declaration
* Package Initialization
* How Can I Find the Packages?
* Creating Database Triggers
* Types of triggers
* Statement & Row Level Triggers
* Using New & Old Qualifiers in Triggers
* Using RAISE_APPLICATION_ERROR Procedure in Triggers
* Using WHEN Clause in Triggers
* INSTEAD OF Triggers
* Exploring & Managing the Triggers
* Compound Triggers
* Handling Mutating Table Errors
Interview Questions & Answer
An error condition is called an exception in PL/SQL. Exceptions can be internally defined (by the runtime system) or can be user-defined. For example, internally defined exceptions include division by zero and out of memory.
When an error occurs, an exception is raised. That is normal execution stops and control transfers to the exception-handling part of your PL/SQL block or subprogram. Internal exceptions are raised automatically by run-time system. User-defined exceptions must be raised explicitly by RAISE statements, which can also raise predefined exceptions.
in order to handle raised exceptions, you write separate routines called exception handlers. After an exception handler runs, the current block stops executing and the enclosing block resumes with the next statement. If there is no enclosing block, then control returns to the host environment.
Example: Runtime Error Handling
stock_price NUMBER := 9.73;
net_earnings NUMBER := 0;
-- Calculation might cause division-by-zero error.
pe_ratio := stock_price / net_earnings;
DBMS_OUTPUT.PUT_LINE('Price/earnings ratio = ' || pe_ratio);
EXCEPTION -- exception handlers begin
-- Only one of the WHEN blocks is executed.
WHEN ZERO_DIVIDE THEN -- handles 'division by zero' error
DBMS_OUTPUT.PUT_LINE('Company must have had zero earnings.');
pe_ratio := NULL;
WHEN OTHERS THEN -- handles all other errors
DBMS_OUTPUT.PUT_LINE('Some other kind of error occurred.');
pe_ratio := NULL;
END; -- exception handlers and block end here
Rules for Avoiding and Handling PL/SQL Errors and Exceptions
- Add exception handlers whenever there is any possibility of the error occurring.
- Add error-checking code whenever you can predict that an error might occur if your code gets a bad input data.
- Make your programs robust sufficient to work even if the database is not in the state you expect.
- Handle named exceptions whenever possible, instead of using WHEN OTHERS in exception handlers.
- Test your code with different combinations of the bad data to see what potential errors arise.
- Write out debugging information in your exception handlers.