how to bind varible in sql/plsql

Question

My query is

with dates_table as (
                 SELECT to_date(:begin_date, 'dd/mm/yyyy') + ROWNUM - 1 cal_day
                        FROM dual
                  CONNECT BY LEVEL <= to_date(:end_date, 'dd/mm/yyyy') - to_date(:begin_date, 'dd/mm/yyyy') + 1)
    SELECT '1' AS ID,
           'bank' AS DESC,
           cal_day AS dates,
           (SUM (
               CASE
                  WHEN S_DATE BETWEEN ADD_MONTHS (cal_day, 0) - 13
                                  AND ADD_MONTHS (cal_day, 0) - 7
                  THEN
                     VOLUME
                  ELSE
                     0
               END))
              LAST_14_days,
           (SUM (
               CASE
                  WHEN S_DATE BETWEEN ADD_MONTHS (cal_day, 0) - 6
                                  AND ADD_MONTHS (cal_day, 0)
                  THEN
                     VOLUME
                  ELSE
                     0
               END))
              last 7day
      FROM abc, day
      where day.cal_day between '13-NOV-16' and '22-MAR-17'
      group by cal_day
      order by cal_day

Answer 1

you may replace you parametric sql in the upper part with the following :

SELECT to_date('&&begin_date', 'dd/mm/yyyy') + ROWNUM - 1 cal_day
                        FROM dual
                  CONNECT BY LEVEL <= to_date('&&end_date', 'dd/mm/yyyy') - to_date('&&begin_date', 'dd/mm/yyyy') + 1)
....

For numeric values you may directly define variables without single quotes'', but you should include quotes for date or string type parameters. In each case use double ampersand so that repeating parameters( you have more than one :begin_date ) don't prompt you(normally, you can use single ampersand also, but in this case you'll be prompted for each parameter even if they're the same )