Oracle Sqlplus variable declare

sqlplus var declare
sqlplus variable 
sqlplus bind variable 




SQL> var h varchar2(10)
SQL> begin :h := 'abcdf'; end;
 /


PL/SQL procedure successfully completed.


SQL> print :h

H
--------------------------------
abcdf






SQL> var myvar varchar2(30)
SQL> exec :myvar := 'Anuj'

PL/SQL procedure successfully completed.


SQL> print myvar

MYVAR
----------------------------
Anuj

OR !!!

SQL> print :myvar

MYVAR
----------------------
Anuj




To unset variable 

exec :myvar := '' or exec :myvar := NULL



You cannot undefine bind variables in sqlplus


================

VARIABLE btime VARCHAR2(30)
VARIABLE etime VARCHAR2(30)
exec :btime:='2022-01-26 12:00:00';
exec :etime:='2022-01-28 12:00:00';

select
 count(*) count
--distinct a.machine,a.program,a.module
from dba_hist_active_sess_history a
where 1=1
and sample_time between to_date(:btime,'YYYY-MM-DD HH24:MI:SS') and to_date(:etime,'YYYY-MM-DD HH24:MI:SS')
;

    COUNT
----------
     41627


===

for spool 

VARIABLE BgnSnap NUMBER

VARIABLE EndSnap NUMBER

exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;


column awr new_val X
select to_char(:BgnSnap||'_'||:EndSnap) awr from dual;
spool awr_&X._file.txt

--- select 
spool off 

===

in Oracle 23c 

Connected to:
Oracle Database 23c Free Release 23.0.0.0.0 - Develop, Learn, and Run for Free
Version 23.3.0.23.09

SQL>
 var h varchar2(8)
 begin :h := '123456789'; end;
 /

PL/SQL procedure successfully completed.

SQL> print :h

H
--------------------------------
12345678

SQL>