LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon