Infrastructure at your Service

Daniel Westermann

When a function returns a collection: can you directly reference the index of the returned collection?

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:
Read More
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 …

One Comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure