As I am still interested in PLSQL I browsed the documentation some days ago and learned something new which is about functions returning collections. Here is a simple test case:
Let’s create a simple package, specifying on collection and one function:
create or replace package pkg1 as type t1 is table of number index by pls_integer; function f1 ( a number ) return t1; end pkg1; / create or replace package body pkg1 as function f1 ( a number ) return t1 is lt1 t1; begin lt1(1) := 1; lt1(2) := 2; lt1(3) := 3; return lt1; end f1; begin null; end; /
If I call the f1 function which returns a collection: Can I directly reference the collection returned by index?
SQL> !cat exec_test_func.sql set serverout on declare lt1 pkg1.t1; ln number; begin ln := pkg1.f1(10)(3); dbms_output.put_line(ln); end; /
Yes, I really can. This might not seems very useful at the very first moment. But imagine there is a function which returns a collection indexed by varchar2. Then I could directly reference the collection by an index I might me interested in:
create or replace package pkg2 as type t1 is table of number index by varchar2(6); function f1 ( a number ) return t1; end pkg2; / create or replace package body pkg2 as function f1 ( a number ) return t1 is lt1 t1; begin lt1('FIRST') := 1; lt1('SECOND') := 2; lt1('THIRD') := 3; return lt1; end f1; begin null; end pkg2; /
Let’s directly reference an index of the returned collection:
SQL> !cat exec_test_func.sql set serverout on declare lt1 pkg2.t1; ln number; begin ln := pkg2.f1(10)('SECOND'); dbms_output.put_line(ln); end; /
Might be useful for some situations …