wordpress.simplitrain.com

course

Home / Courses / Oracle 19c PL/SQL Fundamentals (TTOR12019)

Course Objectives

Working within in a hands-on learning environment, guided by our expert team, attendees will develop a practical approach to Oracle Database Technology. Throughout the course participants will explore:

  • Using PL/SQL programming language for database applications and development incorporating PL/SQL modules within the application architecture from the initial design and planning phase The essentials of building executable PL/SQL program units Each of the major segments of a working program and how these interact with each other during program execution Important error or exception handling capabilities of the language. How database
  • resident program units can be used as part of the overall database application architecture Applying these new skills to the development of PL/SQL packages. Advanced database programming capabilities and benefits How database triggers can be used as part of an advanced database application design

Agenda

  • Considering Available Tools
  • Selecting the Appropriate Tool
  • Oracle Net Database Connections
  • Oracle PAAS Database Connections
  • Setup SQL Developer
  • Setup SQL *Plus
  • Setup JDeveloper
  • About BIND and Substitution Variables
  • Using SQL Developer
  • Using SQL *Plus

  • What is Database Programming
  • PL/SQL Programming
  • PL/SQL Performance Advantages
  • Integration with Other Languages

  • PL/SQL Program Structure
  • Language Syntax Rules
  • Embedding SQL
  • Writing Readable Code
  • Generating Readable Code
  • Generating Database Output
  • SQL * Plus Input of Program Block

  • About the Declare Section
  • Declare Primitive Types
  • Declaration Options
  • Not Null
  • Constant
  • Data Dictionary Integration
  • % Type
  • Declare Simple User-Defined Types
  • Type … Table
  • Type … Record
  • Extended User Defined Types

  • About the Begin Section
  • Manipulating Program Data
  • Logic Control & Braching
  • GOTO
  • LOOP
  • IF-THEN-ELSE
  • CASE

  • About the Exception Section
  • Isolating the Specific Exception
  • Pragma Exception_INIT
  • SQLCODE &SQLERRM Example
  • SQL%ROWCOUNT & Select … Into

  • About Explicit Cursors
  • Extend Cursor Techniques
  • For Update of Clause
  • Where Current of Clause
  • Using for … Loop Cursors

  • About Database – Resident Programs
  • Physical Storage & Execution
  • Types of Stored Program Units
  • Stored Program Unit Advantages
  • Modular Design Principles

  • Stored Procedures & Functions
  • Create Procedure / Create Function
  • Creating Procedures & Functions
  • Raise_Salary() Procedure
  • Salary_Valid() function
  • The Parameter Specification
  • Default Clause
  • System & Object Privileges
  • Using the Development Tools

  • Calling Procedures & Functions
  • Unit Testing with Execute
  • Anonymous Block Unit Testing
  • Specifying a Parameter Notation
  • SQL Worksheet Unit Testing
  • Calling Functions from SQL

  • Recompiling Programs
  • Mass Recompilation Using UTL_RECOMP()
  • Dropping Procedures & Functions
  • Drop Procedures & Functions
  • Drop Procedure / Function
  • Data Dictionary Metadata
  • Using USER_OBJECTS
  • Using USER_SOURCE
  • Using USER_ERRORS
  • Using USER_OBJECT_SIZE
  • Using USER_DEPENDENCIES

  • Dependency Internals
  • Tracking Dependencies
  • The Dependency Tracking Utility
  • SQL Developer Dependency Info
  • Dependency Strategy Checklists

  • About Packages
  • Creating Packages
  • Maintaining Packages
  • Performance Considerations

  • Definer & Invoker Rights
  • White Lists & Accessible By
  • Persistent Global Objects
  • Defining Initilization Logic
  • Object Orientation Support

  • Using Cursor Variables
  • Using SYS_REFCURSOR
  • Using Cursor Expressions

  • DBMS_OUTPUT()
  • UTL_FILE()
  • FOPEN() Example

  • About Database Triggers
  • DML Event Trigger Sub-Types
  • Database Trigger Scenario
  • Trigger Exhaustion Mechanisms
  • Trigger within SQL Worksheet

  • Statement Level Triggers
  • Using Raise Application_Error()
  • Row-Level Triggers
  • Examples of Triggers
  • Employee_Salary_Check Example
  • Employee_Journal Example
  • Budget_Event Example
  • Instead of Triggers
  • Triggers within and Application

  • Call Syntax
  • Trigger Maintenance Tasks
  • Show Errors Trigger
  • Drop Trigger
  • Alter Trigger
  • Multiple Triggers for a Table
  • Handling Mutating Table Issues

  • What are System Event Triggers
  • Defining the Scope
  • Available System Events
  • System Event Attributes
Tags
Technical Product Line: Other Technical Vendor: TriveraTech Core Type: Core 0
FREE

Interested in course?

Buy Course

Course Type: Instructor Led