Pl-SQL is the next course required for those seeking a career as a Oracle Developer.
Pl-SQL certification exam is the second exam that one needs to clear to achieve OCA certification.
Audience:
- Data Warehouse Administrator
- Forms Developer
- System Analysts
- Business Analysts
- Application Developers
- PL/SQL Developer
Course Objectives
-
Introduction to PLSQL
- istory of PL/SQL language
- he anonymous PLSQL block
- ariables & Data types
- SQL operators, Nested blocks
- SELECT … INTO statement
- How to Manage, Test and Remove Triggers
-
Triggers
- Describe Triggers
- Identify the Trigger Event Types and Body
- Business Application of Triggers
- Create DML Triggers using the CREATE TRIGGER Statement and SQL Developer
- Identify Trigger Event Types, Body & Firing
- Differences in Statement & Row Level Triggers
- Create Instead of and Disabled Triggers
Composite data types
- Parsing %ROWTYPE
- SQL Records & Table of Records
Oracle Utilities
- SQL*loader (sqlldr)
- Spool and spool append commands.
- Profiling PL/SQL code using DBMS_HPROF supplied package.
Using Oracle Supplied Packages
- BMS_OUTPUT & DBMS_UTILITY
- TL_FILE, UTL_MAIL
- BMS_RANDOM & EXECUTE IMMEDIATE
Package Features
- Overloading package subprograms
- Forward declaration of subprograms
- Creating bodiless package
- Persistent state of package variables & cursor
Creating Packages (PL/SQL API Development)
- What are packages and what is an API ?
- Package Body & Specification
- Private and Public Package level data
- Calling package functions, procedures
- Referencing package-level constants & variables
Creating Procedures (Stored sub-programs)
- Stored sub-programs (Procedure/Functions)
- d for Procedures – create syntax
- Use of Parameters ( IN , OUT & IN OUT)
- Executing procedures & trapping Exceptions
Creating Functions
- Syntax of CREATE FUNCTION
- Restrictions on function calls
- Dropping functions
Exceptions and Exception handling
- What are Exceptions?
- Need for handling & types of exceptions
- The raise_application_error built-in procedure & User defined exceptions
- Raising & Trapping Exceptions
- Exception propagation in nested calls
- Use of SQLCODE & SQLERRM
- Testing of these statements.
- Use of FOR Loop & WHILE Loop
- How to label a loop and how to nest a loop.
Iterative and Conditional control structures
- sing cursors in procedures
- OPEN. FETCH CLOSE construct
- The %NOTFOUND operator
- FOR UPDATE cursors, Pl-SQL Case
- Use of IF-THEN-ELSE statement
Certification Details®
Course Name |
Exam |
No of (days) |
Oracle SQL |
SQL Certification Exam 12c or 11g |
1 |
Total |
1 |
Course/Exam Name |
Duration |
Fees |
SQL Course |
30 hours or 12 classes |
Rs. 7,000 |
Pl-SQL Certification Exam |
Payable to Oracle at time of certification exam |
Rs.10,900 |
|
|