Example for bind variable usage in SqlPlus

Example sql code

*Declare DATE variable as bind variable in SQL Plus*
 
 
set linesize 132;
set timing on;
set linesize 255;
set pagesize 5000;
set echo on;
set heading on;
set serveroutput on;
 
 
variable type varchar2(100);
execute :type := 'S';
 
variable startdate varchar2(21);
execute :startdate := '01/10/2013_00:00:00';
 
variable enddate varchar2(21);
execute :enddate := '01/10/2013_01:00:00';
 
variable testid number;
execute :testid := 6154;
 
set autotrace on exp stat;
set autotrace traceonly exp stat;
 
select count(*) as y0_
  from TEST_TABLE this_
 where this_.TEST_ID = :testid
   and this_.TYPE = :inctype
   and this_.C_TIME_LOCAL >= to_date(:startdate,'dd/mm/yyyy_hh24:mi:ss');
   and this_.C_TIME_LOCAL <= to_date(:enddate,'dd/mm/yyyy_hh24:mi:ss');

-- Check Execution Plan details
select sql_id, child_number, s.*
  from gv$sql s
 WHERE upper(SQL_FULLTEXT) like '%SUMMARY%'
 order by last_active_time desc;
 
SELECT * FROM TABLE(dbms_xplan.display_cursor('b250w5cxy46va', 0,'ADVANCED'));
SELECT * FROM table(DBMS_XPLAN.DISPLAY_AWR('fywm6bbj5s1zn'));
SELECT * FROM TABLE(dbms_xplan.display_cursor('287h80pjaugst'));
SELECT * FROM TABLE(dbms_xplan.display_cursor('dqkapjswv169p', 0));
SELECT * FROM TABLE(dbms_xplan.display_cursor('6z5c8yaf3m1gt', 0,'ADVANCED'));
 
set autotrace on exp stat;
set autotrace traceonly exp stat;
 
EXPLAIN PLAN FOR SQL_STATEMET;
@?/RDBMS/ADMIN/UTLXPLS
select * from table(dbms_xplan.display);
 
 
 select sql_id, child_number, s.*
  from gv$sql s
 WHERE upper(SQL_FULLTEXT) like '%V_INCIDENT_SIGHTING_SUMMARY%'
 order by last_active_time desc;
select sql_text, v.sql_id, name, value_string, datatype_string
  from v$sql_bind_capture vbc
  join v$sql v
 using (hash_value)
 where v.sql_id in ('frmxr4w3tb9wv', 'dqkapjswv169p', '61g3km3x621wt');
SELECT sql_id, name, value_string, datatype_string
  from v$sql_bind_capture
 where sql_id in ('frmxr4w3tb9wv', 'dqkapjswv169p');
SELECT * FROM v$sql_bind_capture WHERE sql_id = 'frmxr4w3tb9wv';
SELECT * FROM v$sql_bind_capture WHERE sql_id = '61g3km3x621wt';
 
SELECT *
  FROM v$sql_bind_capture
 WHERE sql_id in ('dqkapjswv169p', 'frmxr4w3tb9wv', '61g3km3x621wt');