Oracle 12c – Alternative to CREATE TYPE with Read-Only Privileges

functionsoracle-12c

I have read-only access to a Oracle 12c system where there is zero chance I can get additional privileges or get the DBAs to change anything on my behalf (such as do CREATE TYPEs for me).

I'm using Oracle 12c feature WITH FUNCTION myfunc() .. SELECT .. to create functions as part of my SQL queries, which is working great.

Except, I would like to do something similar as shown in this, which is:
WITH FUNCTION myfunc(p_numbers t_numbers) .. SELECT ..
where t_numbers would normally be declared:
CREATE TYPE t_numbers AS TABLE OF NUMBER;
which I can't do.

Is there any alternative way to accomplish this kind of "inline" function without CREATE TYPE privileges? I want to be able to pass in an arbitrary quantity of numbers to an inline function as discussed in this.

Best Answer

There is built-in type called SYS.ODCINUMBERLIST which is a VARRAY(32767) of NUMBER, and EXECUTE privilege is granted on it to everyone (PUBLIC). You can use that for up to 32767 numbers, example:

with function f1 (p_numbers SYS.ODCINUMBERLIST) return number
as
begin
 return p_numbers.count;
end;
select f1(SYS.ODCINUMBERLIST(1,2,3,4,5,6,7)) from dual;
/

F1(SYS.ODCINUMBERLIST(1,2,3,4,5,6,7))
-------------------------------------
                                    7

In 19c, there is another built-in type called SYS.FI_NUMERICALS which is a TABLE of NUMBER, also usable by anyone in a similar way.

with function f1 (p_numbers SYS.FI_NUMERICALS) return number
as
begin
 return p_numbers.count;
end;
select f1(SYS.FI_NUMERICALS(1,2,3,4,5,6,7)) from dual;
/

F1(SYS.FI_NUMERICALS(1,2,3,4,5,6,7))
------------------------------------
                                   7

I have not found any documentation or reference for the latter.

Related Question