menu in PL/SQL

[kasora]

Hi everybody,

I want to create the Procedure menu that will read the parameter from the keybord interactively and perform some actions not leaving the menu.
Something like this:
CREATE OR REPLACE PROCEDURE menu AS
...
BEGIN
LOOP
=> reading the value from keybord
IF value = 'A' THEN
=> call some function
ELSEIF value = 'B' THEN
=> call another function
ELSEIF value = 'C' THEN
EXIT;
END IF;
END LOOP;
END;

In SQL*Plus we can use ACCEPT and then call the stored procedure passing the "value" as parameter, but when the procedure is done the script is finished. I can't find any way to create loop-like script that will repeat reading of the value.

It is possible to have the loop in PL/SQL but what about the interactive reading of the value?. I looked through several PL/SQL books and didn't find any example.

Any clue?

Kas

[nealh]

The problem is that SQL*plus has no loop function. You can rig up something using three files as follows:

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

prompt 1 Option 1
prompt 2 Option 2
prompt 3 Option 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
myproc(&1);
end;
/
@menu

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

exit

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

A bit naff, but SQL*Plus isn't really designed for this sort of thing.

[kasora]

Thank you nealh!!

I tested yours proposal and it is working fine.
Generally it is what I need, so I will implement this approach.
But I am still curies, is any way to do this in the PL/SQL?

Kas

[nealh]

I don't believe so. Obviously, PL/SQL has a loop function but it doesn't have the ability to accept keyboard input. You can do something like:

begin
loop:
if a = '&xx' then
...
end loop;
end;

SQL*plus will prompt you for a variable subsitution but it then passes control over to PL/SQL. You can't get PL/SQL to come back to SQL*plus to ask for more input.