Re: PlSql help. Urgent Please.

From: Arjan van Bentem <avbentem_at_DONT-YOU-DAREdds.nl>
Date: Sun, 25 Apr 1999 11:37:54 +0200
Message-ID: <7fvk6n$73h$1@weber.a2000.nl>

Abhimanyu Parasher
> BEGIN
> DBMS_output.put_line('*******************************************');
> DBMS_output.put_line('* 1) Amend Courses *');
> DBMS_output.put_line('* 2) Amend Subjects Info. *');
> DBMS_output.put_line('* 3) Amend Student Info. *');
> DBMS_output.put_line('* 4) Amend Student Enrolment Info. *');
> DBMS_output.put_line('*******************************************');
> DBMS_output.put_line('&choice');
> END;
It simply cannot be done in PL/SQL. Remember that PL/SQL code could be run anywhere: in triggers, which might fire when a user insert data using a GUI (where to put the menu then?) or even scheduled using dbms_jobs (no user interaction at all...). Also note that SQL*Plus replaces occurences of the ampersand (&) by the value you specify, not PL/SQL. So, even before SQL*Plus passes your code to Oracle, it already substitutes the ampersands.

You can use SQL*Plus commands to make your "menu". Not too fancy though, something like:

    prompt *******************************************
    prompt * 1) Amend Courses                        *
    prompt * 2) Amend Subjects Info.                 *
    prompt * 3) Amend Student Info.                  *
    prompt * 4) Amend Student Enrolment Info.        *
    prompt *******************************************

    accept choice prompt Make your choice:

    /* use default if user has not entered anything */     column the_choice new_value the_choice noprint     select nvl('&&choice.' , 'the default') the_choice     from dual;

    select '&the_choice.' "Final choice"     from dual;

I am not quite sure if the prompts need to be quoted. I think not -- which is a pity as you cannot use dashes in your menu, as a dash at the end of a line tells SQL*Plus that your statement continues on the next line...

    prompt -------------------------------

Arjan. Received on Sun Apr 25 1999 - 04:37:54 CDT