using variable in dynamic query

Question

set serveroutput on
DECLARE
 statement VARCHAR2(4000);
 BALANCING_ID INTEGER;
 VAULT_TILL_ID INTEGER;
 balancingid INTEGER := 16437;
BEGIN
 statement := 'SELECT EOD_BALANCING_ID, VAULT_TILL_ID
 FROM MBCUSR2UT2.CASH_BALANCING
 WHERE eod_balancing_id = :balancingid';
 EXECUTE IMMEDIATE statement
 INTO BALANCING_ID, VAULT_TILL_ID;
 DBMS_OUTPUT.PUT_LINE('ID: ' || BALANCING_ID || ', Vault: ' || VAULT_TILL_ID);
END;

I have this query and it is not working due to error in line

WHERE eod_balancing_id = :balancingid

saying

Error report -
ORA-01008: not all variables bound
ORA-06512: at line 10
01008. 00000 - "not all variables bound"
*Cause: 
*Action:

but when I use directly like

WHERE eod_balancing_id = 16437';

it is working fine why is it not working?

Answer 1

Why do you use dynamic SQL? IN your case it does not seem to be needed.

Anyway, would be this one:

EXECUTE IMMEDIATE statement INTO BALANCING_ID, VAULT_TILL_ID USING balancingid;

• guradio: I am just exploring oracle if there is a way to use variable so that it is easy for me to rerun the query just by changing variable value.