Oracle Database 12c: PL/SQL Fundamentals

SELECTION & SETUP OF THE DATABASE INTERFACE

•      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 & SUBSTITUTION VARIABLES

•      USING SQL DEVELOPER
•      USING SQL*PLUS

CHOOSING A DATABASE PROGRAMMING LANGUAGE

•      WHAT IS DATABASE PROGRAMMING?
•      PL/SQL PERFORMANCE ADVANTAGES
•      INTEGRATION WITH OTHER LANGUAGES

PL/SQL LANGUAGE FUNDAMENTALS

•      PL/SQL PROGRAM STRUCTURE
•      LANGUAGE SYNTAX RULES
•      EMBEDDING SQL
•      WRITING READABLE CODE
•      GENERATING DATABASE OUTPUT
•      SQL*PLUS INPUT OF A PROGRAM BLOCK

DECLARE SECTION

•      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

BEGIN SECTION

•      ABOUT THE BEGIN SECTION
•      MANIPULATING PROGRAM DATA
•      LOGIC CONTROL & BRANCHING
•      GOTO
•      LOOP
•      IF-THEN-ELSE
•      CASE

EXCEPTION SECTION

•      ABOUT THE EXCEPTION SECTION
•      ISOLATING THE SPECIFIC EXCEPTION
•      PRAGMA EXCEPTION_INIT
•      SQLCODE & SQLERRM Example
•      SQL%ROWCOUNT & SELECT…INTO

BEYOND THE BASICS: EXPLICIT CURSORS

•      ABOUT EXPLICIT CURSORS
•      EXTENDED CURSOR TECHNIQUES
•      FOR UPDATE OF Clause
•      WHERE CURRENT OF Clause
•      Using FOR…LOOP Cursors

INTRODUCING DATABASE-RESIDENT PROGRAM UNITS

•      ABOUT DATABASE-RESIDENT PROGRAMS
•      PHYSICAL STORAGE & EXECUTION
•      TYPES OF STORED PROGRAM UNITS
•      STORED PROGRAM UNIT ADVANTAGES
•      MODULAR DESIGN PRINCIPLES

CREATING STORED PROCEDURES & FUNCTIONS

•      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

EXECUTING STORED PROCEDURES & FUNCTIONS

•      CALLING PROCEDURES & FUNCTIONS
•      UNIT TESTING WITH EXECUTE
•      ANONYMOUS BLOCK UNIT TESTING
•      SPECIFYING A PARAMETER NOTATION
•      SQL WORKSHEET UNIT TESTING
•      CALLING FUNCTIONS FROM SQL

MAINTAINING STORED PROGRAM UNITS

•      RECOMPILING PROGRAMS
•      Mass Recompilation Using UTL_RECOMP()
•      DROPPING PROCEDURES & FUNCTIONS
•      DROP PROCEDURE / FUNCTION
•      DATA DICTIONARY METADATA
•      Using USER_OBJECTS
•      Using USER_SOURCE
•      Using USER_ERRORS
•      Using USER_OBJECT_SIZE
•      Using USER_DEPENDENCIES

MANAGING DEPENDENCIES

•      DEPENDENCY INTERNALS
•      TRACKING DEPENDENCIES
•      THE DEPENDENCY TRACKING UTILITY
•      SQL DEVELOPER DEPENDENCY INFO
•      DEPENDENCY STRATEGY CHECKLISTS

CREATING & MAINTAINING PACKAGES

•      ABOUT PACKAGES
•      CREATING PACKAGES
•      MAINTAINING PACKAGES
•      PERFORMANCE CONSIDERATIONS

ADVANCED PACKAGE CAPABILITIES

•      DEFINER & INVOKER RIGHTS
•      WHITE LISTS & ACCESSIBLE BY
•      PERSISTENT GLOBAL OBJECTS
•      DEFINING INITIALIZATION LOGIC
•      OBJECT ORIENTATION SUPPORT

ADVANCED CURSOR TECHNIQUES

•      USING CURSOR VARIABLES
•      Using SYS_REFCURSOR
•      USING CURSOR EXPRESSIONS

USING SYSTEM-SUPPLIED PACKAGES

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

DATABASE TRIGGER CONCEPTS

•      ABOUT DATABASE TRIGGERS
•      DML EVENT TRIGGER SUB-TYPES
•      DATABASE TRIGGER SCENARIO
•      TRIGGER EXECUTION MECHANISMS
•      TRIGGERS WITHIN SQL WORKSHEET

CREATING DATABASE TRIGGERS

•      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 AN APPLICATION

MAINTAINING DATABASE TRIGGERS

•      CALL SYNTAX
•      TRIGGER MAINTENANCE TASKS
•      SHOW ERRORS TRIGGER
•      DROP TRIGGER
•      ALTER TRIGGER
•      MULTIPLE TRIGGERS FOR A TABLE
•      HANDLING MUTATING TABLE ISSUES

IMPLEMENTING SYSTEM EVENT TRIGGERS

•      WHAT ARE SYSTEM EVENT TRIGGERS?
•      DEFINING THE SCOPE
•      AVAILABLE SYSTEM EVENTS
•      SYSTEM EVENT ATTRIBUTES

INTRODUCTION: MANAGING THE DATABASE INSTANCE

• THE DATABASE MANAGEMENT TOOLS
• EM DATABASE EXPRESS
• EM CLOUD CONTROL
• SQL DEVELOPER
• DATABASE ADMINISTRATION DUTIES

INTRODUCTION: DATABASE ARCHITECTURE REVIEW

• DATABASE INSTANCE CONFIGURATIONS
• MEMORY STRUCTURES
• PROCESS STRUCTURES
• STORAGE ARCHITECTURE

