how to loop accepting user input with pl/sql?

Question

I want to be able to insert a variable number of rows into a table based on user input? eg.

Please enter value, enter "done" when no more values: value 1
Please enter value, enter "done" when no more values: value 2
Please enter value, enter "done" when no more values: done

2 Rows inserted successfully.

I'm not sure how to store the rows temporarily and I'm not sure how to ask the user multiple times to insert data. Does pl/sql have arrays?

Thanks

Answer 1

Most likely, you can't, at least not without writing some sort of client application (not a SQL*Plus script). PL/SQL runs on the server and has nothing to accept user input. SQL*Plus runs on the client but it has no looping constructs. You can't mix the two, so you can't have a PL/SQL loop that happens repeatedly prompts the user for input and does an INSERT based on the values passed in.

Most commonly, people get around this by writing a small front-end script in your favorite scripting language that gathers the input and then issues appropriate INSERT statements.

If you really, really want to accomplish the task in SQL*Plus, it is probably possible, but quite painful. You would basically have to construct an infinite loop that you threw an error to escape from. For example

Define a script a.sql (I happen to store mine in c:\temp)

whenever sqlerror exit;
accept x_val number prompt "Enter a value for x or -1 to stop  ";
INSERT INTO x( col1 ) values( :x_val );
BEGIN
    IF( &x_val = -1 )
    THEN
      commit;
      RAISE_APPLICATION_ERROR( -20001, 'Done' );
    END IF;
END;
/

@c:\temp\a.sql

And then in SQL*Plus

SQL> variable x_val number;
SQL> crate table x( col1 number );
SQL> truncate table x;

Table truncated.

SQL> @c:\temp\a.sql
Enter a value for x or -1 to stop  3

1 row created.

old   2:        IF( &x_val = -1 )
new   2:        IF(          3 = -1 )

PL/SQL procedure successfully completed.

Enter a value for x or -1 to stop  4

1 row created.

old   2:        IF( &x_val = -1 )
new   2:        IF(          4 = -1 )

PL/SQL procedure successfully completed.

Enter a value for x or -1 to stop  5

1 row created.

old   2:        IF( &x_val = -1 )
new   2:        IF(          5 = -1 )

PL/SQL procedure successfully completed.

Enter a value for x or -1 to stop  -1

1 row created.

old   2:        IF( &x_val = -1 )
new   2:        IF(         -1 = -1 )
BEGIN
*
ERROR at line 1:
ORA-20001: Done
ORA-06512: at line 4

Horribly ugly, of course, but it "works" for some definition of "works"

Answer 2

I'd recommend Oracle Application Express. It's free, and dead easy to build simple apps very quickly.

Sure, it may be overkill for your extremely simple requirement here, but chances are someone will come back and say "I loved your little SQL*Plus script - could you just add a few more features to it" and before you know it you're building a skyscraper with a shack foundation.

Answer 3

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.

Answer 4

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!

Answer 5

Maybe you can have your user enter a comma seperated list of values?

Please enter values: 1,2,3,4,5

Then process the string in your plsql block.