Oracle Database 11g R2 – Advanced PL/SQL Programming and Tuning

Overview

Course Description

This Oracle 11g course will give attention to three fundamental pillars of effective implementation of PL/SQL applications. The PL/SQL programming language is at the core of most Oracle database applications.

Audience Profile

The target audience for this course is senior application developers. Developers who will be building, debugging and tuning PL/SQL program units will benefit from this course.

Prerequisites

There are no prerequisites for this course.

Learning Objectives

The PL/SQL programming language is at the core of most Oracle database applications. This training course will give attention to three fundamental pillars of effective implementation of PL/SQL applications. First, we will explore the advanced features of the language which allow powerful and adaptable database applications to be built. Next, we will discuss performance tuning techniques which allows these applications to run efficiently. Finally, we will consider critical security measures which should be implemented to counter hacker attacks and other security threats.

Course Outline


Course Outline

Module 1: Dynamic SQL
Lessons
• Advantages & Disadvantages
• Native Dynamic SQL
• Dynamic UPDATE…Returning
• DYNAMIC SQL USING DBMS_SQL()

Module 2: Using Collections
Lessons
• ABOUT COLLECTIONS
• BULK BIND USING COLLECTIONS
• COLLECTION METHODS
• MORE ABOUT THE RETURNING CLAUSE
• ADVANCED COLLECTION FEATURES
• Collection MULTISET Operations
• IN INDICES OF Clause
• IN VALUES OF Clause

Module 3: System-Supplied Packages: DBMS_METADAT()-Part 1
Lessons
• WHY RETRIEVE OBJECT DEFINITIONS?
• RETRIEVING DEFAULT METADATA
• RETRIEVING CUSTOMIZED METADATA
• Using OPEN() & CLOSE()
• Using SET_Filter()
• Using SET_COUNT()
• Using ADD_TRANSFORM()
• Using FETCH DDL()
• About SYS.KU$_DDL
• About SYS.KU$_DDLS
• Calling FETCH_DDL()

Module 4: System-Supplied Packages: DBMS_METADAT()-Part 2
Lessons
• SET_TRANSFORM_PARAM()
• GET_QUERY()

Module 5: System-Supplied Packages: DBMS_METADAT()-Part 3
Lessons
• FETCH CLOB()
• SET_FILTER() FOR DEPENDENT OBJECTS
• SET_PARSE_ITEM()
• PRIMARY & DEPENDENT OBJECT DDL

Module 6: System-Supplied Packages: DBMS_REDEFINITION
Lessons
• ABOUT TABLE REDFINITION
• USING DBMS_REDEFINITION()
• DBA_REDEFINITION_ERRORS
• CAN_REDEF_TABLE()
• START_REDEF_TABLE()
• FINISH_REDEF_TABLE()
• ABORT_ REDEF_TABLE()
• COPY_TABLE_DEPENDENTS()
• SYNC_INTERIM_TABLE()

Module 7: System-Supplied Packages: DBMS_LOB()
Lessons
• WORKING WITH EXTERNAL BFILES
• WORKING WITH INTERNAL LOBS
• LoadBLOBF0romFile(), LoadCLOBFromFile()
• Compare()
• GetLength()
• Append()
• Copy()
• Erase()
• Trim()
• Read()
• SUBSTR()
• INSTR()
• Write()
• DYNAMIC SECUREFILE OPTIONS
• GetOptions()
• SetOption()

Module 8: System-Supplied Packages: Others
Lessons
• COMPRESSION WITH UTIL_COMPRESS()
• LZ_COMPRESS()
• LZ_UNCOMPRESS()
• DBMS_DESCRIBE()
• UTL_MAIL()
• Set SMTP_OUT_SERVER
• Calling The Send() Procedure
• Calling The Send_Attach_xxx() Procedures
• DBMS_UTILITY()
• COMPILE_SCHEMA()
• DB_VERSION()
• WAIT_ON_PENDING_DML()

Module 9: Advanced Interface Methods
Lessons
• ABOUT EXTERNAL 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

Register for Course

Tuition
Available Registrations: Unlimited
The Tuition ticket is sold out. You can try another ticket or another date.

Cost

$2,650.00

Course Code

FRCORA-6

Course Duration

5

Instructions Method

Instructor Led
Category
REGISTER