Advanced Oracle 11g PL/SQL

Advanced Oracle 11g PL/SQL

This Oracle 11g PL/SQL training teaches attendees advanced Oracle PL/SQL database programming skills.

BJECTIVES

• Invoke external procedures and integrate these into PL/SQL applications.
• Use dynamic SQL to extend the functionality and flexibility of database programs.
• Identify SQL injection attack vulnerabilities and security risks to protect against hacking.
• Incorporate collections and other advanced types into application logic to increase efficiency and execution speed.
• Work with LOBs, including piece-wise data manipulation and dynamic modification of SecureFile storage options.
• Expand functionality with system-supplied database utility packages.
• Tune with the DBMS_PROFILER() system-supplied package and debugging with the DBMS_TRACE() system-supplied package.
• Write efficient PL/SQL code and avoiding common coding mistakes.
• Enable native compilation and execution of all database-resident program units.
• Control and managing PL/SQL compilation for high-efficiency execution.
• Analyze PL/SQL code structure by means of the PL/Scope facility.
• Analyze PL/SQL application performance and tune bottlenecks using the PL/SQL Hierarchical Profiler.
• Implement fine-grained security mechanisms as part of an advanced security model.
• Use dynamic partitioning and DML parallelization using the system-supplied package DBMS_PARALLEL_EXECUTE().

ADVANCED ORACLE 11G PL/SQL TRAINING PREREQUISITES

All attendees must have a prior understanding of writing SQL queries, including joins.

ADVANCED ORACLE 11G PL/SQL TRAINING MATERIALS

All students receive comprehensive courseware and a related textbook.

SOFTWARE NEEDED FOR EACH PC:

• A complete installation of Oracle Enterprise Edition 11g or 11g R2
• Oracle SQL Developer
• For classes delivered online, all participants need either dual monitors or a separate device logged into the online session so that they can do their work on one screen and watch the instructor on the other. A separate computer connected to a projector or large screen TV would be another way for students to see the instructor’s screen simultaneously with working on their own.

ADVANCED ORACLE 11G PL/SQL TRAINING OUTLINE

• Dynamic SQL
  • Advantages & Disadvantages
  • Native Dynamic SQL
  • Dynamic Update…Returning
  • Dynamic SQL Using DBMS_SQL()
• Using Collections
  • 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
• System-Supplied Packages: DBMS_METADATA() – Part I
  • 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()
• System-Supplied Packages: DBMS_METADATA() – Part II
  • SET_TRANSFORM_PARAM()
  • GET_QUERY()
• System-Supplied Packages: DBMS_METADATA() – Part III
  • Fetch CLOB()
  • SET_FILTER() for Dependent Objects
  • SET_PARSE_ITEM()
  • Primary & Dependent Object Ddl
• System-Supplied Packages: DBMS_REDEFINITION()
  • About Table Redefinition
  • Using DBMS_REDEFINITION()
  • DBA_REDEFINITION_ERRORS()
  • CAN_REDEF_TABLE()
  • START_REDEF_TABLE()
  • FINISH_REDEF_TABLE()
  • ABORT_REDEF_TABLE()
  • COPY_TABLE_DEPENDENTS()
  • SYNC_INTERM_TABLE()
• System-Supplied Packages: DBMS_LOB()
  • Working with External Bfiles
  • Working with Internal Lobs
  • LOADBLOBFROMFILE(), LOADCLOBFROMFILE()
  • COMPARE()
  • GETLENGTH()
  • APPEND()
  • COPY()
  • ERASE()
  • TRIM()
  • READ()
  • SUBSTR()
  • INSTR()
  • WRITE()
  • Dynamic Securefile Options
  • GETOPTIONS()
• SETOPTIONS() System-Supplied Packages: Others
  • Compression With UTL_COMPRESS()
  • LZ_COMPRESS()
  • LZ_UN_COMPRESS()
  • DBMS_DESCRIBE()
  • UTL_MAIL()
  • Set SMTP_OUT_SERVER
  • Calling the SEND() Procedure
  • Calling the SEND_ATTACH_XXX() Procedures
  • DBMS_UTILITY()
  • COMPLIE_SCHEMA()
  • DB_VERSION()
  • WAIT_ON_PENDING_DML()
• Advanced Interface Methods
  • About External Procedures
  • Calling Java Classes
  • Calling C Programs
• PL/SQL Advanced Programming & Coding Techniques
  • Autonomous Transactions
  • Using Nocopy for Parameters
  • Choosing the Optimum Data Type
  • Avoiding Implicit Data Type Conversion
  • Choosing Between Number and Pls_Integer
  • About Pls_Integer
  • Using Simple_Integer
  • Char Variables of Different Lengths
  • Varchar2 Variables of Different Lengths
  • Char Vs. Varchar2
  • Char Vs. Varchar2 with an Equality
  • Comparison
  • Useful PL/SQL Coding Techniques
  • Handling String Literals
• Influencing Oracle PL/SQL Compilation
  • PL/SQL Compiler Optimization
  • PLSQL_Optimize_Level
  • Controlling Compilation Messages
  • PL/SQL Native Execution
• Dynamic Partitioning & Parallelization
  • 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
• Application Tuning with the PL/SQL Hierarchical Profiler
  • 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
• PL/SQL Debugging with DBMS_TRACE()
  • Using The Trace Facility
  • DBMS_TRACE() To Manage Runs
  • Examining The Trace Data
  • Event_Kind Values
• Protecting Against SQL Injection Attacks
  • Understanding The Threat
  • Applying Countermeasures
• Implementing Virtual Private Databases
  • Understanding VPDs
  • Preparing for a VPD
  • Configuring a VPD
  • Managing Application Contexts
  • Using SYS_CONTEXT()
  • Managing Policies & Security Rules
• Conclusion