GBase 8s Associative Arrays: A Guide to Key-Value Collections

When running in Oracle‑compatible PL/SQL mode (SET SQLMODE='ORACLE'), GBase 8s provides associative arrays — collections whose size is unspecified and whose index can be a string or PLS_INTEGER. They can be defined in PL/SQL blocks or packages.

Syntax and Characteristics

• Syntax: TYPE population IS TABLE OF INT INDEX BY VARCHAR(10);
• Key traits:
  • Index types: VARCHAR2, VARCHAR, LONG, or PLS_INTEGER.
  • Elements are stored in sorted order (by NLS_SORT and NLS_COMP), not insertion order.
  • No explicit initialization is required.
• Unlike database tables: no disk space or network operations; DML is not allowed.
• Comparison rules: cannot compare to NULL; cannot compare two variables directly (v1 = v2), only individual elements (v1(1) = v2(1)).

Example 1: Basic Operations and Iteration

DECLARE
   TYPE type_1 IS TABLE OF NUMBER INDEX BY VARCHAR2(64);
   v1  type_1;
   i   VARCHAR2(64);
BEGIN
   v1('Smallville') := 2000;
   v1('Midland')    := 750000;
   v1('Megalopolis'):= 1000000;
   v1('Smallville') := 2001;    -- overwrites previous value

   i := v1.FIRST;
   WHILE i IS NOT NULL LOOP
       DBMS_OUTPUT.PUT_LINE('Population of ' || i || ' is ' || v1(i));
       i := v1.NEXT(i);
   END LOOP;
END;
/

Output (sorted by index):

Population of Megalopolis is 1000000
Population of Midland is 750000
Population of Smallville is 2001

Example 2: Associative Arrays as OUT Parameters in Packages

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

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

CREATE OR REPLACE PACKAGE BODY pkg1 AS
   PROCEDURE p1(c OUT t1) IS
   BEGIN
       c := t1(100, 200);
   END;

   PROCEDURE p2(c OUT t2) IS
   BEGIN
       c('var') := 'Jack';
   END;
END;
/

-- Call nested table procedure
DECLARE
   b pkg1.t1 := pkg1.t1(10,20);
BEGIN
   pkg1.p1(b);
   DBMS_OUTPUT.PUT_LINE(b(1));
END;
/
-- Output: 100

-- Call associative array procedure
DECLARE
   c pkg1.t2;
BEGIN
   pkg1.p2(c);
   DBMS_OUTPUT.PUT_LINE(c('var'));
END;
/
-- Output: Jack

Associative arrays are an efficient tool for temporary lookups, caching, or intermediate result sets inside procedural logic in a gbase database. Their flexible string‑based indexing and automatic sorting make them a natural fit for many PL/SQL patterns on GBASE's platform.