How to execute procedure with input parametr (Varray OF INT)?

Question

    CREATE OR REPLACE TYPE list_of_int IS  
       VARRAY(10) OF INT; 

CREATE OR REPLACE PROCEDURE my_procedure(
    in_lista in list_of_int 
)
AS
...
    
    exec my_procedure( [1,2,3] );

How to execute procedure with input parametr VARRAY?

Answer 1

You can do something like this

    CREATE OR REPLACE TYPE list_of_int IS  
       VARRAY(10) OF INT; 
/

CREATE OR REPLACE PROCEDURE my_procedure(
    in_lista in list_of_int 
)
AS
begin
  for i in 1..in_lista.count
  loop
    dbms_output.put_line( in_lista(i) ); 
  end loop;
end;
/

exec my_procedure( list_of_int(1, 2, 3) );
/

Practically, though, I have yet to encounter an instance where it made sense to declare a varray type. It would almost certainly make more sense to declare a nested table type which doesn't limit the number of elements you can have in your collection (well, I think you're limited to 2^32 or whatever you can fit in PGA but if you're doing something seriously wrong if you get close to that). I can't think of a situation where I'd want to have code that intentionally dies if someone wants to pass an 11 element list.

    CREATE OR REPLACE TYPE int_t IS  
       table OF INT; 
/

CREATE OR REPLACE PROCEDURE my_procedure(
    in_lista in int_t 
)
AS
begin
  for i in 1..in_lista.count
  loop
    dbms_output.put_line( in_lista(i) ); 
  end loop;
end;
/

exec my_procedure( int_t(1, 2, 3) );
/