CODEWITHSUNDEEP
oracle-databaseplsqluser-defined-typesvarray
Raj kumar Mishra
Raj kumar Mishra

Reputation: 21

dynamic allocation of values in Varray in PLSQL

I want to dynamically allocate the values of a VARRAY. So, for example, I declared a varray of size 5. In that varray I want to dynamically insert the 5 values from user input. How to do this ?

Upvotes: 0

Views: 784

Answers (1)

APC
APC

Reputation: 146239

PL/SQL is a language for autonomous code. It is not blessed with a great capability for user interaction. It does not accept user input so you need to handle user input on the client side and submit values to your PL/SQL program. In your case this means taking user input, assembling it in a locally declared variable of your varray type and passing that variable as a parameter to the stored procedure.

You say you are running with Oracle SQL Developer, so we can use SQL*Plus substitution variables (using the ampersand notation) to accept user input.

So. Given a VARRAY like this... 

create or replace type params_va as varray(5) of varchar2(128);
/

...and a toy procedure like this...

create or replace procedure p1 (p_args in params_va)
is
begin
  for i in 1..5 loop
    dbms_output.put_line(p_args(i));
  end loop;
end;
/

Note that the procedure takes one parameter, of the VARRAY type.

Now we can run the following script in Oracle SQL Developer. It will pop-up five dialog boxes, one for each element in the VARRAY (strictly speaking, one for each substitution variable):

declare
  l_args params_va := new params_va();
begin
  l_args.extend(5);

  l_args(1) := '&arg1';
  l_args(2) := '&arg2';
  l_args(3) := '&arg3';
  l_args(4) := '&arg4';
  l_args(5) := '&arg5';

  p1(l_args);

end;
/

Upvotes: 1

Related Questions