Can I accept values in a VARRAY in PL/SQL directly from the user?

Question

I am looking for something like below for the purpose of accepting array variables from the user itself, I know the below code is wrong, but it is just for the sake of giving the idea of my requirement.

DECLARE 
  type names_array IS VARRAY(5) OF VARCHAR2(10); 
  names names_array;
BEGIN 
  FOR i in 1..5 LOOP
     accept names(i);
  END LOOP;

  FOR j in 1 .. 5 LOOP 
     dbms_output.put_line(names(j)); 
  END LOOP; 
END; 
/

Answer 1

It will be difficult to implement using PL/SQL.But we could using substitution variables in SQL Plus.

I create two sql scripts: the first is main.sql and another is script_insert.sql:

[oracle@db input]$ cat main.sql
accept colu prompt "Please enter value, enter 'done' when no more values: "
set term off verify off
column script new_value v_script
select case '&colu'
        when 'done' then ''
        else '@script_insert &colu'
        end as script
from dual;
set term on
@&v_script.
[oracle@db input]$ 

[oracle@db input]$ cat script_insert.sql
insert into array_table values ('&1');
@main
[oracle@db input]$ 

Next we should create a table other than using an array:

SQL> create table array_table(colu varchar2(30));

Table created.

SQL>

Now, we could execute it:

SQL> @main
Please enter value, enter 'done' when no more values: A

1 row created.

Please enter value, enter 'done' when no more values: B

1 row created.

Please enter value, enter 'done' when no more values: Hello

1 row created.

Please enter value, enter 'done' when no more values: "Hello World"

1 row created.

Please enter value, enter 'done' when no more values: done
SQL> select * from array_table;

COLU
------------------------------
A
B
Hello
Hello World

SQL>

We got it,you should using a table other than array because only PL/SQL support it.And you shouldn't using substitution variables in loop! The finally, why don't you implement this by C/Python/Java in your program?If so, you'll be more relaxed.

Refer:How to create a menu in SQLPlus or PL/SQL