Oracle Pl/Sql Is My Game: Exam 1Z0-149

Become Oracle PL/SQL Developer / Covers ORACLE university track 100%. All the presentations & scripts are attached

What you'll learn

The full track of Oracle university Exam 1Z0-144 : Program with PL/SQL

Oracle Database 12c Architecture

Downloading and installing oracle database 12c in details

Overview of PLSQL

Declaring PLSQL Variables

Writing executable statements

Interacting with Oracle DB Server

Writing Control structure

Working with Composite Data Types

Using explicit cursors

Handling Exceptions

Creating Procedures

Creating Functions

Creating Packages

Working with Packages

Using oracle-supplied Packages in Application Development

Using dynamic SQL

Design consideration for PLSQL Code

Creating triggers

Creating Compound, DDL, and Event Database Triggers

Using the PLSQL Compiler

Managing PLSQL Code

Managing Dependencies

Requirements

You should know Oracle SQL ( 11g or 12c)

You should have strong knowledge in (SELECT statement, DML, DDL, DCL, creating views, indexes, and synonyms)

Windows Operating system is preferred, but this is not mandatory if you have the knowledge of downloading Oracle database on other OS

Downloading oracle database and this will be covered in details on Windows OS.

No need for any previous experience in PL/SQL, this Course starts from zero in PL/SQL

Description

This is one of the most amazing Udemy courses in Oracle PLSQL.The course covers the oracle university track 100% for :Exam Number: 1Z0-149 : Program with PL/SQLI designed in this course in a very professional structure that includes huge amount of exercises and examples.Be sure that you will become expert in PLSQL programming after finishing this course, and you will be ready 100% to pass the Oracle Exam. You will find all the presentations and all the SQL scripts attached in every chapter, so no need to waste your time repeating any example I did.Simply this course is the best….

Overview

Section 1: Course Setup

Lecture 1 Course Agenda

Lecture 2 Course Setup Notes / 4 Options

Lecture 3 Option 2- creating the HR Schema

Lecture 4 Option 3- Part 1 : Oracle Database 12c Architecture

Lecture 5 Option 3- Part 2 : Downloading Oracle DB 12c

Lecture 6 Option 3- Part 3 : Installing Oracle DB 12c

Lecture 7 Option 3- Part 4 : Connecting SYS using SQL plus & SQL Developer

Lecture 8 Option 3- Part 5: Unlock HR Account Part 1

Lecture 9 Option 3- Part 6 : Unlock HR Account Part 2

Lecture 10 Option 3- Part 7 : ORA-01033 oracle initialization or shutdown in progress 12c

Lecture 11 Option 4- Installing Oracle 11g express Edition + Unlock HR

Lecture 12 About Oracle 18c express edition

Lecture 13 HR Schema in this Course

Lecture 14 Navigate HR schema

Section 2: Overview of PL/SQL

Lecture 15 What is PL/SQL? What is the benefits?

Lecture 16 PL/SQL Block Structure & Types of Blocks

Lecture 17 Writing simple Anonymous block

Lecture 18 Using SQL*plus to run anonyms block

Lecture 19 Creating Script and run it

Section 3: Declaring PLSQL Variables

Lecture 20 About Variables and the syntax

Lecture 21 Defining Variables exercise

Lecture 22 Guidelines for declaring & initializing variables

