Oracle PL/SQL developers will learn to use advanced features enabling improved programming and enhanced performance.
Upon successful completion of this course, class attendees should, with the aid of appropriate documentation be able to:
- Use advanced features of Oracle-supplied packages to enrich applications
- Maximize performance of PL/SQL through design and tuningUse Oracle object features including User-Defined Types, Nested Tables,Varrays, REFs, and Object Views via PL/SQL
- Use advanced data handling functionality including collections and bulk bind
- Know when dynamic SQL may be used to improve performance or functionality
- Create PL/SQL that uses external applications and the operating system
- Design and use PL/SQL packages, procedures, functions, and triggers to maximize performance
- Become aware of PL/SQL’s new features and how to use them
- Use XML from PL/SQL
Course Features
- Lecture 0
- Quiz 0
- Duration 2 Days
- Skill level All levels
- Language English
- Students 17
- Assessments Yes
Review of PL/SQL
- Exception Handling
- Block Structure, Syntax and Datatypes
- Procedures, Functions, Packages, Local subprograms
New PL/SQ:Features You Might Have Missed
Invoker Rights (AUTHID)
Autonomous Transactions
Computer Exercise: Use Invoker Rights and Autonomous Transactions
Function Optimization Hints (NOCOPY) Using Purity Features (WNDS, RNDS, RNPS, WNPS, TRUST)
Using Overloading
Dependencies
Triggers, Compound Triggers, and Triggers with FOLLOWS
SQL RETURNING Clause and PL/SQL
Computer Exercise: Use Advanced Features of Stored PL/SQL
Oracle-Supplied Packages: DBMS_LOB, UTL_FILE, UTL_MAIL, others
Fine-grained security: DBMS_RLS, DBMS_SESSION, others
Database object information and DBMS_METADATA
Computer Exercise: Working with Supplied Packages
Dynamic SQL
DBMS_SQL
Native Dynamic SQL: EXECUTE IMMEDIATE
Computer Exercise: Building SQL Dynamically
Cursors
- Implicit and Explicit Cursors
- Cursor design
Cursor variables and REF Cursors
Cursor variables as parameters
Cursor variables vs static cursors
Computer Exercise: Creating and Using REF Cursor
Oracle Object Support
Types and Subtypes
Computer Exercise: Creating Subtypes
Large Objects (BLOB, CLOB, BFILE)
- SQL and PL/SQL (DBMS_LOB) support for LOBs
- Directory Objects
- SecureFile and BasicFile
Optional Exercise: Using Large Objects
XML DB Concepts and Features
Using XMLType data (Binary, Structured, Unstructured)
XML Indexing
XML Functions (ISO and Oracle)
Loading data into XMLType
Using XMLType methods
Computer Exercise: Create XMLType Tables and Columns
Collections of Data
- Associative Arrays (PL/SQL tables)
- Nested Tables
- VARRAYS
Collection Exceptions
Choosing collection type
Exception handling
Using Packages for Constants
Computer Exercise: Working with PL/SQL Collections
Bulk-Bind in PL/SQL
BULK COLLECT
FORALL
SQL%BULK_EXCEPTIONS
Computer Exercise: PL/SQL Savings with Bulk Bind
Interfacing with non-PL/SQL
Using External C Routines and Operating System Commands
Create Java “Wrapper” to public Java class
Using Java classes from PL/SQL
Advanced Interface Methods
Computer Exercise: Calling Java Subroutines
Performance and PL/SQL
- BULK COLLECT and FORALL
- NOCOPY hint
- Using appropriate numeric types
- Package groupings
PL/SQL and SQL Results Cache
Learning the System Global Area (SGA) and the Impact on PL/SQL Processing
Understanding Memory Issues with Packages, Procedures, and Functions
“Pinning” PL/SQL
Using DBMS_MONITOR
Tracing PL/SQL Execution
Profiling PL/SQL Applications
Identification and Tuning of Memory Issues
PL/SQL Compiler
Computer Exercise: PL/SQL Performance Planning and Tuning
Design Considerations for PL/SQL Program Units
Overview of Edition Based Redefinition (EBR)
