Packt+ | Advance your knowledge in tech

You're reading from Advanced Oracle PL/SQL Developer's Guide (Second Edition) - Second Edition

Product typeBook

Published inFeb 2016

Reading LevelIntermediate

Publisher

ISBN-139781785284809

Edition2nd Edition

Languages

SQL

Tools

Oracle Database

Concepts

Database Administration

Author (1)

Saurabh K. Gupta

Chapter 4. Using Collections

A collection is a single-dimensional structure of homogeneous elements. Behaviorally speaking, it is quite similar to an array and a list structure available in other third generation languages. First introduced in Oracle 7 as PL/SQL tables, Oracle 8i rebranded collections as Index-by tables. Oracle 8i also introduced persistent collection types, namely nested tables and varrays. Oracle Database 9i renamed Index-by-tables to associative arrays.

Oracle Database offers a wide scope of usability of collections in PL/SQL programming. The language semantics not only allow you to create and maintain collections, but also provides multiple methods for array operations. This chapter helps you to understand the collection types in Oracle and, most importantly, which types suit a given problem. The chapter outline looks like this:

An introduction to collections
- Categorization
- Selection of an appropriate collection type
Associative arrays
Nested tables
Varrays
PL/SQL collection...

Introduction to collections

A collection is an array like homogeneous single-dimensional structure, which holds a set of elements of similar data type. Each cell in a collection, and hence, each element, is uniquely identified by its position index or the subscript. The element or the value contained in a cell can be of a valid SQL data type or a user-defined type. There are three types of collections: associative array; nested table; and varray.

A collection can be bounded or unbounded on the basis of it's collection size. Bounded collections have a fixed number of elements, as in arrays. Unbounded collections can have any number of elements. Varrays are bounded collections while the associative arrays and nested tables are unbounded.

On the basis of persistence in the database, a collection can be either a persistent or non-persistent collection. A persistent or SQL collection can either be created in the Oracle Database schema or declared within the PL/SQL block. A PL/SQL or non-persistent...

Collection types

An associative array is a non-persistent unbounded collection which means that an associative array cannot be created in the Oracle Database schema, but locally declared within the declarative section of a PL/SQL block.

A nested table is a persistent collection of homogeneous elements that can be created in a database as a schema object as well as declared within a PL/SQL block. A nested table being an unbounded collection, has no limit on the number of elements.

A varray is a single-dimensional homogeneous collection that can be created in a database as well as in PL/SQL. Being a bounded collection, it can hold only a fixed number of elements.

Associative arrays

Associative arrays in Oracle are similar to conventional lists in other programming languages. An associative array is an unbounded array of cells and always defined in the declarative section of a PL/SQL program. While a cell is identified by an index of number or string type, it can hold a value of a scalar data type or user-defined composite type.

The syntax to declare an associative array in a PL/SQL block is as follows:

TYPE [COLL NAME] IS TABLE OF [ELEMENT DATA TYPE] NOT NULL
  INDEX BY [INDEX DATA TYPE]

In the syntax, the [INDEX DATA TYPE] signifies the data type of an array subscript. It can be BINARY_INTEGER, PLS_INTEGER, POSITIVE, NATURAL, SIGNTYPE, or VARCHAR2. The data types that are not supported as index types are RAW, NUMBER, LONG-RAW, ROWID, and CHAR.

The [ELEMENT DATA TYPE] can be one of the following:

PL/SQL scalar data type: NUMBER (along with its subtypes), VARCHAR2 (and its subtypes), DATE, BLOB, CLOB, or BOOLEAN
Inferred data: The data type inherited from...

Nested tables

A nested table is a persistent SQL collection that is used as a list to hold elements of the same data type. It can also be created in the database and defined in a PL/SQL program. It is an unbounded collection and the user doesn't have to maintain the cell index. Oracle automatically assigns the cell index as 1 to the first cell and moves onwards incrementally. A nested table collection type initially starts off as a dense collection but it becomes sparse due to delete operations.

Note

A dense collection refers to a collection that is tightly populated which means no empty cells between the lower and upper indexes of the collection. A dense collection may become sparse by performing delete operations.

When a nested table is created as a schema object in the database, it can be referenced in a PL/SQL block as a variable parameter. A column of nested table types can be included in a table. An attribute of nested table types can exist in an object type. In a database schema, a nested...

