OraFAQ Forum: Client Tools » SQLPLUS: can substitution variable &1 have a default?

Home » Other » Client Tools » SQLPLUS: can substitution variable &1 have a default? (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQLPLUS: can substitution variable &1 have a default? [message #399739]

Thu, 23 April 2009 16:41

mgrimley
Messages: 1
Registered: April 2009

Junior Member

Hello,

Is it possible to have a default value for an &1 substitution variable in an sqlplus script?

When the user executes the script, if they provide a value then I want &1 to use this value. This works fine. But if they run the script without providing a value, then sqlplus prompts for a value as follows
Enter value for 1:

But what I would like to do is to default &1 to a value defined in the script instead of prompting the user. Is there anyway to accomplish this?

Thanks for your help,
Mary

Report message to a moderator

Re: SQLPLUS: can substitution variable &1 have a default? [message #399745 is a reply to message #399739]

Thu, 23 April 2009 21:00

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

this won't help much but I guess I should offer to comments:

1) I recall reading once many years ago, a paper showing how to do this. If I remember right, it was rather complicated.

2) that said, as you can see by the high number of reads of your post with no replys, there is no simple way, otherwise someone including myself would be able to tell you.

Sorry. Let us hope my memory has faded badly with age and that someone who's brain still works can give you the instructions you need, or that Oracle has upgraded sqlplus for this and I just don't know it yet, but seems to me this has been a long outstanding fault with sqlplus.

Good luck, Kevin

Report message to a moderator

Re: SQLPLUS: can substitution variable &1 have a default? [message #399773 is a reply to message #399739]

Thu, 23 April 2009 23:33

Michel Cadot
Messages: 68731
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Apart from setting a value in your [g]login.sql file I don't see any way to do it.

Regards
Michel

Report message to a moderator

Re: SQLPLUS: can substitution variable &1 have a default? [message #399784 is a reply to message #399739]

Fri, 24 April 2009 01:27

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

This doesn't answer your problem exactly, but ... perhaps might help in certain situations: instead of one ampersand (&), try with two of them (&&). Doing so, you'll have to enter value only once; every new query execution won't prompt you for the value:

SQL> select count(*) from emp
  2  where deptno = &&1;
Enter value for 1: 10

  COUNT(*)
----------
         3

SQL> /

  COUNT(*)
----------
         3

SQL> /

  COUNT(*)
----------
         3

SQL>

Report message to a moderator

Previous Topic:	text output from iSQL*PLUS
Next Topic:	Starting sqlplus using a window cmd prompts timestamp in front

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Jan 18 05:14:31 CST 2025