« 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: '
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 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
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”
Comments
You must be logged in to post a comment.
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 [...]