Home » SQL & PL/SQL » Client Tools » SQLPLUS: can substitution variable &1 have a default? (10g)
SQLPLUS: can substitution variable &1 have a default? [message #399739] Thu, 23 April 2009 16:41 Go to next message
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
Re: SQLPLUS: can substitution variable &1 have a default? [message #399745 is a reply to message #399739] Thu, 23 April 2009 21:00 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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
Re: SQLPLUS: can substitution variable &1 have a default? [message #399773 is a reply to message #399739] Thu, 23 April 2009 23:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: SQLPLUS: can substitution variable &1 have a default? [message #399784 is a reply to message #399739] Fri, 24 April 2009 01:27 Go to previous message
Littlefoot
Messages: 20891
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>
Previous Topic: text output from iSQL*PLUS
Next Topic: Starting sqlplus using a window cmd prompts timestamp in front
Goto Forum:
  


Current Time: Sun Dec 04 12:51:15 CST 2016

Total time taken to generate the page: 0.07372 seconds