To pass collection to Stored procedure call as input without using the variable

Question

I have a requirement where we have to call a oracle Stored Procedure which has collection type as input parameter. So my stored procedure is like:

Create or replace package test
As 

Type t is table of varchar(400) index by binary_integer.

Procedure testprc(p_client_id in number,t_no in number,t1 in t,t2 in t);

End;

Create or replace package body test
As

Procedure testprc(p_client_id in number,t_no in number,t1 in t, t2 in t)
is

Begin

for i in 1 ..tno loop

Insert into client
(Client_id,Client_phone,client_email) values (p_client_id,t1(i),t2(i));

End loop;

End;

End;

Now in the call to this stored procedure testprc.test I don't want to create a variable of type t and pass it to the stored procedure instead I want to directly pass the list of values to the SP. Something like this

Exec testprc(13,1,{1=>'22737371'},{1=>'test@abc.com'}).

Is there any way I can achieve this.

Answer 1

If you are on 18c or higher, you can use a qualified expression to avoid having to create a temporary variable to hold associative arrays. For example:

SQL> exec test.testprc(13,1,test.t(1=>'22737371'),test.t(1=>'test@abc.com'))

PL/SQL procedure successfully completed.