3

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.

user3341592
  • 1,419
  • 1
  • 17
  • 36
  • If you do not define your bind variables in SQL Developer, you will receive a prompt (same behavior with substitution variables). You can see this behavior demonstrated "out of the box" with a lot of the reports (e.g. look at the data dictionary reports). – Patrick Bacon Jul 18 '16 at 16:26
  • Are you talking of the 2nd code block? In the 3rd, I do have a DEFINE section -- if that's the right way to define bind vars? – user3341592 Jul 18 '16 at 16:41
  • I was speaking about how substitution and bind variables work in SQL Developer in general (not to any specific example you provided). From an Oracle perspective, substitution variables are used primarily within SQL*Plus scripts (well documented behavior). With bind variables, I will use them in SQL Developer typically in anonymous block or objects (e.g. package) or DML. BTW, Justin Cave has a nice answer to a similar question here, http://stackoverflow.com/questions/5653423/how-do-i-use-variables-in-oracle-sql-developer – Patrick Bacon Jul 18 '16 at 18:32
  • Patrick, sorry, but I don't get it. I don't see (structural) differences between my 2nd code block and the example (with `:exec`) of Justin. Could you be more explicit on the way to go for solving this problem? Thanks in advance! – user3341592 Jul 20 '16 at 12:18

1 Answers1

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;
Francesco Serra
  • 763
  • 8
  • 13
  • I've adapted your skeleton to my real use case. Though, whatever I do (pressing F5 or clicking on the green left arrow), SQL Developer pops up "Enter Substitution Variable" for var_t2_id and var_t3_id... And, I can't provide those answers: these are supposed to be known when running the first "SELECT.. INTO.. FROM" requests... What's the problem? – user3341592 Jul 20 '16 at 12:15
  • I've no confidence with SQLDeveloper but try selecting all the code, then run it ... – Francesco Serra Jul 20 '16 at 12:42
  • I did that as well... Same popup... (PS- What are you using?) – user3341592 Jul 20 '16 at 12:54
  • I've edited my answer with a tested and universal query. Remember to enable dbms_output using 'set serveroutput on' – Francesco Serra Jul 20 '16 at 13:37
  • Update script: `SET SERVEROUTPUT ON; DECLARE var_t1_id int := 2601; var_t2_id int; BEGIN SELECT t2_id INTO var_t2_id FROM table1 WHERE t1_id = var_t1_id; dbms_output.put_line('T2Id ' || var_t2_id || '...'); SELECT * FROM table2 WHERE t2_id = var_t2_id; END; ` – user3341592 Jul 25 '16 at 08:56
  • Still get an error: `PLS-00428: an INTO clause is expected in this SELECT statement` at the line of the `SELECT * FROM table2`. – user3341592 Jul 25 '16 at 08:57
  • In a stored procedure you can't execute a query without a INTO clause! Define another variable as yourvariable table2%ROWTYPE then modify in this way: SELECT * INTO yourvariable FROM table2 WHERE t2_id = var_t2_id; – Francesco Serra Jul 25 '16 at 09:28