web analytics

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

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

  

  

  

Categories

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.