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
--------------------------------------------------------------------
