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.
Course info
Rating
(76)
Level
Intermediate
Updated
May 8, 2015
Duration
4h 52m
Table of contents
Need for Collection Types & Their Characteristics
Associative Arrays
Collection Methods
Nested Tables
Nested Tables: Comparison, TABLE & MULTISET Operators
Varrays
Multilevel Collections & Converting Collections
Bulk Operations: Bulk Collect
Bulk Operations: FORALL
Description
Course info
Rating
(76)
Level
Intermediate
Updated
May 8, 2015
Duration
4h 52m
Description

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.

About the author
About the author

Experienced technologist, with expertise in various aspects of software development lifecycle, architecting software solutions and software development.

More from the author
Oracle REST Data Services
Beginner
5h 31m
Feb 13, 2018
Getting Productive with SQL Developer
Beginner
3h 53m
Apr 20, 2017
More courses by Pankaj Jain
Section Introduction Transcripts
Section Introduction Transcripts

Associative Arrays
Hello, and welcome to Pluralsight. My name is Pankaj Jain and welcome to this module on Associative Arrays. In this module, we will talk about associative arrays, the most commonly used array type in PL/SQL. We will start by talking about what is an associative array. We will see how to define, initialize, and use associative arrays. We will discuss the key, or the index of the associative array, and see how its data type can affect the sort order of the elements stored inside the collection. We will talk about the places where they can be declared, as well as how we will declare them affects their system visibility and their ability to provide session persistence. Finally, we will conclude by some usage guidelines for associative arrays.

Collection Methods
Hello, and welcome to Pluralsight. My name is Pankaj Jain and welcome to this module on Collection Methods. The collection methods provide an easy and powerful way to get more information about a collection, or manipulate a collection. In order to work effectively with collections, it is vital to have a good understanding of the collection methods. We have discussed the FIRST and NEXT collection methods in the previous module. In this module, we will go over in detail on some of the other useful collection methods. We will discuss these collection methods in the context of the associative arrays, since that is what we have covered so far, but the usage should be similar with other collection types. Most of the collection methods can be applied against all collection types except a few, like the TRIM and the EXTEND methods, which cannot be used with associative arrays. In this module, we will take a look at the LAST method, which gives us the reference to the last index pointer in the collection. The EXISTS method runs a check for the existence of an index counter and is a very useful method to avoid no data found exceptions. We will talk about the DELETE method and its various _____, the COUNT method, which is used to get the collection count, the PRIOR method, which can be used to iterate or a loop in reverse, the TRIM method which is used to trim a collection. We will take a look at the syntax of the EXTEND method, which is applicable against nested tables and varrays. I want to discuss it here in the context of collection methods, but we will see the detailed usage of the EXTEND method in the next module when we talk about nested tables. We have seen one way to iterate over a collection, that is using the WHILE Loop. Now, we will examine some other effective ways to iterate over a collection. There is a lot of exciting stuff to talk about. So let's get started.

Nested Tables
Hello, and welcome to Pluralsight. My name is Pankaj Jain and welcome to this module on Nested Tables. In this module and the next, we will take a look at nested tables, another collection type available in Oracle. In Oracle PL/SQL, associative arrays is the most commonly used collection type, but nested tables have some unique advantages in terms of the table expressions and multiset operator, which can make PL/SQL programming compact and simple. Let us take a closer look at this powerful collection type. In this module, we will talk about how we can define, initialize, and use nested tables. We will see how we can add and remove elements using methods like EXTEND, TRIM, and DELETE. We will talk about some of the exceptions we can encounter while working with nested tables so that we can program appropriately to avoid them. Finally, we will see how to define nested tables at the schema level, and interact with them in PL/SQL, how we can INSERT, DELETE, UPDATE, and SELECT from the nested table columns defined in database tables. In the next module, we will continue with our discussion on nested tables, and see the use of the powerful table expression, which can be used to un-nest nested table elements, and do piecewise insert, update, and delete operations on them. We will see the use of operators like multiset, set, submultiset, as well as see how we can compare nested tables. But first, let us talk about the fundamental concepts of working with nested tables in this module. Let's get started.

Nested Tables: Comparison, TABLE & MULTISET Operators
Hello and welcome to Pluralsight. My name is Pankaj Jain and welcome to the second module on nested tables where we'll talk about the Nested Tables: Comparison, TABLE and MULTISET Operators. In this module we will continue our discussion on nested tables and cover some important operators. We will start off with the TABLE Expression, which is a very easy and powerful way to unnest individual elements from out of the nested tables. The TABLE Expression also allows us to do piecewise DML and selects against nested table columns. We will take a look at examples of doing this. We will then talk about the MULTISET operator, the MULTISET union distinct and accept operators which allow us to do set operations against two nested tables. One of the advantages of working with nested tables is that they allow direct comparison of two nested tables. We will see how to do that. We will then complete our module by talking about some of the other useful operators like the SET, SUB MULTISET, CARDINALITY and MEMBER OF operators. These make working with nested tables very easy and provide a lot of useful utility functions. So let's get started.

Bulk Operations: Bulk Collect
Hello and welcome to Pluralsight. My name is Pankaj Jain and welcome to this module on Bulk Operations where we'll talk about Bulk Collect. In this module we will talk about bulk operations, one of the most powerful optimization techniques in PL/SQL. We will talk about bulk collect in this module, which is a way to fetch data in bulk from the Oracle server. We will cover the other bulk operation, the for all statement in the next module, which is a way to do bulk DML. We will start off with trying to understand how PL/SQL blocks are processed to better understand the need for bulk operations and the benefits they offer. We will then see how to use bulk collect in our PL/SQL code. We will talk about the limit clause, which is a way to control session memory usage with the bulk collect operation. Finally we will do a performance comparison between a normal cursor fetch and using bulk collect. We will see how using the bulk collect significantly reduces the time it takes to do the fetch operations. This is a very important topic and will greatly enhance the value of your PL/SQL code, so let's get started. As I have mentioned before, we have two kinds of bulk operations. The bulk collect is when we are trying to optimize our fetches from the database server. Forall is a converse operation where we are trying to optimize _____, our DML statements to the database. Let us now take a look at how a PL/SQL block is processed and in that context see the benefits of using bulk operations.

Bulk Operations: FORALL
Hello and welcome to Pluralsight. My name is Pankaj Jain and welcome to this module on the forall class, which is another construct for the bulk operations in Oracle PL/SQL. We saw in the last module the bulk collect statement, which is used to perform a fetch from the database in bulk. Forall is the other part of the bulk operations in Oracle, using which we perform bulk writes to the database. Using forall we can batch up inserts, updates and delete operations and send them in one network roundtrip to the database. This mechanism again provides tremendous performance gains. In this module we will take a look at how to define and use the forall clause in our PL/SQL code. The forall clause works with collections in order to send writes in bulk to the database. If the collections are sparse we have to use the indices of clause in order to process the records in the collection. You can also use the values of clause with forall, if you want to process the records in the collection with a specific order or process them more than once. We will talk about these in detail with examples. The forall statement executes a bunch of updates, deletes and insert statements in one call and if you want to know how many rows were affected by each of those DML statements, SQL%BULK_ROWCOUNT is useful to obtain that information. If you want that during the bulk operations, let us say while doing bulk inserts, even if one of the insert statements fails, we want to save that information and continue on with the rest of the inserts. The SQL%BULK_EXCEPTIONS collection can later provide us the information of which rows failed. Lastly, we will do a demo to see the performance boost we're can get by using the forall statement versus doing a DML using a regular for loop. So there's a lot of exciting stuff to talk about so let's get started.