Anydata and Collections

Read Time:2 Minute, 36 Second

Steven Feuerstein has written a PLSQL-utility str2list in which he uses dynamic sql to fill collections of different types.
While I was playing with the “new” Oracle types ANYTYPE, ANYDATA and ANYDATSET I found that the ANYDATA type can be used to do something simular.

So I have written a function which accepts a string and a delimiter, and returns the string split in several parts in an ANYDATA which can be deposited in a collection of different types.

create or replace function str2list
  ( str   in varchar2
  , delim in varchar2 := ';'
  )
return anydata
is
  v_endloc   pls_integer;
  v_startloc pls_integer;
  ad  anydata;
  mt  anytype;
begin
  if str is null
  then
    return null;
  end if;
  anytype.BeginCreate( DBMS_TYPES.TYPECODE_TABLE, mt );
  mt.setinfo( null, null, null, null, null, null, DBMS_TYPES.TYPECODE_VARCHAR2, 0 );
  mt.EndCreate;
  anydata.begincreate( mt, ad );
--
  v_startloc := 1;
  loop
    v_endloc := instr( str, delim, v_startloc );
    if v_endloc > 0
    then
      ad.setvarchar2( substr( str, v_startloc, v_endloc - v_startloc ) );
      v_startloc := v_endloc + 1;
    else
      ad.setvarchar2( substr( str, v_startloc ), true );
      exit;
    end if;
  end loop;
  ad.endcreate;
  return ad;
end;

And to show the use of this function:

create or replace type anton1 is table of varchar2(20);

create or replace type anton4 is varray(5) of varchar2(20);

declare
  type anton2 is table of varchar2(20) index by binary_integer;
  type anton3 is table of varchar2(20);
  a1 anton1;
  a2 anton2;
  a3 anton3;
  a4 anton4;
  r pls_integer;
  ind pls_integer;
begin
  r := str2list('amis;is;great').getCollection( a1 );
  ind := a1.first;
  while ind is not null
  loop
    dbms_output.put_line( 'nested table type: ' || ind || ' = ' || a1(ind) );
    ind := a1.next( ind );
  end loop;
--
  r := str2list('amis;is;great').getCollection( a2 );
  ind := a2.first;
  while ind is not null
  loop
    dbms_output.put_line( 'index by table: ' || ind || ' = ' || a2(ind) );
    ind := a2.next( ind );
  end loop;
--
  r := str2list('amis;is;great').getCollection( a3 );
  ind := a3.first;
  while ind is not null
  loop
    dbms_output.put_line( 'nested table: ' || ind || ' = ' || a3(ind) );
    ind := a3.next( ind );
  end loop;
--
  r := str2list('amis;is;great').getCollection( a4 );
  ind := a4.first;
  while ind is not null
  loop
    dbms_output.put_line( 'varray: ' || ind || ' = ' || a4(ind) );
    ind := a4.next( ind );
  end loop;
--
end;

nested table type: 1 = amis
nested table type: 2 = is
nested table type: 3 = great
index by table: 0 = amis
index by table: 1 = is
index by table: 2 = great
nested table: 1 = amis
nested table: 2 = is
nested table: 3 = great
varray: 1 = amis
varray: 2 = is
varray: 3 = great

PL/SQL procedure successfully completed.

This demonstrates that it is possible to cast the ANYDATA to all different collections type, so I wonder why it is not possible to do the inverse. Casting the PLSQL collection types (the index-by table and the PLSQL nested table) to an ANYDATA raises
ORA-22370: incorrect usage of method ORA-22370: incorrect usage of method ConvertCollection
ORA-06512: at “SYS.ANYDATA”, line 63
ORA-06512: at line 70

Anton