Order To The Chaos – with PL/SQL Collections

Published by Sam on

When it comes to storing several pieces of data in a database, we first think about tables, of course. But sometimes we only need these pieces of data for a short amount of time to process them inside of PL/SQL. In that case, it is often a much better approach to use a PL/SQL collection.

Oracle offers 3 different types of collections, all with slightly different capabilities and limitations. Since I still struggle to memorize the differences (and discovered a mistake in my own mental model about collections this week), I thought it would be a good idea to showcase the different collections and their differences as a condensed code example.

You can run and view this Code-Example in Oracle LiveSQL

Associative Array

declare
  type t_map is table of varchar2(100) index by varchar2(100);
  character_species t_map;

  -- Procedure to save a bit of screen width ;)
  procedure log(i_msg varchar2) as
  begin
    dbms_output.put_line(i_msg);
  end;
begin
  log('Associative Arrays');
  log('------------------');

  if character_species is null then
    log('Uninitialized collection is NULL');
  else
    log('Uninitialized collection is not NULL');
  end if;

  /****************
  * Construction
  ****************/
  character_species('Chewbacca') := 'Wookie';
  character_species('Darth Vader') := 'Human';

  -- Since 18c: Possible with Qualified Expressions
  character_species := t_map(
    'Chewbacca'=>'Wookie',
    'Darth Vader'=>'Human'
    );

  /****************
  * Access
  ****************/
  log('Access via index: '||character_species('Chewbacca'));

  /****************
  * Methods
  ****************/
  -- EXISTS:
  if character_species.exists('Chewbacca') then
    log('Exists: true');
  else
    log('Exists: false');
  end if;

  -- EXTEND: Not possible
  log('Extend: Not possible');

  -- COUNT:
  log('Count: '||character_species.count);

  -- LIMIT: Not possible
  log('Limit: Not possible');

  -- FIRST:
  log('First: '||character_species.first);

  -- LAST:
  log('Last: '||character_species.last);

  -- NEXT:
  log('Next: '||character_species.next('Chewbacca'));

  -- PRIOR:
  log('Prior: '||character_species.prior('Darth Vader'));

  -- TRIM: Not possible
  log('Trim: Not possible');

  -- DELETE:
  character_species.delete('Chewbacca');
  log('Delete: Remaining elements: '||character_species.count);

  log('Caution! Associative arrays cannot be used in SQL');
end;
/
Associative Arrays
------------------
Uninitialized collection is not NULL
Access via index: Wookie
Exists: true
Extend: Not possible
Count: 2
Limit: Not possible
First: Chewbacca
Last: Darth Vader
Next: Darth Vader
Prior: Chewbacca
Trim: Not possible
Delete: Remaining elements: 1
Caution! Associative arrays cannot be used in SQL

Varying Array (Varray)

asdf

declare
  type t_list is varray(100) of varchar2(100);
  characters t_list;

  -- Procedure to save a bit of screen width ;)
  procedure log(i_msg varchar2) as
  begin
    dbms_output.put_line(i_msg);
  end;
begin
  log('Varying Array (Varray)');
  log('------------------');

  if characters is null then
    log('Uninitialized collection is NULL');
  else
    log('Uninitialized collection is not NULL');
  end if;

  /****************
  * Construction
  ****************/
  characters := t_list(); -- Empty but initialized

  characters := t_list('Chewbacca', 'Darth Vader');

  /****************
  * Access
  ****************/
  log('Access via num-index: '||characters(1));

  /****************
  * Methods
  ****************/
  -- EXISTS:
  if characters.exists(1) then
    log('Exists: true');
  else
    log('Exists: false');
  end if;

  -- EXTEND:
  characters.extend;
  characters(3) := 'Leia Organa';
  log('Extend: New element count: '||characters.count);

  -- COUNT:
  log('Count: '||characters.count);

  -- LIMIT:
  log('Limit: '||characters.limit);

  -- FIRST:
  log('First: '||characters.first);

  -- LAST:
  log('Last: '||characters.last);

  -- NEXT:
  log('Next: '||characters.next(1));

  -- PRIOR:
  log('Prior: '||characters.prior(2));

  -- TRIM:
  characters.trim;
  log('Trim: Remaining elements: '||characters.count);

  -- DELETE: Only without parameter, empties the collection
  characters.delete;
  log('Delete: Remaining elements: '||characters.count);
