Four Oracle Data Constructs with a description of Nested Table

Nested tables are still supported and can be used in Oracle Cloud databases, including 19c and newer versions.
While they might not be as trendy as some newer features, they still serve a valuable purpose in certain situations. Here's why:

• Storing multi-valued attributes: Nested tables excel at storing multiple values within a single row of a table. For instance, consider an "Orders" table where each order can have multiple items. Instead of creating a separate table for order items, you could use a nested table to store all items within the "Orders" table itself.
• Object-relational model: Nested tables are a key component of Oracle's object-relational model, allowing you to create complex data structures and relationships within the database.
• Performance benefits: In specific scenarios, nested tables can offer performance advantages. For example, retrieving all related data in a single query can be faster than joining multiple tables.

However, it's important to note that:

• Complexity: Nested tables can add complexity to your database design and queries.
• Alternatives: In many cases, alternative approaches like separate tables with foreign keys or JSON data types might be more suitable.

In summary: While not as widely used as they once were, nested tables remain a valid option in Oracle Cloud databases. Carefully consider your specific needs and weigh the pros and cons before using them.
The following constructs are still available in Oracle 19c :

1. ADT (Abstract Data Type): Oracle's ADTs, or object types, are still supported in Oracle 19c. ADTs are used to define user-defined types in the database, encapsulating both data and methods (procedures and functions).
2. DEREF: The `DEREF` operator is still available in Oracle 19c. It is used to dereference a REF (reference) to an object type, allowing you to access the object it points to.
3. OID (Object Identifier): OIDs are still used in Oracle 19c to uniquely identify instances of object types in object-relational databases. These remain crucial for distinguishing between different object instances.
4. CREATE TYPE: The `CREATE TYPE` statement is still supported in Oracle 19c for defining both object types (ADT) and collection types like VARRAYs and nested tables.

These features are part of Oracle's object-relational model and continue to be supported in version 19c.

In the database, a nested table is a column type that stores an unspecified number of rows in no particular order. When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1. Using these indexes, you can access the individual rows of the nested table variable. The syntax is variable_name(index). The indexes and row order of a nested table might not remain stable as you store and retrieve the nested table from the database.
The amount of memory that a nested table variable occupies can increase or decrease dynamically, as you add or delete elements. An uninitialized nested table variable is a null collection. You must initialize it, either by making it empty or by assigning a non-NULL value to it.
Example 3-1 defines a local nested table type, declares a variable of that type (initializing it with a constructor), and defines a procedure that prints the nested table. The example invokes the procedure three times: After initializing the variable, after changing the value of one element, and after using a constructor to the change the values of all elements. After the second constructor invocation, the nested table has only two elements.
Referencing element 3 would raise error ORA-06533.

Example 3-1 Nested Table of Local Type

DECLARE
TYPE Roster IS TABLE OF VARCHAR2(15); -- nested table type
-- nested table variable initialized with constructor:
names Roster := Roster('D Caruso', 'J Hamil', 'D Piro', 'R Singh');
PROCEDURE print_names (heading VARCHAR2) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(heading);
FOR i IN names.FIRST .. names.LAST LOOP -- For first to last element
DBMS_OUTPUT.PUT_LINE(names(i));
END LOOP;
DBMS_OUTPUT.PUT_LINE('---');
END;
BEGIN
print_names('Initial Values:');
names(3) := 'P Perez'; -- Change value of one element
print_names('Current Values:');
names := Roster('A Jansen', 'B Gupta'); -- Change entire table
print_names('Current Values:');
END;
/

Output displayed below

Result:
Initial Values:
D Caruso
J Hamil
D Piro
R Singh
---
Current Values:
D Caruso
J Hamil
P Perez
R Singh
---
Current Values:
A Jansen
B Gupta

The Oracle error ORA-06533: Subscript beyond count occurs when a PL/SQL program tries to reference an element of a collection (such as a VARRAY or nested table) using an index (or subscript) that exceeds the number of elements in the collection.
Causes of ORA-06533:

1. Invalid Index/Position: Attempting to access an element at an index greater than the number of elements present in the collection.
2. Uninitialized Collection: The collection may not have been initialized, or its size may not have been explicitly defined.

Example Scenario: If you have a collection with three elements, but try to reference the fourth element, Oracle will raise the ORA-06533 error:

DECLARE
 my_array  varray(3) of NUMBER := varray(3)(10, 20, 30);
BEGIN
 -- This will cause ORA-06533 because index 4 is beyond the array's count of 3
 DBMS_OUTPUT.put_line(my_array(4));
END;

How to Fix:

• Check the index: Ensure that the index you are using to reference the collection is within its valid bounds.
• Check collection size: Ensure that the collection has been properly initialized and has the number of elements you expect before accessing it.
• Use built-in methods: Utilize methods like `COUNT` to safely access the size of the collection before accessing elements:

IF my_array.COUNT >= 4 THEN
   DBMS_OUTPUT.put_line(my_array(4));
END IF;

By ensuring that the index is within the valid range, you can avoid triggering the ORA-06533 error.