How to create a reference value in Oracle SQl Developer

Question

I use basic sql querying for my day to day work, but I regularly find myself needing to run queries in different tables using the same where clauses.

What I would ideally like to do is locally set a value to a name, for example: traderef = ABCD1234. It's kind of like Defining a name in excel.

and then use 'traderef' in my queries,

select * from table1 where tranid = traderef

select * from table2 where tranid = traderef and otherattr = 'xyz1'

I only have query access to the dbs that i use, i have tried to google results, and found some info re SET

TIA

Answer 1

Declare a bind variable using the SQL/Plus and SQL Developer command VARIABLE name data_type and assign it a value using EXECUTE (or a PL/SQL anonymous block) and then use it in your queries:

VARIABLE traderef VARCHAR2(20)

EXEC :traderef := 'ABCD1234';

SELECT * FROM table1 WHERE tranid = :traderef;

SELECT * FROM table2 HWERE tranid = :traderef AND otherattr = 'xyz1';