[MDEV-34319] Associative arrays: DECLARE TYPE .. TABLE OF .. INDEX BY in stored routines - Jira

XML

Word

Printable

Details

Type: New Feature
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Fix Version/s: 12.1.1
Component/s: PL/SQL, Stored routines
Labels:
- contribution

Sprint:
Q3/2025 Maintenance

Description

Let's add support for INDEX BY tables (associative arrays) in the DECLARE section of stored routines and anonymous blocks, with this syntax:

DECLARE

  TYPE type_name TABLE OF rec_type_name INDEX BY idx_type_name;

type_name should support explicit and anchored data types (e.g t1.col1%TYPE).
The INDEX BY clause should support at least integer and string data types.
rec_type_name should support both scalar types and records.

An associative array of scalar elements

Explicit type_name:

DECLARE

  TYPE salary IS TABLE OF NUMBER INDEX BY VARCHAR2(20);

  salary_list salary;

  name VARCHAR2(20);

BEGIN

  salary_list('Rajnisj') := 62000;

  salary_list('James') := 78000;

  name:= salary_list.FIRST;

  WHILE name IS NOT NULL

  LOOP

    dbms_output.put_line(name || ' ' || TO_CHAR(salary_list(name)));

    name:= salary_list.NEXT(name);

  END LOOP;

END;

Anchored type_name:

CREATE TABLE t1 (a INT);

DECLARE

  TYPE salary IS TABLE OF t1.a%TYPE INDEX BY VARCHAR2(20);

  salary_list salary;

  name VARCHAR2(20);

BEGIN

  salary_list('Rajnisj') := 62000;

  salary_list('James') := 78000;

  name:= salary_list.FIRST;

  WHILE name IS NOT NULL

  LOOP

    dbms_output.put_line(name || ' ' || TO_CHAR(salary_list(name)));

    name:= salary_list.NEXT(name);

  END LOOP;

END;

An associative array of records

Using explicit data types:

DECLARE

  TYPE person_t IS RECORD

    first_name VARCHAR(64),

    last_name VARCHAR(64)

);

  person person_t;

  TYPE table_of_peson_t IS TABLE OF person_t INDEX BY VARCHAR(20);

  person_by_nickname table_of_peson_t;

  nick VARCHAR(20);

BEGIN

  person_by_nickname('Monty') := person_t('Michael', 'Widenius');

  person_by_nickname('Serg') := person_t('Sergei ', 'Golubchik');

  nick:= person_by_nickname.FIRST;

  WHILE nick IS NOT NULL

  LOOP

    person:= person_by_nickname(nick);

    dbms_output.put_line(nick || ' ' || person.first_name || ' '|| person.last_name);

    nick:= person_by_nickname.NEXT(nick);

  END LOOP;

END;

Using anchored data types:

DROP TABLE persons;

CREATE TABLE persons (nickname VARCHAR(64), first_name VARCHAR(64), last_name VARCHAR(64));

INSERT INTO persons VALUES ('Serg','Sergei ', 'Golubchik');

INSERT INTO persons VALUES ('Monty','Michael', 'Widenius');

DECLARE

  TYPE table_of_person_t IS TABLE OF persons%ROWTYPE INDEX BY persons.nickname%TYPE;

  person_by_nickname table_of_person_t;

  nickname persons.nickname%TYPE;

  person persons%ROWTYPE;

BEGIN

  FOR rec IN (SELECT * FROM persons)

  LOOP

    person_by_nickname(rec.nickname):= rec;

  END LOOP;

  nickname:= person_by_nickname.FIRST;

  WHILE nickname IS NOT NULL

  LOOP

    person:= person_by_nickname(nickname);

    dbms_output.put_line(person.nickname || ' ' || person.first_name || ' '|| person.last_name);

    nickname:= person_by_nickname.NEXT(nickname);

  END LOOP;

END;

Attachments

Issue Links

blocks

MDEV-20030 sql_mode="oracle" does not support FOR ALL ... BULK COLLECT

Open

MDEV-34323 Oracle compatibility project 3

Open

MDEV-34560 Qualified package data types

Open

MDEV-34569 Oracle compatibility project 4

Open

MDEV-36767 Associative arrays: deferred features

Open

MDEV-37430 sql_mode=ORACLE: TYPE definitions in PACKAGE BODY

Closed

causes

MDEV-36850 SIGSEGV in Item_sp_variable::save_in_field | fill_record

Closed

MDEV-36954 Improve error handling when a function-method/procedure-method is missing in stored routines

Closed

MDEV-37408 Assertion `!thd->is_error()' failed in LEX::make_item_func_or_method_call upon SELECT or WITH

Closed

is blocked by

MDEV-34317 DECLARE TYPE type_name IS RECORD (..) with scalar members in stored routines

Closed

MDEV-36705 Preparations for associative arrays (MDEV-34319)

Closed

MDEV-36716 A case expression with ROW arguments in THEN crashes

Closed

is part of

MDEV-35973 Oracle Compatibility Project 1 for 2025

Stalled

relates to

MDEV-32670 VARRAY for scalar data types

Open

MDEV-34648 Support Composite Variable, Associative Arrays, in Procedure Parameter out

Open

MDEV-19149 System package SYS.DBMS_OUTPUT

Open

MDEV-35965 Oracle: Named and Mixed notations for stored routine call parameters

Open

MDEV-35980 Oracle: Nested tables for scalar elements

Open

(1 blocks, 3 causes, 3 is blocked by, 1 is part of, 5 relates to)

Activity

People

Assignee:: Alexander Barkov

Reporter:: Alexander Barkov

Votes:: 2 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Due:: 2025-05-01

Created:: 2024-06-06 09:42

Updated:: 2025-09-08 06:09

Resolved:: 2025-08-01 21:05

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.