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

You have choices: http://stackoverflow.com/questions/5653423/how-do-i-use-variables-in-oracle-sql-developer — Patrick Marchand, Aug 02 '16 at 13:21

score 2 · Answer 1 · answered Aug 02 '16 at 13:32

2

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

answered Aug 02 '16 at 13:32

Tony Andrews

129,880
21
220
259

Still doesn't work. anonymous block completed Bind Variable "COB_DATE" is NOT DECLARED. Or if I replace the : with && it says "SQL command not properly ended" – Nickpick Aug 02 '16 at 13:33

Marcin Wroblewski · Accepted Answer · 2016-08-02T13:39:19.293

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

edited Aug 02 '16 at 13:39

answered Aug 02 '16 at 13:32

Marcin Wroblewski

3,491
18
27

so how can i define a date? – Nickpick Aug 02 '16 at 13:35
Works great. Only remaining issue, how can i put multiple entries to v_mode? – Nickpick Aug 02 '16 at 13:42
I suggest creating a new question – Marcin Wroblewski Aug 02 '16 at 13:44
exec :v_mode1 := 'MAG'; exec :v_mode2 := '???'; select * ... TRD_SRC_SYS in (:v_mode1, :v_mode2) ... – Marcin Wroblewski Aug 02 '16 at 13:55
what if there are 20 variables? Any way to put them in as a list? – Nickpick Aug 02 '16 at 13:56
There's no list variable in sqlplus. You can try to do sth like e.g. v_modes := 'VAL1,VAL2'; and TRD_SRC_SYS IN TABLE(some_table_function(:v_modes)) – Marcin Wroblewski Aug 02 '16 at 14:02
Couldn't get that to work. Also tried assigning these values: '("value1", "value2")'; but the result is 0 rows which is incorrect – Nickpick Aug 02 '16 at 14:04

score 1 · Answer 3 · answered Oct 14 '20 at 10:47

1

for Oracle SQL Developer:

define defVar= 'AA%'

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

answered Oct 14 '20 at 10:47

Henry

11
1

1

It might not be the best example, or properly formatted, but that's easily fixed. The principle is correct. @Gi1ber7, your comment should help new SO members, not discourage them from ever posting again... feel free to edit the post (or suggest an edit, depending on reputation) to improve it. And no, there's not actually a need for an "explanation of how you solved the issue". :-) – Amos M. Carpenter Aug 26 '21 at 06:51

Define variable in Oracle SQL developer

3 Answers3