GBase 8s Nested Tables: A Practical Guide to Collection Types

GBase 8s supports Oracle‑compatible PL/SQL when the environment variable SQLMODE is set to 'ORACLE'. Among the three collection types available, nested tables are unique: their element count is unspecified, their index type is integer, and they can be defined locally inside a PL/SQL block or globally as a database object.

Syntax

• Local definition: TYPE type_name IS TABLE OF datatype;
• Global definition: CREATE TYPE type_name IS TABLE OF datatype;

Characteristics

1. Must be initialized before use — an uninitialized nested table is NULL.
2. Elements are stored in no particular order.
3. Indexes start at 1.
4. Memory can grow and shrink dynamically as elements are added or removed.

Example: Nested Table vs Associative Array as OUT Parameters

The following package defines two procedures — one using a nested table, the other an associative array indexed by VARCHAR(5) — to illustrate how they are declared, populated, and called.

-- Base table
DROP TABLE IF EXISTS tab1;
CREATE TABLE tab1(id INT, name VARCHAR(10));

-- Package specification
CREATE OR REPLACE PACKAGE pkg1 AS
   TYPE t1 IS TABLE OF tab1%ROWTYPE;                     -- nested table
   TYPE t2 IS TABLE OF tab1%ROWTYPE INDEX BY VARCHAR(5); -- associative array
   PROCEDURE p1(c OUT t1);
   PROCEDURE p2(c OUT t2);
END;
/

-- Package body
CREATE OR REPLACE PACKAGE BODY pkg1 AS
   PROCEDURE p1(c OUT t1) IS
       a tab1%ROWTYPE;
   BEGIN
       a.id := 10;
       a.name := 'Jerry';
       c := t1(a);            -- explicit initialization
   END;

   PROCEDURE p2(c OUT t2) IS
       a tab1%ROWTYPE;
   BEGIN
       a.id := 20;
       a.name := 'Tom';
       c('var') := a;         -- direct assignment in associative array
   END;
END;
/

-- Call the nested‑table procedure
DECLARE
   a tab1%ROWTYPE;
   b pkg1.t1 := pkg1.t1(a);
BEGIN
   pkg1.p1(b);
   DBMS_OUTPUT.PUT_LINE(b(1).id);
   DBMS_OUTPUT.PUT_LINE(b(1).name);
END;
/

-- Call the associative‑array procedure
DECLARE
   c pkg1.t2;
BEGIN
   pkg1.p2(c);
   DBMS_OUTPUT.PUT_LINE(c('var').id);
   DBMS_OUTPUT.PUT_LINE(c('var').name);
END;
/

Nested tables are a great fit for passing bulk data across procedures, and when defined with CREATE TYPE they can be reused across packages and stored as columns — a powerful feature for procedural logic in a gbase database.