Scenario: Use SQL*Plus to produce a menu of choices:

1. Run procedure 1
2. Run procedure 2
3. Run procedure 3
4 Exit

Enter Your Choice:

After entering the choice and executing the procedure, the menu should be redisplayed so the user can make another choice.

Method: This is not easy because there is no loop function in SQL*Plus. PL/SQL does have looping capabilities but it cannot prompt for user input.

The only solution I have been able to find is to use a recursive method. You need 3 files:

------------------- Menu.sql --------------------------

prompt 1 Run procedure 1
prompt 2 Run procedure 2
prompt 3 Run procedure 3
prompt 4 Exit

accept option prompt 'Choice: '
col result new_value result

select decode(&option, 4, 'file_exit', 'run_file '||&option) result
from dual
/

@&result

--------------------- run_file.sql --------------------------------

begin
run_proc&1;
end;
/
@menu

----------------------- file_exit.sql-------------------------------

exit

--------------------------------------------------------------------