Overview
Course Description
Thank you for reading this post, don't forget to subscribe!In this course, students will learn the foundation for the programming series and the use of database-resident stored program units for Oracle 12c.
Audience Profile
The target audience for this textbook is all Oracle professionals. Among the specific groups for whom this textbook will be helpful are:
- Application designers and database developers
- Database administrators
- Web server administrators
Prerequisites
Oracle Database 12c: SQL Fundamentals
Learning Objectives
Upon successful completion of this course, students will be able to work with Oracle database programming using the PL/SQL programming language. They will learn the syntax, structure and features of the language.
Course Outline
Course Outline
Module 1: Selection & Setup of the Database Interface
Lessons
• Considering Available Tools
• Selecting the Appropriate Tool
• Oracle Net Database Connections
• Oracle PAAS Database Connections
• Setup SQL Developer
• Setup SQL*Plus
• Setup Jdeveloper
Module 2: About Bind & Substitution Variables
Lessons
• Using SQL Developer
• Using SQL*Plus
Module 3: Choosing a Database Programming Language
Lessons
• What is Database Programming?
• PL/SQL Performance Advantages
• Integration with Other Languages
Module 4: PL/SQL
Lessons
• PL/SQL Program Structure
• LANGUAGE SYNTAX RULES
• EMBEDDING SQL
• WRITING READABLE CODE
• GENERATING DATABASE OUTPUT
• SQL*PLUS INPUT OF A PROGRAM BLOCK
Module 5: Declare Section
Lessons
• 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
Module 6: Begin Section
Lessons
• About the Begin Section
• Manipulating Program Data
• Logic Control & Branching
• GOTO
• LOOP
• IF-THEN-ELSE
• CASE
Module 7: Exception Section
Lessons
• ABOUT THE EXCEPTION SECTION
• ISOLATING THE SPECIFIC EXCEPTION
• PRAGMA EXCEPTION_INIT
• SQLCODE &SQLERRM Example
• SQL%ROWCOUNT &SELECT…INTO
Module 8: Beyond the Basics: Explicit Cursors
Lessons
• ABOUT EXPLICIT CURSORS
• EXTENDED CURSOR TECHNIQUES
• FOR UPDATE OF Clause
• WHERE CURRENT OF Clause
• Using FOR…LOOP Cursors
Module 9: Beyond the Basics: Nested Blocks
Lessons
Module 10: Beyond the Basics: Declared Subprograms
Lessons
• USING DECLARED SUBPROGRAMS
• DECLARED PROCEDURE
• DECLARED FUNCTION
Module 11: Introducing Database-Resident Program Units
Lessons
• ABOUT DATABASE—RESIDENT PROGRAMS
• PHYSICAL STORAGE & EXECUTION
• TYPES OF STORED PROGRAM UNITS
• STORED PROGRAM UNIT ADVANTAGES
• MODULAR DESIGN PRINCIPLES
Module 12: Creating Stored Procedures & Functions
Lessons
• 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
Module 13: Executing Stored Procedures & Functions
Lessons
• CALLING PROCEDURES & FUNCTIONS
• UNIT TESTING WITH EXECUTE
• ANONYMOUS BLOCK UNIT TESTING
• SPECIFYING A PARAMETER NOTATION
• SQL WORKSHEET UNIT TESTING
• CALLING FUNCTIONS FROM SQL
Module 14: Maintaining Stored Program Units
Lessons
• 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
Module 15: Managing Dependencies
Lessons
• DEPENDENCY INTERNALS
• TRACKING DEPENDENCIES
• THE DEPENDENCY TRACKING UTILITY
• SQL DEVELOPER DEPENDENCY INFO
• DEPENDENCY STRATEGY CHECKLISTS
Module 16: Creating & Maintaining Packages
Lessons
• ABOUT PACKAGES
• CREATING PACKAGES
• MAINTAINING PACKAGES
• PERFORMANCE CONSIDERATIONS
Module 17: Advanced Package Capabilities
Lessons
• PROCEDURES
• CALLING JAVA CLASSES
• CALLING C PROGRAMS
Module 10: PL/SQL Advanced Programming & Coding Techniques
Lessons
• AUTONOMOUS TRANSACTIONS
• USING NOCOPY FOR PARAMETERS
• CHOOSING THE OPTIMUM DATA TYPE
• Avoiding Implicit Data type Conversion
• Choosing Between NUMBER And PLS_INTEGER
• About PLS_INTERGER
• Using SIMPLE_INTERGER
• CHAR Variables of Different Lengths
• CHAR Vs. VARCHAR2
• CHAR Vs. VARCHAR2 With an Equality Comparison
• USERFUL PL/SQL CODING TECHNIQUES
• HANDLING STRING LITERALS
Module 11: Influencing Oracle PL/SQL Compilation
Lessons
• PL/SQL COMPILER OPTIMIZATION
• PLSQL_OPTIMIZ_LEVEL
• CONTROLLING COMPILATION MESSAGES
• PL/SQL NATIVE EXECUTION
Module 12: Dynamic Partitioning & Parrellelization
Lessons
• ABOUT DYNAMIC PARTITIONING (CHUNKS)
• CREATING & PROCESSING CHUNKS
• CREATE_TASK()
• CREATE_CHUNKS_BY_ROWID()
• CREATE_CHUNKS_BY_NUMBER_COL()
• EXECUTE_RUN_TASK()
• TASK_STATUS()
• DROP_TASK()
• MONITORING CHUNK PROCESSING
Module 13: Application Tuning with the PL/SQL Hierarchical Profiler
Lessons
• WHAT IS THE HIERARCHICAL PROFILER?
• CONFIGURING THE PROFILER
• MANAGING PROFILER RUNS
• ANALYZING PROFILER DATA
• INTERPRETING THE RESULTS
• DBMSHP_RUNS
• DBMSHP_FUNCTION_INFO
• DBMSHP_PARENT_CHILD_INFO
Module 14: PL/SQL Debuggin with DBMS_TRACE()
Lessons
• USING THE TRACE FACILITY
• DBMS_TRACE() TO MANAGE RUNS
• EXAMINING THE TRACE DATA
• EVENT_KIND VALUES
Module 15: Protecting Against SQL Injection Attacks
Lessons
• UNDERSTANDING THE THREAT
• APPLYING COUNTERMEASURES
Module 16: Implementing Virtual Private Databases
Lessons
• UNDERSTNADING VPDS
• PREPARING FOR A VPD
• CONFIURING A VPD
• MANAGING APPLICATION CONTEXTS
• Using SYS_CONTEXT()
• MANAGING POLICIES & SECURITY RULES