Overview
Course Description
Thank you for reading this post, don't forget to subscribe!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