Programming Samples

Click here to go to:



Excel VBA

Word VBA

MS Access

T-SQL

SSIS

SSRS

Power BI

Crystal Reports

SSAS

SQL Replication

C# Code

ASP .NET Code

Oracle PL/SQL

Database Diagramming


Back to Home Page


PL/SQL Nested Tables

Nested Table Types in SQL *Plus

This article describes how to create and use the single dimensional structures known as nested tables. These collections can be used in PL/SQL code as types or as types within Tables. Nested Table Type Topics include: creating a table with this type, inserting a record with this type and updating a record with this type.

PL/SQL Nested Table Using Initialized Values

To get started using a Nested Table, create a code block that declares a nested table type and instantiates a nested table of this created type (Lines 2 & 4). This code iterates through a FOR Loop 3 times to show that the Nested Table has been initialized with 3 Null members. The values of the Nested table are then assigned and another FOR Loop iterates 3 times to output the values of the Nested Table once again.

Nested Table

The results from DBMS_OUTPUT are as expected. 3 lines with no results from the null values and 3 lines with the populated data.

Results

PL/SQL Nested Table Using Extend

The next example declares a type of Nested Table on line 2 and then declares a variable of this new type, Animal_Table on line 3 again. This time, the values of the array and the size of the array are not instantiated. This sample uses the Extend method of the Nested Table to add members within a FOR loop. An IF statement is used to assign the values according to the value of the integer i as the FOR loop is executed. A 2nd loop at line 22 iterates through the nested table to output the values.

Nested Table with Extend

The values are output to the screen as expected.

Extend Nested Table values

Creating and Using Nested Table Database Types

The next section creates a Nested Table type in the database schema for use as a column in a Table. First, the type is declared and created. Then the Table is created with an ID, Name and an address of the Nested Table type created.

Note the additional code syntax on Line 5 that tells Oracle that the column is a nested table type. Without this code, an ORA-22913 error will be generated and the table will not be created.

Type & Table

An INSERT statement into this table requires a call to the type name, emp_addr, and the data is contained inside of the parentheses on Line 3.

INSERT statement Nested table

Create a SELECT statement to view the data in the Employees table using the keyword column_value and THE with a subquery. The information appears to be the data entered by the previously executed INSERT statement.

SELECT query on column_value

Retrieving Data from a PL/SQL Nested Table Type

To get all of the data for the inserted employee, a UNION ALL query will be used with the hidden rownum column to sort the data. The first part of the query uses a SELECT statement to pull the information from a subquery which utilizes the UNION ALL keywords to join 2 additional SELECT statements.

The first SELECT statement on Line 3 simply selects the number 1 (as a sort order), the rownum column and the empname column. The second SELECT statement on Line 8 selects the number 2, rownum and column_value keyword from the addr Nested Table column type. The query returns the data in the correct order as expected.

SELECT statement UNION ALL

To UPDATE the data in the Nested Table column addr, a special syntax using the THE keyword and Subquery is required. This example shows how to update the street address portion of the column.

UPDATE Nested Table

Using the SELECT syntax with column_value, a quick verification that the UPDATE was successful is displayed.

UPDATE Nested Table Verification

Add another employee to the database using the format for the Nested Table by calling the Type emp_addr.

Employee addition to Nested Table Type

A UNION ALL select statement with a couple of adjustments to query only Employee with empid 2 (Lines 7 and 12) will prove that the addition was successful.

INSERT Nested Table Verification 2