Oracle: Function returns result as an array

To let your function return a set of data as an array, you need to firstly create a type that is a table. Then use that data type to hold your result.

A. Create the array type

          CREATE OR REPLACE type FuncArray as table of varchar2(15);

          The 15 in the varchar2(15) should be the length of the longest item you are going to put in this array.

B. Create the function

CREATE OR REPLACE function data2array (myData IN Integer) return FuncArray
AS
myResult FuncArray := FuncArray();
begin
if myData = 0 then myResult .extend; myResult(myResult.count) := 'Not Started';
end if;
if bitand(myData, 1) = 1 then myResult .extend; myResult(myResult.count) := 'First';
end if;
if bitand(myData, 2) = 2 then myResult .extend; myResult(myResult.count) := 'Middle';
end if;
if bitand(myData, 4) = 4 then myResult .extend; myResult(myResult.count) := 'Last';
end if;
return myResult;
end;

C. Test

SQL> select data2array(0) as result from dual;

result

-----------------------------------

FuncArray ('Not Started')

SQL> select data2array(7) as result from dual;

result

-----------------------------------

FuncArray ('First', 'Middle', 'Last')

 -----------------------------------------------------------------------------------------------------------------

Watch the blessing and loving online channel: SupremeMasterTV live

If you have ever asked yourself these questions, this is the book for you. What is the meaning of life? Why do people suffer? What is in control of my life? Why is life the way it is? How can I stop suffering and be happy? How can I have a successful life? How can I have a life I like to have? How can I be the person I like to be? How can I be wiser and smarter? How can I have good and harmonious relations with others? Why do people meditate to achieve enlightenment? What is the true meaning of spiritual practice? Why all beings are one? Read the book for free here.