Lecture 23 Delimiters in string literals (the q' Notation)

Lecture 24 Types of variables and data types 1

Lecture 25 Types of variables and data types 2

Lecture 26 Data Types exercise

Lecture 27 Define Variables Using %TYPE

Lecture 28 Understating the SELECT statement in PL/SQL

Lecture 29 Bind Variables

Section 4: Writing executable statements

Lecture 30 Inside PL/SQL Block

Lecture 31 Inside PL/SQL Block (exercise)

Lecture 32 Nested Blocks

Lecture 33 SQL & PL/SQL programing Guidelines

Section 5: Interacting with Oracle DB Server

Lecture 34 SELECT statement & DML in PL/SQL

Lecture 35 SELECT statement & DML in PL/SQL exercise

Lecture 36 Understanding Implicit SQL cursor/ SQL Attributes

Lecture 37 Implicit SQL cursor exercise

Section 6: Writing Control structure

Lecture 38 IF Statement Part 1

Lecture 39 IF Statement Part 2

Lecture 40 Handling nulls in IF statement

Lecture 41 CASE expression & CASE Statement

Lecture 42 Basic Loop

Lecture 43 While Loop

Lecture 44 FOR Loop

Lecture 45 Nested Loops

Lecture 46 Nested Loops and Labels

Lecture 47 Continue Statement

Section 7: Working with Composite Data Types

Lecture 48 PL/SQL Records: Programmer method part 1

Lecture 49 PL/SQL Records: Programmer method part 2

Lecture 50 PL/SQL Records: %ROWTYPE method

Lecture 51 Nested PL/SQL Records

Lecture 52 INDEX BY tables (Associative arrays)

Lecture 53 INDEX BY tables Methods

Lecture 54 INDEX BY tables of Records

Lecture 55 nested table

Lecture 56 Varray

Section 8: Using explicit cursors

Lecture 57 Understanding Explicit Cursor

Lecture 58 Explicit Cursor exercise

Lecture 59 Explicit Cursor Attributes

Lecture 60 FOR LOOP Cursor

Lecture 61 Cursor with Parameters

Lecture 62 FOR UPDATE Clause & CURRENT OF Clause

Section 9: Handling Exceptions

Lecture 63 Understanding Exceptions

Lecture 64 Predefined Oracle Server Error Exception

Lecture 65 Common mistakes when dealing with exceptions

Lecture 66 Non-Predefined Oracle Server Error Exception

Lecture 67 User-Defined Error Exception

Lecture 68 Group Functions and exceptions

Lecture 69 Many blocks and many exceptions

Section 10: Creating Procedure

Lecture 70 Understanding Procedures and subprograms

Lecture 71 Understanding Procedures Syntax

Lecture 72 Creating Procedure (IN parameter)

Lecture 73 Creating Procedure (OUT parameter)

Lecture 74 Creating Procedure (IN OUT parameter)

Lecture 75 Comparing the Paramter Modes

Lecture 76 Available notations for passing parameters

Lecture 77 Using the default option for Parameters

Lecture 78 Exception handling in multiple blocks

Lecture 79 Using Boolean and PL/SQL records as parameters

Section 11: Creating Functions

Lecture 80 Understanding user-defined functions

Lecture 81 Difference between procedures & functions

Lecture 82 Creating function example

Lecture 83 No_data_found and functions

Lecture 84 Advantages of user-defined functions in SQL Statements

Lecture 85 Restrictions when calling functions from SQL part 1

Lecture 86 Restrictions when calling functions from SQL part 2

Section 12: Creating Packages

Lecture 87 Understanding packages by nice example

Lecture 88 More about packages

Lecture 89 Creating package practice

Lecture 90 Creating package specification without body

Lecture 91 Guidelines for writing packages

Lecture 92 The visibility of a package’s components

Lecture 93 When & Why to recompile the package?

Section 13: Working with Packages

Lecture 94 Understanding Overloading Subprograms in PL/SQL

Lecture 95 Overloading Subprograms practices (procedures)

Lecture 96 Overloading Subprograms practices (functions)

Lecture 97 Overloading Standard package

Lecture 98 Forward Declaration

Lecture 99 Persistent State of packages

Lecture 100 Using PRAGMA SERIALLY_REUSABLE

Lecture 101 Persistent State and cursor

Lecture 102 using pl/sql tables in packages

Section 14: Using oracle-supplied Packages in Application Development

Lecture 103 About oracle-supplied Packages

Lecture 104 DBMS_OUTPUT part 1

Lecture 105 DBMS_OUTPUT part 2

Lecture 106 UTL_FILE Package Part 1 / create directory

Lecture 107 UTL_FILE Package Part 2 / get_line example 1

Lecture 108 UTL_FILE Package Part 3 / get_line example 2

Lecture 109 UTL_FILE Package Part 4 / put_line

Lecture 110 UTL_FILE Package Part 5 / exceptions

Lecture 111 UTL_MAIL

Lecture 112 UTL_MAIL.send

Lecture 113 utl_mail.send_attach_raw

Lecture 114 utl_mail.send_attach_varchar2

Section 15: Using Dynamic SQL

Lecture 115 Execution flow of SQL

Lecture 116 what is Dynamic SQL?

Lecture 117 Execute immediate example 1 / delete any table

Lecture 118 Execute immediate example 2 / DDL in PLSQL

Lecture 119 Execute immediate example 3 / USING Clause

Lecture 120 Execute immediate example 4 / INTO Clause

Lecture 121 Execute immediate example 5 / single row query

Lecture 122 What is REF Cursor ?

Lecture 123 Dynamic SQL with multi row query

Lecture 124 Execute immediate/ more examples

Lecture 125 DBMS_SQL Package VS Execute immediat

Section 16: Design consideration for PLSQL Code

Lecture 126 Standardizing Part 1

Lecture 127 Standardizing Part 2

Lecture 128 Definer’s Rights

Lecture 129 Invoker’s Rights

Lecture 130 Autonomous Transactions

Lecture 131 Features Autonomous Transactions

Lecture 132 The (IN) parameters always passed by reference

Lecture 133 The (out/in out) parameters can be passed by value or reference

Lecture 134 Passing by reference example (IN parameters)

Lecture 135 Passing by value VS passing by reference using nocopy

Lecture 136 When Does NOCOPY hint has no effects?

Lecture 137 NOCOPY Performance Exercise

Lecture 138 Using the PARALLEL_ENABLE hint

Lecture 139 Using the RESULT_CACHE hint

Lecture 140 Using the DETERMINISTIC hint

Lecture 141 Understanding Bulk Binding

Lecture 142 Bulk Binding Example

Lecture 143 Bulk binding with save exception part 1

Lecture 144 Bulk binding with save exception part 2

Lecture 145 Understanding Bulk collect

Lecture 146 Bulk collect another example

Lecture 147 Bulk collect and cursors

Lecture 148 using returning in bulk collect

Lecture 149 INDICES OF

Section 17: Creating triggers

Lecture 150 Understanding Triggers

Lecture 151 Tirgger Types/ Trigger Event Types

Lecture 152 Statement Level trigger VS Row Level (Part 1)

Lecture 153 Statement Level trigger VS Row Level (Part 2)

Lecture 154 Triggers common cases

Lecture 155 Statement Level trigger Example 1

Lecture 156 Statement Level trigger Example 2

Lecture 157 Understanding the NEW & OLD qualifiers

Lecture 158 Row level trigger example

Lecture 159 Row level trigger (create Audit table)

Lecture 160 Trigger Firing sequence

Lecture 161 Compile/enable/ disable/ drop trigger

Lecture 162 Using the triggers for populating a default value

Lecture 163 Instead of triggers ( on Views)

Section 18: Creating Compound, DDL, and Event Database Triggers

Lecture 164 Follows Statement in triggers

Lecture 165 Understanding Compound triggers

Lecture 166 Understanding Mutating Table

Lecture 167 Solving Mutating Table issue using compound trigger

Lecture 168 Using Compound trigger & array to solve Mutating

Lecture 169 Mutating table & ON DELETE CASCADE

Lecture 170 Database Triggers VS Stored procedures

Lecture 171 Creating DDL triggers ( Schema or Database )

Lecture 172 Create system event trigger

Lecture 173 Final comments about the triggers

Section 19: Using the PLSQL Compiler

Lecture 174 Introduction about PL/SQL compiler

Lecture 175 Understanding ( Plsql_code_type ) parameter

Lecture 176 Understanding ( plsql_optimize_level ) parameter

Lecture 177 Understanding Warnings in PL/SQL

Lecture 178 Understanding (plsql_warnings) parameter

Lecture 179 More examples for plsql_warnings

Lecture 180 using the package dbms_warning

Section 20: Managing PLSQL Code

Lecture 181 Understanding conditional compilation

Lecture 182 Understanding (dbms_db_version ) package

Lecture 183 conditional compilation examples

Lecture 184 Understanding plsql_ccflags part 1

Lecture 185 Understanding plsql_ccflags part 2

Lecture 186 Understanding plsql_ccflags part 3

Lecture 187 plsql_ccflags Real example

Lecture 188 Understanding Obfuscation

Lecture 189 dbms_ddl.create_wrapped

Lecture 190 The Wrapper Utility part 1

Lecture 191 The Wrapper Utility part 2

Section 21: Managing Dependencies

Lecture 192 Understanding dependencies

Lecture 193 Impact of changing referenced object

Lecture 194 Example of changing referenced object

Lecture 195 Query the Direct and indirect dependencies 1

Lecture 196 Query the Direct and indirect dependencies 2

Lecture 197 Objects could have 4 statuses

Lecture 198 Understanding invalidation of Dependent Objects

Lecture 199 Invalidation of Dependent Objects Example

Lecture 200 Impact of adding/altering the referenced object on the View

Lecture 201 Impact of adding/altering the referenced object on the function

Lecture 202 Impact of adding/altering the referenced object on the procedure

Lecture 203 Packages and Dependencies Part 1

Lecture 204 Packages and Dependencies Part 2

Lecture 205 Synonyms and Dependencies part 1

Lecture 206 Synonyms and Dependencies part 2

Lecture 207 Synonyms and Dependencies part 3

Lecture 208 Guidelines for reducing invalidation

Lecture 209 Understanding Remote Dependencies

Lecture 210 Understanding Database link

Lecture 211 Creating new pluggable DB & DB link

Lecture 212 Remote Dependencies & time stamp

Lecture 213 Example: Remote Dependencies & time stamp

Lecture 214 Example: Remote Dependencies & Signature mode

Section 22: Bonus Section

Lecture 215 permanent solution for ORA-01033 oracle initialization or shutdown in progress

Lecture 216 DML logs using package dbms_errlog

Lecture 217 Move your skills to Oracle Analytic Functions In-Depth & Advanced Oracle SQL

Lecture 218 oracle 18c express edition part 1 (download and install )

Lecture 219 oracle 18c express edition part 2 (unlock hr )

Lecture 220 oracle 18c express edition part 3 ( sql developer )

Lecture 221 About oracle apex

Students who wants to be expert in PL/SQL programming,Students who wants to be certified in PL/SQL,Fresh and junior programmers who need to find better jobs by gaining oracle certification,Students who already know Oracle SQL and they need to move to next level which is PL/SQL