Extend Oracle Collections with member functions

Question

I am wondering wether it is possible to extend any type of collections (Associative Array,Nested Table, VArray) with custom functions.

I wish to be able to define custom functions in the same style I can do it for regular types using member functions. Using this, i would like to create a function which for example translates the content of my collection to a Character String by concating its items.

Answer 1

No, it is not possible. What you can do is to encapsulate collection into another type like this

create or replace type my_array as table of varchar2(10);
/
create or replace type my_array_type as object (
arr my_array, member function do_something return varchar2)
/
create or replace type body my_array_type is 
member function do_something return varchar2 is
l_temp varchar2(32767);
begin
for i in arr.first .. arr.last
loop
   l_temp:=l_temp||arr(i);
end loop;
return l_temp;
end;

end;

/

Now you can try your concatenation function out:

declare 
temp_array my_array:=my_array();
test_array my_array_type:=my_array_type(null);
result_string varchar2(32767);

begin
temp_array.extend(3);
temp_array(1):='a';
temp_array(2):='b';
temp_array(3):='c';

test_array:=my_array_type(temp_array);
result_string :=test_array.do_something;
dbms_output.put_line(result_string);

end;