Max Video Learn

دورات اون لاين احترافيه مع شرح عربي مع ابقاء المحتوى بالأنجليزي للنجاح في الامتحانات مثل امتحانات الأوراكل و غيرها

Develop PLSQL Program Unit agenda

Oracle Database 12c :

Develop PLSQL Program Unit

This include :

Oracle Database PL-SQL Fundamentals

Oracle DB Develop PLSQL Program Unit

EXAM 1Z0-144

Chapter 1: Introduction

•System development life cycle.
•What is Data?
•What is Database?
•What is DBMS?
•What is RDBMS?
•What is ORDBMS?
•What is the Table?
•What is the Data Models & ERM?
•What is the SQL?
•What is the SQL*PLUS/ Oracle SQL developer?
•Types of SQL Statements.
•HR Tables used for this course .
•Brief History of Oracle Database.
•Oracle DB 12c Architecture.
•Comparison Between 12C and Pervious Releases

Chapter 2: Installing oracle 12c on windows

•Visiting Oracle Website.
•Oracle Database Versions.
•Oracle Enterprise Edition and Standard Edition.
•Steps to Download Oracle DB 12c.
•Steps to Install Oracle DB 12c.
•Discover the new options when Installing12c.
•Naming the container DB and PDB while installing.

Chapter 3: First connection and unlock HR account

•What is TNSNAMES.ORA
•Making the First connection by SYS user on the container DB.
•Steps to unlock the HR account.
•Make the second connection by User HR on the Pluggable DB.

Chapter 4: Overview of PLSQL

•What is the PL/SQL
•PL/SQL Environment
•What is the benefits of PL/SQL
•PL/SQL block Structure
•Block Types
•Differences between anonymous block and subprograms

Chapter 5: Declaring PLSQL Variables

•Why to use Variables
•Requirements for Variables names
•Handling variables in PL/SQL
•Declaring and initializing variables
•Types of Variables
•Guidelines for declaring and initializing variables
•Scalar data types
•%Type attribute
•Bind Variables
•LOB Data type variables
•Composite Data types

Chapter 6: Writing executable statements

•Inside PL/SQL blocks
•Using comments
•SQL Functions in PL/SQL
•Sequences in PL/SQL
•Nested Blocks
•Programing Guidelines

Chapter 7: Interacting with Oracle DB Server

•SQL statements in PL/SQL
•Naming conventions
•Using PL/SQL to Manipulate Data
•SQL Cursor
•SQL cursor Attributes

Chapter 8: Writing Control structure

•IF statement
•Case Expressions and Case Statement
•Handling Nulls
•Basic Loop
•For loop
•While Loop
•Nested Loops and Labels

Chapter 9: Working with Composite Data Types

•What is the composite data type
•What is the PL/SQL record
•How to Define PL/SQL records
•%ROWTYPE
•PL/SQL collections
•Index By table or Associative Arrays
•Nested Tables
•Varray
•Characteristics for each type of collection

Chapter 10: Using explicit cursors

•What is the Cursor
•Controlling Explicit cursor
•Explicit Cursor Attributes
•Hints when Declaring a cursor
•Hints when fetching Data from a cursor
•Hints when closing a cursor
•Writing cursor
•Cursor for Loops
•Cursor with parameters
•Using FOR UPDATE in cursor
•Where current of clause

Chapter 11: Handling Exceptions

•Example of an exception
•Handling Exceptions with PL/SQL
•Exception Types
•Trapping Exceptions
•Guidelines for Trapping exceptions
•Trapping Predefined oracle server errors
•Predefined exceptions
•Trapping Non-Predefined oracle server errors
•Trapping user defined exceptions
•Rise_Application_error procedure

Chapter 12: Creating Procedure

•Review of Blocks Types
•Modularized subprogram design
•Benefits of anonymous block
•what is the PL/SQL Subprograms
•What is the procedure
•Creating Procedure
•ParamterPassing Mode
•Comparing Parameter modes
•Notations for passing parameters
•Using the Default Option for the parameters
•Handling Exceptions

Chapter 13: Creating Functions

•Overview of stored functions
•Creating functions
•Differences between procedure and function
•Running functions
•Function in SQL statements
•Restrictions when calling functions from SQL Expressions.

Chapter 14: Creating Packages

•What are PL/SQL Packages
•Advantages of using packages
•The visibility of package’s components
•Create package specification
•Creating package body
•Guidelines for writing packages
•Dictionary tables and dropping the package

Chapter 15: Working with Packages

•Overloading Subprograms in PL/SQL
•Overloading Procedure Example
•Overloading and the STNADARDPackage
•Illegal Procedure Reference
•Using Forward Declaration
•Persistent State of Packages

Chapter 16: Using oracle-supplied Packages in Application Development

•Using Oracle Supplied packages
•Examples of some Oracle Supplied packages
•DBMS_OUTPUT package
•UTL_FILE
•UTL_MAIL

Chapter 17: Using Dynamic SQL

•Execution flow of SQL
•What is Dynamic SQL
•Native Dynamic SQL
•Execute Immediate
•DBMS_SQL Package

Chapter 18: Design consideration for PLSQL Code

•Standardizing Constants and Exceptions
•Local Subprograms
•Definer’s Rights VS Invoker’s Rights
•AUTHID / CURRENT_USER
•Autonomous Transactions
•Passing Parameters ( By Value and by Refernce)
•Using NOCOPY hint
•Using PARRALEL_ENABLE Hint
•RESULT_CACHE Hint
•DETERMINSTIC Hint
•BULK Binding

Chapter 19: Creating Triggers

•What are Triggers
•Defining Triggers
•Trigger Event
•Application Vs database Trigger
•When to Use triggers
•Trigger Types
•Statement level triggers
•Row level triggers
•Using OLD and NEW Qualifiers
•Trigger Firing Sequences
•Managing Triggers
•INSTEAD OF triggers

Chapter 20: Creating Compound, DDL,and Event Database Triggers

•Review of triggers firing Sequence
•What is Compound Trigger
•Compound Trigger Structure
•Compound Trigger Restrictions
•Mutating Tables and how to Solve it using Compound Trigger
•DDL triggers
•Database Even Triggers
•CALL statements in triggers
•Guidelines for Designing Triggers

Chapter 21: Using the PLSQL Compiler

•Using the PL/SQL Compiler
•Changes in PL/SQL Compiler
•Compilation paramters
•PLSQL_CODE_TYPE
•PLSQL_OPTIMIZE_LEVEL
•Warnings for Subprorams
•Benefits of compiler warnings
•PLSQL_WARNINGS

Chapter 22: Managing PLSQL Code

•What is Conditional compilation?
•Benefits of Conditional Completion
•How Conditional Completion works?
•DBMS_DB_VERSION Package
•Using Selection Directives 
•DBMS_PREPORCESSOR
•PLSQL_CCFLAGS       
•What is Obfuscation ?
•Benefits of Obfuscation
•DBMS_DDL.create_wrapped
•The PLSQL Wrapper Utility
•Guidelines for Wrapping
•DBMS_DDL VS Wrapper Utility

Chapter 23: Managing Dependencies

•What is Dependencies?
•Overview of schema object Dependencies
•Direct local Dependencies
•Indirect local Dependencies
•How to display Direct and Indirect Dependencies?
•Object Status
•Invalidation of Dependent Objects
•Impact of Adding/ Altering column for the Referenced Table
•Impact of Redefining Synonyms
•Guidelines for Reducing Invalidation
•Remote Dependencies
•Remote Dependencies/ time stamp vs signature
•Unsuccessful recompilation
•Successful recompilation