This question may sound a bit absurd, but is it possible to get multiple array from a stored function? For example:
CREATE OR REPLACE TYPE STRINGLISTT1 as table of varchar2(4000);
CREATE OR REPLACE TYPE STRINGLISTT2 as table of varchar2(4000);
create or replace function in_list(p_string in varchar2,p_delimiter in varchar2)
-- Returning two different arrays
return StringListT, StringListT22 is
No its not possible.
first, there is no need to create two types that are actually the same. both of the types are tables of varchar2(4000). so it is enough to create one type and define two variables from it.
to deal with a function returning more than one type/object, you can create a container object, fill the object and return it.
so create the type:
CREATE OR REPLACE TYPE STRING_LIST as table of varchar2(4000);
then create an object type that has two members, each is a list from the type above
create or replace type TWO_STRING_LISTS as object
(first_list STRING_LIST,
second_list STRING_LIST
);
now in your function, you prepare the lists, create an object and put the lists in it, and return it:
create or replace function in_list(p_string in varchar2,p_delimiter in varchar2)
return TWO_STRING_LISTS is
list1 STRING_LIST;
list2 STRING_LIST;
begin
//fill the lists
return TWO_STRING_LISTS(list1,list2);
end;
when you get the object back from the in_list function, you can then access the lists.
declare
TWO_STRING_LISTS lists;
begin
//some code
........
lists := in_list(........);
//reference first list
lists.first_list .....
end;
outparameters.