Prompt remembers value after first time in SQL Plus

[MCubitt]

In SQL Plus I have a "prompt &&value" line which worked fine on the first time I ran the script.

However, each subsequent run remembers it which I do not want it to. I understand && means remember it throughout the script but it in fact the session? I have not exited SQL Plus because I expected each time I ran the script for the prompt to ask me to enter data.

Is there a way to tell SQL Plus to clear out this and ask each time the prompt is executed?

I am using && because the variable is used in a few statements following the prompt and I do not want to have to enter data over again per statement.


Thanks



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[stefanhei]

Hi MCubitt,
the SQL*Plus command undefine is what you're looking for:

11:48:20 SQL>prompt &&testvar
Enter value for testvar: 123
123
11:48:42 SQL>prompt &&testvar
123
11:48:49 SQL>undefine testvar
11:48:55 SQL>prompt &&testvar
Enter value for testvar: 456
456

Stefan

 

[MCubitt]

Thanks Stef, I though I had tried that but I did use & pefixing the variable. Thanks.



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SantaMufasa]

MCubitt,

Instead of the "undefine", I'll bet you want to use the following construct:

accept testvar prompt "Enter the value to locate: "
select * from <table_name> where <some_column> = '&testvar'
         and '&testvar' <> '<unwanted_value>';

In the example, above, testvar remains until you run the script again. You can "undefine" it if you wish, but you shouldn't need to since the the above construct will over-write the old value and should behave the way you intend.

Let us know what you discover,

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:35 (23Jun04) UTC (aka "GMT" and "Zulu"), 09:35 (23Jun04) Mountain Time)

 

[MCubitt]

Santa,

Thanks for your additional notes, but in this case I resolved the issue another way anyway. Since the SQL is being called from a batch file now teh SQl session is closed each time.

Thanks all - useful for next time.




There's no need for sarcastic replies, we've not all been this sad for that long!