Oracle: using substitution variables in sqlplus

Get the value:


ACCEPT my_password CHAR PROMPT 'Password: ' HIDE
ACCEPT birthday DATE FORMAT 'dd/mm/yyyy' DEFAULT '01/01/1950' PROMPT 'Enter birthday date: '

Declaring the variable

DEFINE the_answer = 42

Undefine the variable

UNDEFINE the_answer

How to remember the result of the query

column the_date new_value the_rundate noprint;
select to_char(sysdate, 'DDMMYYYY_HH24MI') the_date from dual;

select '&the_rundate' from dual ;

Save the variables to the file

store set myvars.txt create
store set myvars.txt replace
store set myvars.txt append

Assign several values to the variable

DEFINE my_list = " 'the Life', 'the Universe', 'and Everything'"

select *
from Book
where answer in ( &my_list );

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.