Oracle Database 12c: PL/SQL Fundamentals

IN-602

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
Want To Share This?



Send Whatsapp Message To Us