Friday, June 15, 2012

FORALL with arrays indexed by string


INDICES OF or VALUES OF clauses could not be used in FORALL with associative arrays indexed by strings. When it is necessary to load associative array into the table you should 'parse' it to another collection which use PLS_INTEGER as indexes.
CREATE TABLE experts (
  firstname VARCHAR2(30)
  ,lastname VARCHAR2(30)
);

create or replace
PROCEDURE load_experts
IS

  SUBTYPE experts_type IS experts%ROWTYPE;
  SUBTYPE name_type IS experts.firstname%TYPE;

  TYPE aat_namename_type IS TABLE OF name_type
                          INDEX BY name_type;
  TYPE nt_rec_type IS TABLE OF experts_type;
                        
  src_array   aat_namename_type;
  ndx_table   nt_rec_type := nt_rec_type();
  loc         name_type;
  item        experts_type;

BEGIN

  src_array('Steven') := 'Feuerstein';
  src_array('Tom')    := 'Kyte';
  src_array('Larry')  := 'Ellison';
  
  loc := src_array.FIRST;
  WHILE loc IS NOT NULL LOOP
    item.firstname  := loc;
    item.lastname   := src_array(loc);
    ndx_table.EXTEND;
    ndx_table(ndx_table.LAST) := item;
    loc := src_array.NEXT(loc);
  END LOOP;
  
  FORALL k IN INDICES OF ndx_table
    INSERT INTO experts VALUES ndx_table(k);
    
END;

No comments:

Post a Comment