Skip to content

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 …

Let’s work together

Ready to operate at
the next level?

Tell us about your challenge. We’ll bring the depth, the expertise, and the Swiss precision.

Open source Team

Open source Team

Posts published under the Open source Team account are collective contributions by the dbi services open infrastructure consultants. The practice covers open-source databases and platforms, PostgreSQL first of all, along with MySQL, MariaDB, and Linux, from architecture, replication, and high availability to performance tuning and operations under service level agreements, and contributes actively to the PostgreSQL community.View Author posts

721 posts since September 2011

Leave a Comment

Your email address will not be published. Required fields are marked *