Oracle Database: Develop PL/SQL Program Units Ed 2

loading..
(Course eligible for SATVs)

loading..

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.

Lessons

  • 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

Lessons

  • 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

Lessons

  • 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

Lessons

  • 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

Lessons

  • 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

Lessons

  • 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

Lessons

  • 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

Lessons

  • Different types of triggers
  • Database triggers and their use
  • Creating database triggers
  • Database trigger firing rules
  • Removing database triggers

Lessons

  • Compound triggers
  • Mutating tables
  • Creating triggers on DDL statements
  • Creating triggers on system events
  • Displaying information about triggers

Lessons

  • 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

Lessons

  • 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

Lessons

  • 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

Traditional Classroom   

    Show all

    Traditional Classroom   

      Show all

      Traditional Classroom   

        Show all

        Traditional Classroom   

          Show all

          OnlineLive   

            Show all

            Connected Classroom   

              Show all

              Connected Classroom   

                Show all

                Connected Classroom   

                  Show all

                  Connected Classroom   

                    Show all

                    Flexible Mentored Learning   

                      Show all

                      Flexible Mentored Learning   

                        Show all

                        Flexible Mentored Learning   

                          Show all

                          Flexible Mentored Learning   

                            Show all

                            Not currently scheduled

                            Fast track the availability of this course.
                            Add to watch list or call 1300 794 006.

                            Need your team upskilled?
                            Request a Quote

                            blah is false
                            top