Thursday, December 13, 2007

VARRAY examples in Oracle PL/SQL

Create type StrList as VARRAY(100) of VARCHAR2(2000);


function List(pItems IN VARCHAR2, pDelimit IN VARCHAR2) return StrList
IS
vItemsCount PLS_INTEGER;
vList StrList := StrList();
i PLS_INTEGER := 1;
vItems VARCHAR2(30000);
begin
vItems := pItems
WHILE i < vItemsCount LOOP
vList(i) := SUBSTR(vItems, 1 ,INSTR( vItems, pDelimit ) -1 );
vItems := SUBSTR( vItems, INSTR( vItems, pDelimit ) + 1);
END LOOP;
vList(i) := vItems;
return vlist;
END List;

No comments: