This course provides a complete, hands-on introduction to Oracle Database Administration, including the use of Enterprise Manager Database Express (EMDE), SQL Developer and SQL*Plus.

Audience: This course is appropriate for anyone needing to understand and manage an Oracle 12c database or those needing a general understanding of Oracle database functionality, such as end users, business analysts, application developers, database administrators and IT management.
Course Duration: 5 days
Prerequisites:

Oracle12c SQL is required. Oracle12c PL/SQL is highly desirable.

Course Outline:
  • PL/SQL Program Structure
    • PL/SQL vs. SQL
    • PL/SQL Engines Available
    • Anonymous PL/SQL Block Structure
    • Object Naming Rules
    • Variable Declarations
    • Available Datatypes
      • Scalar Datatypes
      • Using Extended Datatypes
      • Object Types
    • Executable Statements
    • Expressions
    • Block Labeling
    • Variable Scoping Rules
    • Comments in Programs and Scripts
    • Basic Coding Standards
    • Lab One – PL/SQL Program Structure
    • Lab One Solutions – PL/SQL Program Structure
  • PL/SQL Flow Control
    • Conditional Control
    • Comparison Operators
    • Logical Operators
      • Truth Tables
    • Repetition Control
      • The Simple Loop
      • WHILE Loop
      • FOR Loop
      • CONTINUE Statements in Loops
      • Step Loops
    • The Goto Statement
    • Case Expressions / Statements
    • Bind Variables
    • Substitution Variables
    • Lab Two – PL/SQL Flow Control
    • Lab Two Solutions – PL/SQL Flow Control
  • SQL Developer and PL/SQL
    • SQL Developer and PL/SQL
    • Creating and Executing Scripts
    • Lab Three – SQL Developer and PL/SQL
    • Lab Three Solutions – SQL Developer and PL/SQL
  • Select INTO
    • Selecting Single Rows of Data
    • Anchoring Variables to Datatypes
    • DML inPL/SQL
    • RETURNING … INTO
    • Sequences In PL/SQL
    • Transaction Control In PL/SQL
    • Autonomous Transactions
    • Lab Four – Select INTO
    • Lab Four Solutions – Select INTO
  • The PL/SQL Cursor
    • Declaring Explicit Cursors
    • Opening and Closing Explicit Cursors
    • Using Explicit Cursors to Retrieve Values
    • Explicit Cursor Attributes
    • Using A Loop with An Explicit Cursor
    • Using %ROWTYPE with Cursors
    • The Cursor FOR LOOP
    • DBMS_OUTPUT
    • Lab Five – The PL/SQL Cursor
    • Lab Five – The PL/SQL Cursor
  • Optimization
    • Timing PL/SQL
    • For Update/Where Current Of
    • Lab Six – Optimization
    • Lab Six Solutions – Optimization
  • PL/SQL Exception Handling
    • The Exception Section
    • Oracle Named Exceptions
    • Pragma EXCEPTION_INIT
    • User Defined Exceptions
      • The Scope of User-Defined Exceptions
    • Raising Named Exceptions
    • Exception Propagation
    • Raising an Exception Again
    • Life After an Exception
    • When Others
    • Taking Your Ball and Going Home
    • DBMS_ERRLOG
    • Lab Seven – PL/SQL Exception Handling
    • Lab Seven Solutions – PL/SQL Exception Handling
  • Stored Procedures
    • Procedures
    • Benefits of Stored Procedures
      • Database Security
      • Performance
      • Productivity
      • Portability
    • Parameters and Stored Procedures
      • Parameter Notation
    • Stored Object Creation
      • Syntax for Creating A Procedure
    • Compilation Errors
    • Viewing Compiled Code
    • Dropping A Procedure
    • The Alter Command and Stored Procedures
    • Lab Eight – Stored Procedures
    • Lab Eight Solutions – Stored Procedures
  • Creating Functions In PL/SQL
    • Functions
    • Purity Levels
    • Using White Lists
    • Optimizations
    • PARALLEL_ENABLE
    • Deterministic Functions
    • PL/SQL Result Cache
    • NOCOPY
    • DBMS_OUTPUT in Functions
    • Using the WITH Clause for Functions
    • Pragma UDF
    • Pragma INLINE
    • Using SQL Developer with Stored Procedures
    • Debugging
    • Lab Nine – Functions
    • Lab Nine Solutions – Functions
  • Packages
    • Creating Packages
    • Package Benefits
      • Security
      • Persistent State
      • I/O Efficiency
    • A Simple Package
    • Overloading
    • Bodiless Packages
    • Source Code Encryption
    • Creating Packages from Procedures and Functions
    • Lab 10 – Packages
    • Lab 10 Solutions – Packages
  • Creating DML Triggers
    • DML Triggers
    • DML Trigger Structure
    • Conditional Triggering Predicates
    • Triggers for Business Rules Enforcement
    • Mutating and Constraining Tables
    • Compound Triggers
    • Controlling Firing Order
    • DML for Triggers
    • Viewing Trigger Source
    • Instead of Triggers
    • Lab 11 – DML Triggers
    • Lab 11 Solutions – DML Triggers
  • Advanced Concepts
    • Embedded Procedures
    • The Optimizing Compiler
    • PL/SQL Compiler Warnings
    • Compiling for Debugging
    • Conditional Compilation / Inquiry Directives
      • Error Directives
      • Inquiry Directives
      • Using Static Constants
    • DBMS_DB_VERSION
    • Native Compilation
      • Recompiling All Database Objects
    • Lab 12 – Advanced Concepts
    • Lab 12 Solutions – Advanced Concepts
  • File Operations
    • Moving Files Between Databases
    • Directory Access
    • File Manipulation
      • FCLOSE Procedure
      • FCLOSE_ALL Procedure
      • FCOPY Procedure
      • FFLUSH Procedure
      • FGETATTR Procedure
      • FGETPOS Function
      • FOPEN Function
      • FREMOVE Procedure
      • FRENAME Procedure
      • FSEEK Procedure
      • GET_LINE Procedure
      • GET_RAW Procedure
      • IS_OPEN Function
      • NEW_LINE Procedure
      • PUT Procedure
      • PUT_LINE Procedure
      • PUTF Procedure
      • PUT_RAW Procedure
    • Lab 13 – File Operations
    • Lab 13 Solutions – File Operations
  • Communications
    • DBMS_ALERT
      • REGISTER Procedure
      • REMOVE Procedure
      • REMOVEALL Procedure
      • SET_DEFAULTS Procedure
      • SIGNAL Procedure
      • WAITANY Procedure
      • WAITONE Procedure
      • Security
      • DBMS_ALERT Example
    • DBMS_PIPE
      • CREATE_PIPE Function
      • NEXT_ITEM_TYPE Function
      • PACK_MESSAGE Procedure
      • PURGE Procedure
      • RECEIVE_MESSAGE Function
      • RESET_BUFFER Procedure
      • REMOVE_PIPE Function
      • SEND_MESSAGE Function
      • UNIQUE_SESSION_NAME Function
      • UNPACK_MESSAGE Procedure
      • DBMS_PIPE Example
    • UTL_SMTP
    • UTL_HTTP
    • UTL_TCP
    • UTL_MAIL
    • DBMS_NETWORK_ACL_ADMIN
    • Lab 14 – Communications
    • Lab 14 Solutions – Communications
  • Security
    • DBMS_RLS
      • Implementation Tasks
      • Our Example Scenario
      • The Policy Package
      • The Dynamic Predicate Package
      • Creating the Context and The Trigger
      • Using DBMS_RLS
      • Exempting Policies
      • DBMS_FGA
      • Controlling Column Access with Virtual Columns
    • Lab 15 – Security
    • Lab 15 Solutions – Security
  • Scheduling
    • Oracle Scheduling
    • DBMS_JOB
      • PROCEDURE BROKEN
      • PROCEDURE CHANGE
      • PROCEDURE INSTANCE
      • PROCEDURE INTERVAL
      • PROCEDURE ISUBMIT
      • FUNCTION IS_JOBQ
      • PROCEDURE NEXT_DATE
      • PROCEDURE REMOVE
      • PROCEDURE RUN
      • PROCEDURE SUBMIT
      • PROCEDURE USER_EXPORT
      • PROCEDURE USER_EXPORT
      • PROCEDURE WHAT
      • The NEXT_DATE Parameter
      • The INTERVAL Parameter
      • The WHAT Parameter
    • DBMS_SCHEDULER
      • Evaluating Calendaring Expressions
      • DBMS_SCHEDULER Capabilities
    • Lab 16 – Scheduling
    • Lab 16 Solutions – Scheduling
  • Miscellaneous Packages
    • DBMS_OUTPUT
    • DBMS_RANDOM
      • STRING Function
      • VALUE Function
    • DBMS_STATS
      • GATHER_SCHEMA_STATS
      • GATHER_TABLE_STATS
    • DBMS_WM
    • DBMS_METADATA
    • DBMS_REDEFINITION
    • Other Notable Packages / Procedures
    • Lab 17 – Miscellaneous Packages
    • Lab 17 Solutions – Miscellaneous Packages
  • Database Triggers
    • Permissions Needed
    • DDL Triggers
    • SERVERERROR Triggers
    • LOGON / LOGOFF Triggers
    • STARTUP / SHUTDOWN Triggers
    • Suspend Triggers
    • Lab 18 – Database Triggers
    • Lab 18 Solutions – Database Triggers
  • Collections
    • Defining Records
    • Collections
      • Associative Arrays
      • Nested Tables
      • VARRAYs / VARYING ARRAYs
      • Assignments
      • Comparing Collections
    • Collection Methods
      • EXISTS
      • FIRST
      • LAST
      • COUNT
      • LIMIT
      • PRIOR
      • NEXT
      • DELETE
      • TRIM
    • Set Theory and Nested Tables
    • Lab 18 – Collections
    • Lab 18 Solutions – Collections
  • Bulk Operations
    • Bulk Binding
    • FORALL
    • SQL%BULK_ROWCOUNT
    • SAVE EXCEPTIONS / SQL%BULK_EXCEPTIONS
    • Bulk Collect
      • The LIMIT Clause
      • FORALL and the INDICES OF Clause
      • FORALL and VALUES OF
    • Pipelined Table Functions
    • Multidimensional Collections
    • Lab 19 – Bulk Operations
    • Lab 19 Solutions – Bulk Operations
  • Cursor Variables
    • What Is a Cursor Variable?
    • Cursor Variable Control
    • OPEN FOR
    • FETCH
    • CLOSE
    • Cursor Variable Examples
    • ROWTYPE_MISMATCH
    • Lab 20 – Cursor Variables
    • Lab 20 Solutions – Cursor Variables
  • Dynamic SQL
    • Standard Dynamic SQL
    • New DBMS_SQL Functionality
    • Native Dynamic SQL (NDS)
    • Execute Immediate
      • OPEN FOR, FETCH and CLOSE
      • Additional Rules for Native Dynamic SQL
    • Bulk Execute Immediate
    • SQL Injection Attacks
      • SQL Statement Manipulation
      • Additional Statement Insertion
      • The USING Clause Vs. Concatenation
      • DBMS_ASSERT
    • Switching Between NDS and DBMS_SQL
    • Lab 21 – Dynamic SQL
    • Lab 21 Solutions – Dynamic SQL
  • Large Objects
    • External vs. Internal Large Objects
    • Initializing Internal Lob Locators
    • The DBMS_LOB Package
    • Inserting Internal Large Objects
    • Loading Errors
    • The SECUREFILE Option
    • Lab 22 – Large Objects
    • Lab 22 Solutions – Large Objects
  • Object-Oriented Programming
    • Oracle Objects
    • Ordering Object Types
    • Object Tables
    • SQL for Object Tables
    • Advanced Object Concepts
    • Lab 23 – Object-Oriented Programming
    • Lab 23 Solutions – Object-Oriented Programming
  • Java and Other Languages
    • Alternative Languages
    • Loading Code
    • Publishing Code
    • Executing the Code
    • Interfacing with C
    • Enhanced Language Interface Features