Define variable in Oracle SQL developer

Question

I'm trying to define standard values in variables in ORACLE SQL Developer, but it keeps asking me to enter a value. How can I avoid that and put as default value for v_mode ='X1','X2' and set COB_DATE to 14 July so that there is NO popup?

 variable   COB_DATE date
 variable   v_mode varchar(20);
 exec :COB_DATE := '14-JUL-2016';
 exec :v_mode := 'MAG';

select * 
    FROM DF_RISK_SIT2_OWNER.recon_ts_rs
    WHERE SRC_HUB = 'DBRS'
    AND TRD_SRC_SYS in :v_mode 
    AND DSET_COB_DT = :COB_DATE

but I get the error: Bind Variable "COB_DATE" is NOT DECLARED

Answer 1

You have to use "Run Script (F5)" not "Run Statement (Control+Enter") - I have circled the toolbar icon in red:

Answer 2

SQL> help var

 VARIABLE
 --------

 Declares a bind variable that can be referenced in PL/SQL, or
 lists the current display characteristics for a single variable
 or all variables.

VAR[IABLE] [variable [type]]

 where type represents one of the following:

     NUMBER         CHAR          CHAR (n [CHAR|BYTE])
     NCHAR          NCHAR (n)     VARCHAR2 (n [CHAR|BYTE])
     NVARCHAR2 (n)  CLOB          NCLOB
     REFCURSOR      BINARY_FLOAT  BINARY_DOUBLE

As you can see there is no DATE type here. I guess the whole

variable   COB_DATE date

is ignored.

As a workaround you can define COB_DATE as varchar2 and convert it to DATE in the sql

variable   COB_DATE varchar2(30)
variable   v_mode varchar2(20)
exec :COB_DATE := '14-JUL-2016';
exec :v_mode := 'MAG';

select * 
    FROM DF_RISK_SIT2_OWNER.recon_ts_rs
    WHERE SRC_HUB = 'DBRS'
    AND TRD_SRC_SYS in :v_mode 
    AND DSET_COB_DT = TO_DATE(:COB_DATE, 'DD-MON-YYYY')

or rely on implicit conversion using your original query

Answer 3

for Oracle SQL Developer:

define defVar= 'AA%'

Select... where somefield like '&&defVar';