SQL*Plus substitution variables and PL/SQL output
Define, prompt for and reuse SQL*Plus variables, with an example of storing a date in a variable.
SQL*Plus substitution variables let a script ask for a value or reuse a value in several statements. This article lists the basic commands, a PL/SQL output example, and a way to store a date in a variable.
Define, show and undefine variables
DEFINE -- list all user-defined variables
DEFINE myv = 'King' -- define a variable
DEFINE myv -- show it: DEFINE MYV = "King" (CHAR)
UNDEFINE myv -- remove it
Ask the user for a value
ACCEPT myv2 CHAR PROMPT 'Enter a last name: '
Reference a variable
select employee_id from employees where last_name = '&myv';
Double ampersand (&&)
&&name prompts once and keeps the value for the later references in the same session, until the variable is undefined or the session ends.
PL/SQL output
SET SERVEROUTPUT ON;
declare
v_str1 varchar2(80);
begin
select '002' into v_str1 from dual;
dbms_output.put_line('test ' || v_str1);
end;
/
Current date and time
select sysdate from dual;
SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "TODAY" FROM DUAL;
SELECT sessiontimezone FROM dual;
Store a date in a variable
A COLUMN ... NEW_VALUE clause copies the value of the column into a variable after each query. Here it builds a file name:
column TODAY new_value datestr;
SELECT TO_CHAR (SYSDATE, 'YYYYMMDDHH24MISS') "TODAY" FROM DUAL;
define myv = &DATESTR._spfile.ora
create pfile='/backup/&myv' from spfile;
Notes
- Sources: a developpez.net forum thread and an Oracle blog article on SQL*Plus substitution variables.