menu in PL/SQL
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: menu in PL/SQL

Hybrid View

  1. #1
    Join Date
    Apr 2001
    Posts
    10
    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

  2. #2
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    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.

  3. #3
    Join Date
    Apr 2001
    Posts
    10
    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

  4. #4
    Join Date
    Apr 2001
    Location
    UK
    Posts
    137
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width