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

i looking below purpose of accepting array variables user itself, know below code wrong, sake of giving idea of requirement.

declare    type names_array varray(5) of varchar2(10);    names names_array; begin    in 1..5 loop      accept names(i);   end loop;    j in 1 .. 5 loop       dbms_output.put_line(names(j));    end loop;  end;  / 

it difficult implement using pl/sql.but using substitution variables in sql plus.

i create 2 sql scripts: first main.sql , 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' ''         else '@script_insert &colu'         end script dual; set term on @&v_script. [oracle@db input]$   [oracle@db input]$ cat script_insert.sql insert array_table values ('&1'); @main [oracle@db input]$  

next should create table other using array:

sql> create table array_table(colu varchar2(30));  table created.  sql> 

now, execute it:

sql> @main please enter value, enter 'done' when no more values:  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 * array_table;  colu ------------------------------ b hello hello world  sql> 

we got it,you should using table other array because pl/sql support it.and shouldn't using substitution variables in loop! finally, why don't implement c/python/java in program?if so, you'll more relaxed.

refer:how create menu in sqlplus or pl/sql