title: How to use Date type bind variable in ORACLE SQL Statement?
body: <p>I have a loop in my query So I have to use Oracle procedure but It's working only if I am giving date hardcoded, but I want to do it with bind variables, How can it be done?</p>
  
  <p><strong>My Query:</strong></p>
  
  <pre><code>DECLARE
      TABLE_NAME VARCHAR2(100);
      SQL_STATEMENT VARCHAR2(2000);
      TOTAL_CASES NUMBER(10) := 0;
      LOOP_CASES NUMBER(10) := 0;
  BEGIN
  
      FOR MY_ROW IN 00..99
      LOOP
          TABLE_NAME:= 'history'||TRIM(TO_CHAR(MY_ROW,'00'));
  
          SQL_STATEMENT:='SELECT COUNT(DISTINCT USR_ID) 
          FROM ' || TABLE_NAME || '@hr WHERE ATTRIBUTE_ID = 109 
  
          AND OLD_VALUE IS NULL AND UPDATED_BY_SCREEN = ''CRM'' AND TRUNC(UPDATE_DATE) between TO_DATE(''15-MAY-2016'') and TO_DATE(''21-MAY-2016'')';
          EXECUTE IMMEDIATE SQL_STATEMENT INTO LOOP_CASES;
          TOTAL_CASES := TOTAL_CASES + LOOP_CASES;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(TOTAL_CASES);
  
  END;
  </code></pre>
  
  <p>Above query is giving me output but I want to use Bind Variable for date parameters, How can it be done?</p>
  
  <p><strong>Solutions I tried:</strong></p>
  
  <pre><code>DECLARE
      TABLE_NAME VARCHAR2(100);
      SQL_STATEMENT VARCHAR2(2000);
      TOTAL_CASES NUMBER(10) := 0;
      LOOP_CASES NUMBER(10) := 0;
      START_DATE Date:= TO_CHAR(:start_dt);
      END_DATE Date := TO_CHAR(:end_dt);
  BEGIN
  
      FOR MY_ROW IN 00..99
      LOOP
          TABLE_NAME:= 'history'||TRIM(TO_CHAR(MY_ROW,'00'));
  
          SQL_STATEMENT:='SELECT COUNT(DISTINCT USR_ID) 
          FROM ' || TABLE_NAME || '@hr WHERE ATTRIBUTE_ID = 109 
  
          AND OLD_VALUE IS NULL AND UPDATED_BY_SCREEN = ''CRM'' AND TRUNC(UPDATE_DATE) between TO_DATE('||START_DATE||') and TO_DATE('||END_DATE||')';
          EXECUTE IMMEDIATE SQL_STATEMENT INTO LOOP_CASES;
          TOTAL_CASES := TOTAL_CASES + LOOP_CASES;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(TOTAL_CASES);
  
  END;
  </code></pre>
  
  <p>But it shows <em>ORA-00907: Missing Right Parenthesis</em> </p>
  
  <p><strong>Another Solution that I tried:</strong></p>
  
  <pre><code>DECLARE
      TABLE_NAME VARCHAR2(100);
      SQL_STATEMENT VARCHAR2(2000);
      TOTAL_CASES NUMBER(10) := 0;
      LOOP_CASES NUMBER(10) := 0;
      START_DATE Date:= TO_DATE(:start_dt,'dd-mm-yyyy');
      END_DATE Date := TO_DATE(:end_dt,'dd-mm-yyyy');
  BEGIN
  
      FOR MY_ROW IN 00..99
      LOOP
          TABLE_NAME:= 'history'||TRIM(TO_CHAR(MY_ROW,'00'));
  
          SQL_STATEMENT:='SELECT COUNT(DISTINCT USR_ID) 
          FROM ' || TABLE_NAME || '@hr WHERE ATTRIBUTE_ID = 109 
  
          AND OLD_VALUE IS NULL AND UPDATED_BY_SCREEN = ''CRM'' AND TRUNC(UPDATE_DATE) between '||START_DATE||' and'||END_DATE||'';
          EXECUTE IMMEDIATE SQL_STATEMENT INTO LOOP_CASES;
          TOTAL_CASES := TOTAL_CASES + LOOP_CASES;
      END LOOP;
      DBMS_OUTPUT.PUT_LINE(TOTAL_CASES);
  
  END;
  </code></pre>
  
  <p>But it gives ERROR <em>ORA-00905: Missing Keyword</em></p>
  
  <p>How can it be solved?</p>