« Oracle: disable all constraints referencing the table | Home | Oracle: using DBMS_METADATA for getting table structure »

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 );

Topics: Oracle, sqlplus | Submitter: checkthis

One Response to “Oracle: using substitution variables in sqlplus”

  1. eraser’s blog » Blog Archive » Oracle: using substitution variables in sqlplus Says:
    April 14th, 2008 at 9:09 pm

    [...] date: ‘. Declaring the variable. DEFINE the_answer = 42. Undefine the variable … code.techinterviews.com/oracle-using-substitution-variables-in-sqlplus/110 Code.TechInterviews.com [...]

Comments

You must be logged in to post a comment.