Working with Collections in Oracle PL/SQL
In this course, we will take an in-depth look at the collection types available in PL/SQL, namely Associative Arrays, Varrays, and Nested Tables. We'll also talk about the bulk fetch and collect features which greatly enhance the performance our PL/SQL code.
What you'll learn
Oracle's relational databases are used by thousands of companies around the globe. PL/SQL (Procedural Language/Structured Query Language) is Oracle Corporation's procedural language extension for SQL which allows us to procedurally tie SQL statements to effectively define business logic. In this course, we will talk about collection types available in PL/SQL. Collections Types are an extremely important part of any programming language. The collection types, along with providing a mechanism to conveniently handle sets of data, can also boost performance. Oracle offers a rich set of collection types in Associative Arrays, Varrays, and Nested Tables. We will take an in-depth look at these powerful datatypes and get familiar with the Oracle bulk fetch and collect features which greatly enhance the performance of our PL/SQL code.
Table of contents
- Module Overview 1m
- Defining Associative Arrays 4m
- Assigning Values to Associative Arrays 4m
- Demo: Assigning Values & Cursor Fetch to Associative Arrays 4m
- Associative Array Index 2m
- Demo: Associative Array Index 1m
- Exceptions with Associative Arrays 2m
- Demo: Exceptions 2m
- FIRST & NEXT Collection Methods 4m
- Demo: Associative Array Sorting 2m
- Visibility & Session Persistence 3m
- Demo: Session Persistence 6m
- Oracle Supplied Associative Arrays 1m
- Comparing Associative Arrays 1m
- Usage Guidelines 1m
- Summary 1m
- Module Overview 2m
- Declare & Initialize Nested Tables 4m
- Demo: Initialization 1m
- Adding Elements 5m
- Removing Elements 1m
- Nested Table Assignments 1m
- Demo: Nested Table Assignments 5m
- Exceptions 3m
- Trim Method 2m
- Demo: Trim Method 4m
- Schema Level Nested Tables 6m
- DML on Nested Table Columns 5m
- Demo: DML on Nested Table Columns 5m
- Summary 1m
- Characteristics & Usage Guidelines 4m
- Defining & Initializing Varrays 3m
- Demo: Defining & Initializing Varrays 2m
- Extending & Reducing Varray Size and Varray Assignments 5m
- Demo: Extending & Reeducing Varray Size and Varray Assignments 5m
- Exceptions 1m
- Demo: Exceptions 2m
- Schema Level Varrays 3m
- DML Against Varrays in Database Tables 4m
- Demo: DML Against Varray Columns & TABLE Expression 5m
- Summary 2m
- Overview 2m
- Context Switches 2m
- Bulk Collect 3m
- Bulk Collect with Direct Select 3m
- Demo: Bulk Collect with Direct Select 2m
- Bulk Collect with Cursor Fetch 3m
- Demo: Bulk Collect with Cursor Fetch & Performance Comparison 3m
- Bulk Collect with Dynamic SQL and Returning into Statements 2m
- Demo: Bulk Collect with Dynamic SQL and Returning into Statements 2m
- Summary 2m
About the author
Pankaj Jain is an experienced technologist, with expertise in various aspects of software development lifecycle, architecting software solutions and software development. He has worked on an array of technologies, with focus on Oracle at the database layer and Java at the web tier. He is a certified Oracle 11g DBA. He lives in Seattle with his wife Simita and boys Ronit and Rohak. When he is not playing with technology, he likes spending time with his kids, travelling and doing photography.