Interactive PL/SQL script

Question

How do I write a PL/SQL script that acceptsthe user input like in the following format?

1 : Output Customers

2 : Output Employees

3 : Output Transactions

Enter your option (1/2/3) :

Thanks, Pradeep

are you sure this is possible? pl/sql is running on the db server, it has no interative statements. do you use oracle forms? — nabuchodonossor
– nabuchodonossor, Commented Jul 14, 2011 at 12:09
often times people mistakenly use "PL/SQL" to mean "SQL for Oracle" -- is that what you mean here? As the other commenter stated, true PL/SQL (a procedural language for Oracle that runs in blocks on the database server without interaction) has no capability to prompt for user input. What I assume you mean is a SQL*Plus script, for which see Sodved's answer. — Dave Costa
– Dave Costa, Commented Jul 14, 2011 at 12:38

Dave Costa · Accepted Answer · 2011-07-14 12:36:13Z

6

If you're running in sqlplus something like this

PROMPT 1 : Output Customers
PROMPT 2 : Output Employees
PROMPT 3 : Output Transactions
DEFINE option = &enter_your_option

BEGIN
    IF( '&option' = '1' )
    THEN
        ....
    ELSIF( '&option' = '2' )
    THEN
        ....
    ELSIF( '&option' = '3' )
    THEN
        ....
    ELSE
        RAISE error;
    END IF;
END;

edited Jul 14, 2011 at 12:36

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

answered Jul 14, 2011 at 12:10

Sodved

8,6272 gold badges34 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Interactive PL/SQL script

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related