This course is designed for developers with basic PL/SQL and SQL language skills. Students learn to develop, execute, and manage PL/SQL stored program units such as procedures, functions, packages, and database triggers. Students also learn to manage, PL/SQL subprograms and triggers.
- Overview of Oracle Database Exadata Express Cloud Service
- Accessing Cloud Database using SQL Workshop
- Connecting to Exadata Express Database using Database Clients
- Using SQL Developer to work with Exadata Express Database
- Using SQLcl to work with Exadata Express Database
- Using SQL*Plus to work with Exadata Express Database
- PL/SQL blocks and subprograms
- Uses and benefits of procedures
- Working with procedures
- Using formal and actual parameters
- Identify the available parameter-passing modes
- Passing parameters using the positional, named, or combination techniques
- Handling exceptions in procedures
- Viewing the procedure information
- Creating Stored Functions
- The Difference Between Procedures and Functions
- Working with Functions
- Identifying the Advantages of Using Stored Functions in SQL Statements
- Using User-Defined Functions in SQL Statements
- Using a PL/SQL Function in the SQL WITH Clause
- Defining and executing PL/SQL functions in SQL statements
- Restrictions When Calling Functions from SQL statements
- Using PL/SQL Packages
- Components of a PL/SQL Package
- Visibility of a Package’s Components
- Developing a PL/SQL Package
- Creating the Package Specification and Package Body
- Invoking the Package Constructs
- Creating and Using Bodiless Packages
- Removing a Package
- Overloading Subprograms
- Using Forward Declarations to Solve Illegal Procedure Reference
- Initializing Packages
- Using Package Functions in SQL and Restrictions
- Controlling Side Effects of PL/SQL Subprograms
- Persistent State of Packages
- Persistent State of Package Variables and Cursors
- Using PL/SQL Tables of Records in Packages
- Using Oracle-Supplied Packages
- Examples of Some of the Oracle-Supplied Packages
- Working of DBMS_OUTPUT Package
- Using the UTL_FILE Package to Interact With Operating System Files
- Using the UTL_MAIL Package
- Introduction to Dynamic SQL
- The Execution Flow of SQL
- Working With Dynamic SQL
- When Do You Need Dynamic SQL?
- Using Native Dynamic SQL (NDS)
- Dynamic SQL with mock up application
- Using BULK COLLECT and FORALL
- Dynamic SQL using DBMS_SQL package
- Different types of triggers
- Database triggers and their use
- Creating database triggers
- Database trigger firing rules
- Removing database triggers
- Compound triggers
- Mutating tables
- Creating triggers on DDL statements
- Creating triggers on system events
- Displaying information about triggers
- Standardizing constants with a constant package
- Standardizing exceptions with an exception package
- Writing PL/SQL code that uses local subprograms
- Grant Roles to PL/SQL Packages and Standalone Stored Subprograms
- Using the NOCOPY compiler hint to pass parameters by reference
- Using the PARALLEL ENABLE hint for optimization
- Using the AUTONOMOUS TRANSACTION pragma
- Describing the differences between invoker rights and definer rights
- Using the PL/SQL Compiler with initialisation parameters
- Using the PL/SQL Compile Time Warnings
- Viewing the Current Setting of PLSQL_WARNINGS
- Viewing the Compiler Warnings
- Guidelines for using PLSQL_WARNINGS
- Conditional Compilation
- Dependent and referenced objects
- Tracking procedural dependencies with dictionary views
- Predicting the effect of changing a database object
- Managing local and remote procedural dependencies
•Technical Consultant•Portal Developer•PL/SQL Developer•Database Administrators•System Analysts•Forms Developer
•Required Prerequisites:•Familiarity with data processing concepts and technique•Familiarity with programming languages•Oracle Database 12c: Introduction for Experienced SQL Users•Suggested Prerequisites:•Familiarity with data processing concepts and techniques•Familiarity with programming languages
After completing this course, students will be able to:
Register
Not currently scheduled
Fast track the availability of this course.
Add to watch list or call 1300 794 006.
Add to watch list or call 1300 794 006.
Request a Quote
About Microsoft On-Demand Learning
Build your Microsoft technical expertise while balancing the demands of your schedule. The new MOC On-Demand series courses combine high-quality video lectures, live hands-on labs and knowledge-checks in a self-paced format to help you build skills as your schedule allows—all at once or five minutes at a time.
MOC On-Demand at a Glance
Self-Paced Learning
Immediate Feedback on Mastery
SATV Eligible
Direct From the Source
Learn Anywhere Anytime
Live Hands-on
Labs
