CODEWITHSUNDEEP
oracle-database
BJH
BJH

Reputation: 45

Reusing a variable in an Oracle SQL query

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?

Upvotes: 0

Views: 1145

Answers (1)

MT0
MT0

Reputation: 167972

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;

Upvotes: 1

Related Questions