end;
/
Varying Array (Varray)
------------------
Uninitialized collection is NULL
Access via num-index: Chewbacca
Exists: true
Extend: New element count: 3
Count: 3
Limit: 100
First: 1
Last: 3
Next: 2
Prior: 1
Trim: Remaining elements: 2
Delete: Remaining elements: 0

Nested Table

declare
  type t_set is table of varchar2(100);
  characters t_set;

  -- Procedure to save a bit of screen width ;)
  procedure log(i_msg varchar2) as
  begin
    dbms_output.put_line(i_msg);
  end;
begin
  log('Nested Tables');
  log('------------------');

  if characters is null then
    log('Uninitialized collection is NULL');
  else
    log('Uninitialized collection is not NULL');
  end if;

  /****************
  * Construction
  ****************/
  characters := t_set(); -- Empty but initialized

  characters := t_set('Chewbacca', 'Darth Vader');

  /****************
  * Access
  ****************/
  log('Access via num-index: '||characters(1));

  /****************
  * Methods
  ****************/
  -- EXISTS:
  if characters.exists(1) then
    log('Exists: true');
  else
    log('Exists: false');
  end if;

  -- EXTEND:
  characters.extend;
  characters(3) := 'Leia Organa';
  log('Extend: New element count: '||characters.count);

  -- COUNT:
  log('Count: '||characters.count);

  -- LIMIT: Works but returns NULL
  log('Limit: '||characters.limit);

  -- FIRST:
  log('First: '||characters.first);

  -- LAST:
  log('Last: '||characters.last);

  -- NEXT:
  log('Next: '||characters.next(1));

  -- PRIOR:
  log('Prior: '||characters.prior(2));

  -- TRIM:
  characters.trim;
  log('Trim: Remaining elements: '||characters.count);

  -- DELETE:
  characters.delete(1);
  log('Delete: Remaining elements: '||characters.count);
end;
/
Nested Tables
------------------
Uninitialized collection is NULL
Access via num-index: Chewbacca
Exists: true
Extend: New element count: 3
Count: 3
Limit: 
First: 1
Last: 3
Next: 2
Prior: 1
Trim: Remaining elements: 2
Delete: Remaining elements: 1

Collections in SQL

Only 2 of the 3 collection types can be used in an SQL context: Varrays and Nested Tables.

That means they can be used as a table source or as a column type, but only if the collection type is created as a global type (we defined them as package-level type in the previous example):

create type t_set_of_strings is table of varchar2(100);

select *
from t_set_of_strings('Chewbacca', 'Darth Vader');

Handy Existing Collection-Types (SYS)

Sometimes you might want to use a collection in SQL but don’t want to create your own global type for it. In that case, it might be handy to use one of the existing, publicly available SYS collection types.

select *
from sys.odcivarchar2list('Chewbacca', 'Darth Vader');

Varying Array (Varray)

select
  -- Output a combined description
    type_name||' '||elem_type_name||
    case when elem_type_name = 'VARCHAR2' then '('||length||')'
    else ''
    end||
    ', Max '||upper_bound||' elements'
  as description,
  type_name, elem_type_name, length, upper_bound
from all_coll_types
where owner = 'SYS'
  and coll_type = 'VARYING ARRAY'
  and type_name not like '%$%'
  and elem_type_name in ('VARCHAR2', 'NUMBER', 'INTEGER', 'DATE', 'CLOB', 'BFILE', 'BINARY_FLOAT', 'BINARY_DOUBLE', 'ANYDATA', 'XMLTYPE')
