How to get variables really working in Oracle SQL Developer?

Question

In Oracle SQL Developer, using substitution variables in this way WORKS:

DEFINE var_t1_id int = 2601;
DEFINE var_t2_id int = (SELECT t2_id FROM table1 WHERE t1_id = &var_t1_id);
DEFINE var_t3_id int = (SELECT t3_id FROM table1 WHERE t1_id = &var_t1_id);

SELECT *
FROM table2
WHERE t2_id = &var_t2_id;

SELECT *
FROM table3
WHERE t3_id = &var_t3_id;

Though, when I change the 2601 into something else, and re-run the query, SQL Developer continues to use 2601 for the substitution!!! Not what I want...

I tried this:

variable var_t1_id number;
variable var_t2_id number;
variable var_t3_id number;

exec :var_t1_id := 2601;
exec SELECT t2_id INTO :var_t2_id FROM table1 WHERE t1_id = :var_t1_id;
exec SELECT t3_id INTO :var_t3_id FROM table1 WHERE t1_id = :var_t1_id;

SELECT *
FROM table2
WHERE t2_id = :var_t2_id;

SELECT *
FROM table3
WHERE t3_id = :var_t3_id;

and this:

DEFINE var_t1_id int = 2601;
DEFINE var_t2_id int = (SELECT t2_id FROM table1 WHERE t1_id = (SELECT &&var_t1_id from dual));
DEFINE var_t3_id int = (SELECT t3_id FROM table1 WHERE t1_id = (SELECT &&var_t1_id from dual));

SELECT *
FROM table2
WHERE t2_id = (SELECT &&var_t2_id FROM dual);

SELECT *
FROM table3
WHERE t3_id = (SELECT &&var_t3_id FROM dual);

but none does work correctly -- I mean, the script should run without prompting me for values...

How can I solve this?

I'm used to SQL Server where all of this is so simple... Of course, I miss Oracle knowledge... reason why I'm asking here for tips.

Answer 1

You can try with INTO keyword

DECLARE
    --all your variables
    yout_variable_0 int := 1608;
    your_variable_1 int;
BEGIN
    select [...]
    into your_variable_1
    from your_table
    where your_column = your_variable_0;
END;

Now your result is stored in your_variable_1.

You can try with this simple query

DECLARE
  --all your variables
  your_variable_0 varchar2(200) := '201605';
  your_variable_1 varchar2(200);
BEGIN
  select your_variable_0
  into your_variable_1 
  from dual;

  dbms_output.put_line('Output ...' || your_variable_1);

END;