INTRODUCTION: STARTING & STOPPING DATABASE

SERVICES

• START & STOP THE DATABASE LISTENER
• ABOUT DATABASE STARTUP/SHUTDOWN
• SYSOPER
• SYSDBA
• DATABASE STATE & STAGES
• STARTUP WITH EM CLOUD CONTROL
• STARTUP WITH SQL*PLUS
• SHUTDOWN WITH EM CLOUD CONTROL
• SHUTDOWN WITH SQL*PLUS
• USING MS WINDOWS SERVICES

INTRODUCTION: ORACLE NETWORK ENVIRONMENT

• ORACLE NET SERVICES ARCHITECTURE
• ORACLE NET CONFIGURATION ASSISTANT
• ORACLE NET MANAGER
• USING EM CLOUD CONTROL
• ADVANCED NETWORK COMPRESSION
• TROUBLESHOOTING NETWORK PROBLEMS

INTRODUCTION: DIAGNOSTICS & ORACLE SUPPORT

• ABOUT DATABASE DIAGNOSTICS
• FAULT DIAGNOSABILITY INFRASTRUCTURE
• DATABASE INSTANCE HEALTH SNAPSHOT
• THE SUPPORT WORKBENCH
• PROACTIVE DATABASE HEALTH CHECKS
• PACKAGE DIAGNOSTIC DATA
• WORKING WITH THE KNOWLEDGE BASE

STORAGE: TABLESPACE MANAGEMENT

• STARTER TABLESPACES
• CREATE TABLESPACE WITH EM CC
• CREATE TABLESPACE WITH SQL
• MANAGE TABLESPACES WITH EM CC
• MANAGE TABLESPACES WITH EM DE
• MANAGE TABLESPACES WITH SQL
• DROP TABLESPACE
• ALTER TABLESPACE

STORAGE: ADVANCED TABLESPACE MANAGEMENT

• MANAGING TEMPORARY TABLESPACES
• TEMP TABLESPACE GROUPS WITH EM
• TEMP TABLESPACE GROUPS WITH SQL
• DEFAULT PERMANENT TABLESPACES
• BIGFILE TABLESPACES
• MANAGING THE SYSAUX TABLESPACE

STORAGE: DATA FILES & TEMP FILES MANAGEMENT

• MANAGE DATA FILES WITH EM
• ACCESS METADATA WITH SQL
• MANAGE DATA FILES WITH SQL
• MANAGE TEMP FILES
• ORACLE-MANAGED FILES (OMF)

STORAGE: UNDO DATA & UNDO TABLESPACES

• ABOUT UNDO SPACE MANAGEMENT
• CONFIGURE UNDO MANAGEMENT
• GUARANTEE UNDO RETENTION
• MONITOR UNDO SPACE WITH SQL
• USE THE EM UNDO ADVISOR

SECURITY & SCHEMAS: SECURITY OVERVIEW & DATABASE PRIVILEGES

• DATABASE SECURITY PRINCIPLES
• DATABASE SYSTEM PRIVILEGES
• DATABASE OBJECT PRIVILEGES
• SUPER ADMINISTRATOR ROLES
• PRIVILEGE ANALYSIS

SECURITY & SCHEMAS: USER SECURITY

• CREATE & MANAGE USER ACCOUNTS
• MANAGE USERS WITH EM
• MANAGE USERS WITH SQL
• CREATE USER
• ALTER USER
• DROP USER
• GRANT & REVOKE PRIVILEGES
• RESOURCE LIMITS VIA PROFILES
• ALTER PROFILE
• DROP PROFILE
• PASSWORD MANAGEMENT VIA PROFILES
• ROLE-BASED SECURITY MANAGEMENT
• ALTER USER…DEFAULT ROLE
• SET ROLE
• DELEGATING PRIVILEGE AUTHORIZATION
• WITH ADMIN OPTION & System Privileges
• WITH GRANT OPTION & Object Privileges
• PRINCIPLE OF LEAST PRIVILEGE CHECKLIST

SECURITY & SCHEMAS: DATABASE AUDITING

• AUDITING DATABASE ACTIVITY
• TRADITIONAL AUDITING
• UNIFIED AUDITING
• CREATE AUDIT POLICY

SECURITY & SCHEMAS: SQL LOADER

• LOADER CONCEPTS
• LOADER FROM THE COMMAND LINE
• CONTROL FILE OPTIONS
• LOAD METHODS
• LOADER EXPRESS MODE
• LOADER FROM EM

SECURITY & SCHEMAS: EXPORT & IMPORT

• MANAGE DIRECTORY OBJECTS
• THE DATA PUMP ARCHITECTURE
• DATA PUMP EXPORT
• DATA PUMP IMPORT
• DATA PUMP DICTIONARY VIEWS
• USING THE EM CC INTERFACE

PERFORMANCE & AVAILABILITY: MANAGING PERFORMANCE & SQL TUNING

• MANAGEMENT ADVISORY FRAMEWORK
• PERFORMANCE MONITORING & AWR
• THE SQL TUNING ADVISOR
• MONITORING EXCEPTIONS WITH METRICS

PERFORMANCE & AVAILABILITY: DATA CONCURRENCY

• ABOUT SYSTEM & USER LOCKS
• MONITOR & MANAGE USER LOCKS
• MONITOR LOCKS WITH DATA DICTIONARY
• MANAGE LOCKS WITH EM

PERFORMANCE & AVAILABILITY: BACKUP & RECOVERY CONCEPTS

• BACKUP & RECOVERY STRUCTURES
• MANAGING REDO DATA
• CONFIGURING FOR RECOVERABILITY
• INSTANCE RECOVERY
• RECOVERABILITY CHECKLIST