order by type_name;
  • AWRRPT_CLB_ARY CLOB, Max 30 elements
  • AWRRPT_NUM_ARY NUMBER, Max 30 elements
  • AWRRPT_VCH_ARY VARCHAR2(80), Max 30 elements
  • DBMSOUTPUT_LINESARRAY VARCHAR2(32767), Max 2147483647 elements
  • DS_VARRAY_4_CLOB VARCHAR2(4000), Max 2000000000 elements
  • FLASHBACKTBLIST VARCHAR2(261), Max 100 elements
  • GRANT_PATH VARCHAR2(128), Max 150 elements
  • JSON_KEY_LIST VARCHAR2(4000), Max 32767 elements
  • ODCIBFILELIST BFILE, Max 32767 elements
  • ODCICOLVALLIST ANYDATA, Max 32 elements
  • ODCIDATELIST DATE, Max 32767 elements
  • ODCIGRANULELIST NUMBER, Max 65535 elements
  • ODCINUMBERLIST NUMBER, Max 32767 elements
  • ODCINUMBERLIST2 NUMBER, Max 32767 elements
  • ODCIRIDLIST VARCHAR2(5072), Max 32767 elements
  • ODCIVARCHAR2LIST VARCHAR2(4000), Max 32767 elements
  • ORATSDP_SD_SENSITIVE_INFO_OTV VARCHAR2(4000), Max 2147483647 elements
  • PACKAGE_ARRAY NUMBER, Max 1024 elements
  • ROLE_ARRAY NUMBER, Max 150 elements
  • ROLE_ID_LIST NUMBER, Max 10 elements
  • ROLENAME_ARRAY VARCHAR2(128), Max 150 elements
  • ROLE_NAME_LIST VARCHAR2(128), Max 10 elements
  • SQL_BINDS ANYDATA, Max 2000 elements
  • SQL_OBJECTS NUMBER, Max 2000 elements
  • SQLPROF_ATTR VARCHAR2(500), Max 2000 elements
  • TABLESPACE_LIST NUMBER, Max 64000 elements
  • TXNAME_ARRAY VARCHAR2(256), Max 100 elements
  • UTL_NLA_ARRAY_DBL BINARY_DOUBLE, Max 1000000 elements
  • UTL_NLA_ARRAY_FLT BINARY_FLOAT, Max 1000000 elements
  • UTL_NLA_ARRAY_INT INTEGER, Max 1000000 elements
  • XMLSEQUENCETYPE XMLTYPE, Max 2147483647 elements

Nested Table

select
  -- Output a combined description
    type_name||' '||elem_type_name||
    case when elem_type_name = 'VARCHAR2' then '('||length||')'
    else ''
    end
  as description,
  type_name, elem_type_name, length
from all_coll_types
    where owner = 'SYS'
      and coll_type = 'TABLE'
      and type_name not like '%$%'
      and elem_type_name in ('VARCHAR2', 'NUMBER', 'INTEGER', 'DATE', 'CLOB', 'BFILE', 'BINARY_FLOAT', 'BINARY_DOUBLE', 'ANYDATA', 'XMLTYPE')
    order by type_name;
  • AWRRPT_INSTANCE_LIST_TYPE NUMBER
  • AWRRPT_NUMBER_LIST_TYPE NUMBER
  • AWRRPT_VARCHAR256_LIST_TYPE VARCHAR2(256)
  • DBMS_DEBUG_VC2COLL VARCHAR2(1000)
  • FI_CATEGORICALS VARCHAR2(4000)
  • FI_NUMERICALS NUMBER
  • ORA_MINING_NUMBER_NT NUMBER
  • ORA_MINING_VARCHAR2_NT VARCHAR2(4000)
  • PRVT_AWR_EVTLIST VARCHAR2(64)
  • PRVT_AWR_NUMTAB NUMBER
  • PRVT_AWRV_VARCHAR64TAB VARCHAR2(64)
  • PRVT_AWR_XMLTAB XMLTYPE
  • SODA_INDEX_LIST_T VARCHAR2(32767)
  • SODA_KEY_LIST_T VARCHAR2(255)
Categories: #100CodeExamplesPL/SQLSQL
Tags:

0 Comments

Leave a Reply

Avatar placeholder

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts

#100CodeExamples

Deeply Nested XML and JSON with SQL

I already wrote about how much I like the XML generating capabilities of modern SQL, but most examples come only with one or two levels of depth. What if you want to create deeply nested XML and JSON directly from SQL?

#100CodeExamples

Get a list of Quarters by Year with Recursive CTEs (WITH-clause)

Recursive CTEs (WITH-clause) is one addition to the SQL standard that makes the language incredibly powerful. Besides other things it can be used to generate data rows "from thin air" - for example a list of quarters by year. SQL is your friend!

#100CodeExamples

XML-Generation in SQL with Namespaces

One of my favorite features of (Oracle-)SQL is how easy it is to turn relational data into other data formats like JSON or XML. But what if you need to add a namespace to some of the nodes in your generated XML? I struggled with that until I made a realization about the nature of the xlmns part in XML.