Home » SQL & PL/SQL » Client Tools » SQL*Plus immediately prompts for value while using &variable_name (SQL*Plus: Release 9.2.0.5.0)
SQL*Plus immediately prompts for value while using &variable_name [message #489715] Tue, 18 January 2011 10:42 Go to next message
DataMouse
Messages: 31
Registered: December 2010
Location: New York, NY - United Sta...
Member
I'd like to display the question: "What is your name?", then have the user type in their name, and then display their name on the screen. However, as you can see in the code below, SQL*Plus is prompting the user to enter in a value for the your_name_input variable immediately, before the question is even asked.

I believe that this is because SQL*Plus will immediately ask for a value for a variable if it encounters an ampersand (&) sign, and this is just how it works. Is this true? How can I get around this?

SQL> SET SERVEROUTPUT ON
SQL> SET VERIFY OFF
SQL> DECLARE
  2   your_name char(30);
  3  BEGIN
  4   dbms_output.put_line('What is your name? ');
  5   your_name := '&your_name_input';
  6   dbms_output.put_line('Your name is: ' || your_name);
  7  END;
  8  /
Enter value for your_name_input: Tiger Woods
What is your name?
Your name is: Tiger Woods

PL/SQL procedure successfully completed.


Thank you.
Re: SQL*Plus immediately prompts for value while using &variable_name [message #489716 is a reply to message #489715] Tue, 18 January 2011 10:44 Go to previous messageGo to next message
cookiemonster
Messages: 13292
Registered: September 2008
Location: Rainy Manchester
Senior Member
Yes it's true. To get around - use a UI that's actually designed to cope with asking questions of users. sqplus isn't.
Re: SQL*Plus immediately prompts for value while using &variable_name [message #489718 is a reply to message #489716] Tue, 18 January 2011 10:46 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>How can I get around this?
SET DEFINE OFF

but you should use different tool to interact with users.
Re: SQL*Plus immediately prompts for value while using &variable_name [message #489720 is a reply to message #489718] Tue, 18 January 2011 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 65850
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ACCEPT your_name_input PROMPT 'What is your name? '
DECLARE
  your_name char(30);
BEGIN
  your_name := '&your_name_input';
  dbms_output.put_line('Your name is: ' || your_name);
END;
/

Regards
Michel
Re: SQL*Plus immediately prompts for value while using &variable_name [message #489724 is a reply to message #489720] Tue, 18 January 2011 10:57 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
>(SQL*Plus: Release 9.2.0.5.0)
V9.2 is obsoleted & unsupported.
Please join 21st Century at your earliest convenience!
Re: SQL*Plus immediately prompts for value while using &variable_name [message #489725 is a reply to message #489720] Tue, 18 January 2011 11:04 Go to previous message
DataMouse
Messages: 31
Registered: December 2010
Location: New York, NY - United Sta...
Member
Michel Cadot wrote on Tue, 18 January 2011 11:49
ACCEPT your_name_input PROMPT 'What is your name? '
DECLARE
  your_name char(30);
BEGIN
  your_name := '&your_name_input';
  dbms_output.put_line('Your name is: ' || your_name);
END;
/

Regards
Michel


Oh wow! This is perfect, thank you.

@BlackSwan Yea I know, that's just what I was set up with. Maybe I'll update my SQL*Plus version soon, but for right now, it's working OK.
Previous Topic: How to transfer data from sql to oracle
Next Topic: SQLPLUs not starting from command prompt
Goto Forum:
  


Current Time: Tue Oct 23 03:10:35 CDT 2018