Home » SQL & PL/SQL » SQL & PL/SQL » Accept User Inputs (Oracle 10g)
Accept User Inputs [message #422031] Sun, 13 September 2009 20:30 Go to next message
lakshmis
Messages: 102
Registered: November 2008
Location: India
Senior Member
Hi,
In this Program, I want the menu to be displayed before prompting for the value of "user_in". Is this possible? Please Help.

DECLARE
user_in number(2);
BEGIN
DBMS_OUTPUT.PUT_LINE('PLEASE CHOOSE FROM THE MENU');
DBMS_OUTPUT.PUT_LINE('**************************************************');
DBMS_OUTPUT.PUT_LINE('1. HIRING');
DBMS_OUTPUT.PUT_LINE('2. FIRING');
DBMS_OUTPUT.PUT_LINE('**************************************************');
user_in :=&user_in;
CASE  user_in 
WHEN 1 THEN
DBMS_OUTPUT.PUT_LINE('HIRING SELECTED');
WHEN 2 THEN
DBMS_OUTPUT.PUT_LINE('FIRING SELECTED');
ELSE
DBMS_OUTPUT.PUT_LINE('WRONG SELECTED');
END CASE;
END;


This is the Output which Iam getting.
Enter value for user_in: 1
PLEASE CHOOSE FROM THE MENU
**************************************************
1. HIRING
2. FIRING
**************************************************
HIRING SELECTED

PL/SQL procedure successfully completed.

Re: Accept User Inputs [message #422032 is a reply to message #422031] Sun, 13 September 2009 20:34 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>Is this possible?
Not using PL/SQL & DBMS_OUTPUT.
Neither is suitable for an application that interacts with end user.
The "output" from DBMS_OUTPUT is buffered & only made visible after the procedure completes.
Re: Accept User Inputs [message #422034 is a reply to message #422031] Sun, 13 September 2009 23:35 Go to previous message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
PL/SQL block is a SINGLE statement that executes in a whole before returning to the client.

Use PROMPT SQL*Plus command instead, for instance:
SQL> host type t.sql
prompt PLEASE CHOOSE FROM THE MENU
prompt **************************************************
prompt 1. HIRING
prompt 2. FIRING
prompt **************************************************
DECLARE
user_in number(2);
BEGIN
user_in :=&user_in;
CASE  user_in
WHEN 1 THEN
DBMS_OUTPUT.PUT_LINE('HIRING SELECTED');
WHEN 2 THEN
DBMS_OUTPUT.PUT_LINE('FIRING SELECTED');
ELSE
DBMS_OUTPUT.PUT_LINE('WRONG SELECTED');
END CASE;
END;
/

SQL> @t
PLEASE CHOOSE FROM THE MENU
**************************************************
1. HIRING
2. FIRING
**************************************************
Enter value for user_in: 1
HIRING SELECTED

PL/SQL procedure successfully completed.

Regards
Michel


[Updated on: Sun, 13 September 2009 23:36]

Report message to a moderator

Previous Topic: Improving sql sentence (merged)
Next Topic: Chained transaction retrieval query
Goto Forum:
  


Current Time: Fri Feb 07 19:16:37 CST 2025