Anydata and Collections

Anton Scheffer

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

Next Post

Example of XSL-transforming in ADF applications - downloading Excel (CSV) files from ADF Web Applications

Facebook0TwitterLinkedinOne of our recent projects based on Oracle ADF (JSP, Struts, ADF BC and ADF Binding Framework on top of an Oracle Database) had as one of its requirements the ability to download certain sets of data in Excel-file format (basically a Comma Separated Values file). In this post I […]