how to clear value assigned to a variable

[amolso]

I have the follwing code in my
qry.sql
-----------------------------------------------
accept auction_id prompt 'Enter Auction Id : '

select auction_supplier_code, price_entered from auction_bids
where auction_id = &auction_id *
/
select a.oid supplier, a.supplier_company_code , b.company_code
from auction_suppliers a, company b
where a.auction_id = &auction_id *
and a.supplier_company_code = b.oid
/
----------------------------------------------

I am accepting auction
I have used 'accept auction_id' so that system should
prompt me only ones to enter id, And I want to use same
auction_id in both queries.
However my problem is different ,
when I execute some other query For e.g.
-----------------------------------------------------
select * from auction where auction_id = &auction_id
-----------------------------------------------------

it doesn't wait for me to enter auction_id but uses same
id which I used in earlier. This is happening becuase of
the same variable name. I have lot of queries where I
have used auction_id variable and I cannot change variable
everywhere. Therefore I have to exit & log on again.
Is there any setting which clears variable values.
(I am using Oracle 8.1.6 on Windows NT Server)
I have tried clear, clear buff and some other option
but It didn't really work.

Hoping for help
Amol

 

[MikeJones]

Your best bet is once you have used the variable to undefine it. That way the next time you try and use it SQL Plus will ask you for a vaule again!

Try...

accept auction_id prompt 'Enter Auction Id : '

select auction_supplier_code, price_entered from auction_bids
where auction_id = &auction_id *
/
select a.oid supplier, a.supplier_company_code , b.company_code
from auction_suppliers a, company b
where a.auction_id = &auction_id *
and a.supplier_company_code = b.oid
/

undefine auction_id

 

[amolso]

Thank you very much mike
your suggestion is working fine

Amol