how to loop accepting user input with pl/sql?

As others have said, PL/SQL alone is not suitable for this task, you need a UI on top to interact with the end user. However, if you have a real need to do this in SQL Plus, it is possible using the technique I described in this SO question.

You need to create 2 SQL Plus scripts:

1) A script to perform a single insert, here called script_insert.sql:

insert into t1 values ('&1.');
@main

2) A script to control the process, here called main.sql:

accept selection prompt "Please enter value, enter 'done' when no more values: "

set term off verify off

column script new_value v_script

select case '&selection.'
       when 'done' then ''
       else '@script_insert &selection.'
       end as script
from dual;

set term on

@&v_script.

Now in SQL Plus you can run it like this:

SQL> select * from t1;

no rows selected

SQL> @main
Please enter value, enter 'done' when no more values: 1
Please enter value, enter 'done' when no more values: 2
Please enter value, enter 'done' when no more values: 3
Please enter value, enter 'done' when no more values: done
SQL> select * from t1;

        N1
----------
         1
         2
         3

Let me reiterate that this demonstrates it can be done, I would not claim it to be a good way to implement the requirement - unless it is just an ad hoc tool to be used by a DBA or developer. I would never give an end user SQL Plus as a UI!


I think you're pounding a nail with a screwdriver.

You'd get far more flexibility using a Python script, a PHP page, a Perl script, a Java program, or any other environment that can access Oracle.