Varray

Oracle Database 8i introduced varrays as a modified format of a nested table. The varray or variable size array is quite similar to nested tables but bounded in nature. The varray declaration includes the count of elements that a varray can accommodate. The minimum varray index is 1, the current size is the total number of elements, and the maximum limit is the varray size. At any moment, the current size cannot exceed the maximum limit. Varrays are appropriately used when you know the maximum number of elements in a collection structure.

Like nested tables, varrays can be created in the database as schema objects as well as in a PL/SQL block. When created in the database as a schema object, varrays can be referenced in PL/SQL program units as variables, parameters and function return types. A table can have a column of a varray type. An object type can have an attribute of a varray type. The syntax for varrays, when defined as a database collection type, is as follows:

CREATE [OR REPLACE...

Comparing the collection types

The following table compares the collection types based on the considerations.

Oracle 12c enhancements to collections

Oracle Database 12c allows a join between a table and a collection type. If an SQL collection type is used as a return type of a function, the table and the function output can be joined using CROSS APPLY and OUTER APPLY. The function must use a value from the joining table as a parameter and return a collection variable of a nested table or varray type.

For the purpose of illustration, let us create the test tables using dictionary views from the Oracle Database. The table T_TBS_OBJ contains the tablespace information and T_SEGMENTS contains the segments created on these tablespaces:

/*Create table T_TBS_OBJ*/
CREATE TABLE t_tbs_obj
AS
SELECT tablespace_name, status, allocation_type
FROM user_tablespaces
/
/*Create table T_SEGMENTS*/
CREATE TABLE t_segments
AS
SELECT segment_name, segment_type, tablespace_name, bytes, blocks
FROM user_segments
/

We'll create the nested table collection to be used in the string:

CREATE TYPE nt_string AS TABLE OF VARCHAR2...

PL/SQL collection methods

Oracle provides a set of methods that can work with collections for development operations. The development operations can be deleting an element, trimming the collection type, or fetching first and last subscripts. The syntax for all the collection methods is as follows:

[COLLECTION].METHOD (PARAMETERS)

EXISTS

The EXISTS function checks the existence of an element in a collection. The general syntax of this function is [COLLECTION].EXISTS(<index>). It accepts the subscript as the input argument and searches for it in the associated collection. If the element corresponding to the index exists, it returns TRUE, or else returns FALSE. It is the only method that doesn't raise any exception when used with a collection.

The following PL/SQL block declares a local nested table collection and its two variables. While one array is uninitialized, the other one is initialized with sample data. It checks the existence of the first element in both arrays:

/*Enable the SERVEROUTPUT...

Summary

This chapter covers one of the more interesting features of PL/SQL programming—collections. We discussed and learned the three types of collections in PL/SQL that is associative arrays, nested tables and varrays. Readers should understand the application of these collection types in different situations. The comparative study of all three would help readers to differentiate between each one and choose the most appropriate one in their development.

The next chapter will explore another distinctive capacity of PL/SQL as a language, that is in the use of external procedures.

Practice exercise

Which two statements are true about associative arrays?
1. Associative arrays can have negative subscripts.
2. Associative arrays are always dense collections.
3. Associative arrays don't need initialization in a PL/SQL block.
4. The upper limit of associative arrays can by dynamically modified.
Which of the following statements is true about nested tables?
1. Nested tables are stored in a segment different from that of a parent table.
2. Nested table columns can have string subscripts.
3. Nested tables can grow dynamically up to any extent.
4. A database column of nested table collection type can be separately queried by its storage name.
Only varrays can have sequential numbers as subscripts.
1. True
2. False

Which of the following associative array declarations is/are correct:

DECLARE
TYPE T1 IS TABLE OF NUMBER INDEX BY BOOLEAN;
TYPE T2 IS TABLE OF VARCHAR2(10) INDEX BY NUMBER;
TYPE T3 IS TABLE OF DATE INDEX BY SIGNTYPE;
TYPE T4 IS TABLE OF EMPLOYEES%ROWTYPE INDEX BY POSITIVE;
BEGIN
…
…
END;

T1
T2
T3
T4

Which of the...

The rest of the chapter is locked

You have been reading a chapter from

Advanced Oracle PL/SQL Developer's Guide (Second Edition) - Second Edition

Published in: Feb 2016Publisher: ISBN-13: 9781785284809

A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.

undefined

Unlock this book and the full library FREE for 7 days

Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of

Start free trial

Renews at $15.99/month. Cancel anytime

Author (1)

Saurabh K. Gupta

Saurabh K. Gupta is a seasoned database technologist with extensive experience in designing high performance and highly available database applications. His technology focus has been centered around Oracle Database architecture, Oracle Cloud platform, Database In-Memory, Database Consolidation, Multitenant, Exadata, Big Data, and Hadoop. He has authored the first edition of this book. He is an active speaker at technical conferences from Oracle Technology Network, IOUG Collaborate'15, AIOUG Sangam, and Tech Days. Connect with him on his twitter handle (or SAURABHKG) or through his technical blog www.sbhoracle. wordpress.com, with comments, suggestions, and feedback regarding this book.
Read more about Saurabh K. Gupta

Personalised recommendations for you

Based on your interests and search pattern

Et al.

Ever wonder why speech recognition systems don't understand the Scottish accent, or what would happen if an astronaut only ate mac 'n' cheese, or other spurious reflections you'd have at a bar? We did, then collated those deliberations into absurd research articles with fake figures and methodologies inspired by even more fictionally absurd studies.

BookAug 2023230 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages4

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages1

Generative AI with LangChain

This book is a comprehensive introduction to LLMs and LangChain, demystifying the basic mechanics of LangChain, its functionalities, and the myriad of applications it can be integrated into.

BookDec 2023360 pages5

Mastering Tableau 2023

This book is a comprehensive resource to mastering your Tableau skills and becoming a BI expert. As you progress, you will learn how to build advanced dashboards and improve your storytelling to derive key business insight, as well as make you well-versed with advanced functionalities of Tableau in the business intelligence domain.

BookAug 2023684 pages

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages5

Building AI Applications with ChatGPT APIs

This guide covers all ChatGPT API features for effortless creation of robust AI powered apps. With its help, you’ll be able to leverage ChatGPT’s cutting-edge NLP models to take your app development skills to the next level. You’ll also work on ten exciting projects that will give you the practical know-how that you can apply to your existing applications.

BookSep 2023258 pages2

Data Engineering with AWS

Embark on a journey to master data engineering pipelines on AWS! Our book offers a hands-on experience of AWS services for ingesting, transforming, and consuming data. Whether you're an absolute beginner or someone with basic data engineering experience, this guide is an indispensable resource.

BookOct 2023636 pages5

Modern Data Architecture on AWS

Every organization wants an agile, performant, and cost-effective data platform that meets all their current and future business needs. Purpose-built AWS analytics services and their features play a big part in building such a modern data platform. This book brings to you all the design and architectural patterns that’ll help you achieve this goal.

BookAug 2023420 pages5

Practical Guide to Applied Conformal Prediction in Python

Discover the power of Conformal Prediction with the "Practical Guide to Applied Conformal Prediction in Python." Master the latest techniques to quantify uncertainty in machine learning and computer vision models, and seamlessly apply them to your industry applications.

BookDec 2023240 pages

TinyML Cookbook

With over 70 project-based recipes, the TinyML Cookbook is a practical guide that will help you to get the most out of your microcontrollers. It provides a comprehensive understanding of the theoretical foundations while giving you hands-on experience training ML models for deployment on Arduino Nano 33 BLE Sense, Raspberry Pi Pico, and SparkFun RedBoard Artemis Nano microcontrollers.

BookNov 2023664 pages

Comparing factor	Nested table	Varray	Associative array
Maximum size	Unbound and grows dynamically.	Bounded. However, varray size, can only be increased thereafter.	Unbound and grows dynamically.
Sparsity	Starts dense but may become sparse due to deletions.	Always dense.	Can be sparse.
Storage considerations	Out-of-line storage in a separate storage table.	In-line storage up to 4000 bytes. For out-of-line storage, the LOB clause must be specified.	Non-persistent collection uses program memory that is UGA.
Querying ability	A nested table type column can be queried as an instance. However, unnesting of an instance is possible using the `TABLE` or `CAST` clause.	A varray type column can be queried as an instance. However, unnesting of an instance is possible using the `TABLE` or `CAST` clause.	Non-persistent collection.
DML operations	Piecewise and atomic operations possible...