How To Loop Accepting User Input With Pl/sql?

March 26, 2024 Post a Comment

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

Solution 1:

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:

insertinto 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

selectcase'&selection.'when'done' then ''else'@script_insert &selection.'endas script
from dual;

set term on

@&v_script.

Now in SQL Plus you can run it like this:

SQL>select*from t1;

norows selected

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

        N1
----------123

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!

Solution 2:

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.

Solution 3:

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  ";
INSERTINTO x( col1 ) values( :x_val );
BEGIN
    IF( &x_val =-1 )
    THENcommit;
      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>truncatetable x;

Table truncated.

SQL>@c:\temp\a.sql
Enter a valuefor x or-1to stop  31row created.

old2:        IF( &x_val =-1 )
new2:        IF(          3=-1 )

PL/SQLprocedure successfully completed.

Enter a valuefor x or-1to stop  41row created.

old2:        IF( &x_val =-1 )
new2:        IF(          4=-1 )

PL/SQLprocedure successfully completed.

Enter a valuefor x or-1to stop  51row created.

old2:        IF( &x_val =-1 )
new2:        IF(          5=-1 )

PL/SQLprocedure successfully completed.

Enter a valuefor x or-1to stop  -11row created.

old2:        IF( &x_val =-1 )
new2:        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"

Solution 4:

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.

Solution 5:

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.

Lab Sql