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>
3 comments:
set linesize 500 pagesize 300
variable b_date varchar2(30);
variable e_date varchar2(30);
exec :b_date := '14/04/2021 01:00:00';
/
exec :e_date := '14/04/2021 02:00:00';
/
This blog contains more useful information, Thanks for this blog...
Microsoft Azure Training in Chennai
Azure Online Training
Best Azure Training in Bangalore
Fantastic blog!!! Thanks for sharing with us, Waiting for your upcoming data.
How Cloud Computing Helps Business
Cloud Computing For Small Business
Post a Comment