How to create a menu in SQLPlus or PL/SQL

Question

I have several scripts that I would like to start from a menu presented to the SQLPlus user. Something like:

Please make a selection:
1: Do script a
2: Do script b
3: Do script c

I just need a point in the right direction, not a quick answer.

Answer 1

It's hard to accomplish what you are trying to do with SQLPlus and/or PL/SQL.

SQLPlus is a frontend for Oracle databases. Its main purpose is to perform queries against an Oracle RDBMS.

PL/SQL is a language to manipulate data in Oracle.

Instead, if you want something with user interaction, I would suggest you to write a little script/program in insert your favorite language here (could python, C/C++, C#, Java) with an Oracle connection to perform the SQL queries or PL/SQL programs you need.

Answer 2

DBMS_OUTPUT can be used to print lines to the screen. It looks like it has has functions that do a GET_LINE as well, but I've never used it and don't know how well they work.

You just need to be careful about your SQLPlus settings. It can truncate lines if you don't set it up properly.

Answer 3

I'd got with a real language for this (as per Pablo's comment).

You could have some stuff in a login.sql that displayed a list suggestions when you connect (PRINT or PROMPT SQL*Plus statements).

And you could have a bunch of scripts called 1.sql, 2.sql etc which would get run if the user entered @1, @2 etc. (as long as they are in the correct directory).

But really SQL*Plus isn't suited for this.

Answer 4

You can execute scripts from a master script:

CASE LOWER(&v_script_selection)
  WHEN 'a' THEN
    @script_a.sql
  WHEN 'b' THEN
    @script_b.sql
  WHEN 'c' THEN
    @script_c.sql
  ELSE
    DBMS_OUTPUT('No such option available')
END

&variablename is used to refer to the variable variablename in SQLPlus, much the same way $variablename is used in shell scripts. If variablename is undefined, then SQLPlus prompts the user for a value.

You can provide a path relative to the master script - the example relies on the supporting scripts to be in the same directory.

Answer 5

Here is a SQL Plus script to do that:

prompt Please make a selection:
prompt 1: Do script a
prompt 2: Do script b
prompt 3: Do script c

accept selection prompt "Enter option 1-3: "

set term off

column script new_value v_script

select case '&selection.'
       when '1' then 'script_a'
       when '2' then 'script_b'
       when '3' then 'script_c'
       else 'menu'
       end as script
from dual;

set term on

@&v_script.

NB The 'menu' in the ELSE part of the case expression is the name of this script, so that it runs itself again when the user enters an invalid option.

Answer 6

If the scripts are totally unrelated, I'd use a simple batch file or shell script.