Reusing a variable in an Oracle SQL query

Question

Very simple question, but one I can't seem to find an answer to.

I have a variable calculated at the start of an Oracle SQL script (in TOAD, if that makes a difference), and I hoped to reuse it later in an UPDATE statement.

Variable is declared and then set here:

 DECLARE
 v_months number;  
 v_check number;
 BEGIN  
 v_check := '1'
 SELECT (total_due/monthly_amount) INTO v_months FROM TABLE1 WHERE ...

and will return a numeric value of, say, 20.

and I want to use that figure here:

IF(v_check = 1)
update TABLE2 set paid = 'YES' where sequence between v_months and 48;

This doesn't seem to be possible as the variable is flagged up as an invalid identifier, but is there a way round this?

Please edit the question with a complete minimal reproducible example. If you only give us snippets then we have to guess what comes before, between and after your snippets and you are less likely to get a helpful answer. — MT0
– MT0, Commented Jan 25, 2022 at 10:55
SQL script window doesn't work like a worksheet, every statement is a separate statement and knows nothing about other statements executed earlier. Please, show your entire code that throws an error — astentx
– astentx, Commented Jan 25, 2022 at 12:41

MT0 · Accepted Answer · 2022-01-25 11:05:28Z

1

DECLARE the variable at the start of the PL/SQL block and then just re-use it in the same PL/SQL block and it works:

DECLARE
  v_months PLS_INTEGER;
BEGIN
  SELECT (total_due/monthly_amount) INTO v_months FROM TABLE1;

  update TABLE2 set paid = 'YES' where sequence between v_months and 48;
END;
/

If you are trying to re-use it between different PL/SQL blocks then it will not work using a PL/SQL variable.

db<>fiddle here

If you want to use a variable in multiple statements then you can use bind variables:

VARIABLE v_months NUMBER

BEGIN
  SELECT (total_due/monthly_amount) INTO :v_months FROM TABLE1;
END;
/

update TABLE2 set paid = 'YES' where sequence between :v_months and 48;

edited Jan 25, 2022 at 11:05

answered Jan 25, 2022 at 10:59

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BJH Over a year ago

thanks - that explains what was wrong - I thought the variable could be carried across differing blocks of code.

Collectives™ on Stack Overflow

Reusing a variable in an Oracle SQL query

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related