How to use Date type bind variable in ORACLE SQL Statement?

Question

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?

My Query:

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;

Above query is giving me output but I want to use Bind Variable for date parameters, How can it be done?

Solutions I tried:

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;

But it shows ORA-00907: Missing Right Parenthesis

Another Solution that I tried:

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;

But it gives ERROR ORA-00905: Missing Keyword

How can it be solved?

google for "tom kyte bind variables", he's a guru of Oracle. — Barbaros Özhan
– Barbaros Özhan, Commented Sep 26, 2017 at 5:22
@BarbarosÖzhan Thanks, buddy, But unable to find the questions related to my query. — Shivam Sharma
– Shivam Sharma, Commented Sep 26, 2017 at 5:52

Dmitriy · Accepted Answer · 2017-09-26 09:22:03Z

1

There could be two possible ways to use bind variables. The first way - if you need to set parameter's values inside the anonymous block:

DECLARE
    TABLE_NAME VARCHAR2(100);
    SQL_STATEMENT VARCHAR2(2000);
    TOTAL_CASES NUMBER(10) := 0;
    LOOP_CASES NUMBER(10) := 0;
    -- here you calculate values to use in dynamic SQL:
    start_date date := TO_DATE('15-MAY-2016','dd-mom-yyyy');
    end_date date := TO_DATE('21-MAY-2016','dd-mon-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 :P_START and :P_END'; 
          -- there are 2 parameters above - :P_START and :P_END
        EXECUTE IMMEDIATE SQL_STATEMENT INTO LOOP_CASES 
            using start_date, end_date;
        TOTAL_CASES := TOTAL_CASES + LOOP_CASES;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(TOTAL_CASES);
END;

The second way - when the anonymous block has to take parameters too:

DECLARE
    TABLE_NAME VARCHAR2(100);
    SQL_STATEMENT VARCHAR2(2000);
    TOTAL_CASES NUMBER(10) := 0;
    LOOP_CASES NUMBER(10) := 0;
    -- one way is when you receive string dates:
    start_date date := TO_DATE(:P_OUTER_START_DATE,'dd-mom-yyyy');
    end_date date := TO_DATE(:P_OUTER_END_DATE,'dd-mon-yyyy');
    -- or another way if you can set parameter in date format outside:
    start_date date := :P_OUTER_START_DATE;
    end_date date := :P_OUTER_END_DATE;
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 :P_START and :P_END'; 
          -- there are 2 parameters above - :P_START and :P_END
        EXECUTE IMMEDIATE SQL_STATEMENT INTO LOOP_CASES 
            using start_date, end_date;
        TOTAL_CASES := TOTAL_CASES + LOOP_CASES;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(TOTAL_CASES);
END;

There are two sets of parameters: the first pair is :P_OUTER_START_DATE and :P_OUTER_END_DATE, they are parameters for the whole block; the second pair is :P_START and :P_END, they are parameters for inner SQL query, which is executed inside the block. In any case, I would recommend to use parameters inside execute immediate too.

edited Sep 26, 2017 at 9:22

answered Sep 26, 2017 at 8:02

Dmitriy

5,58512 gold badges27 silver badges39 bronze badges

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

3 Comments

Shivam Sharma Over a year ago

It doesn't work. It's still using the hardcoded value of the date. I want to use bind variables for it. How will it assign values to P_START and P_END? does it depend on the position and order of variables?

Dmitriy Over a year ago

@ShivamSharma You want to set parameters outside this block?

Shivam Sharma Over a year ago

I just wanted to give date dynamically using bind variables. Please check my answer as I have found the solution there.

Shivam Sharma · Accepted Answer · 2017-09-26 08:55:18Z

0

I got the solution by declaring the variables assigned to Bind Variables. The main problem was that it's the Date type variable so I need to give special attention to the inverted commas.

See The Solution Below:

set serveroutput on;
set echo on;
DECLARE
   TABLE_NAME VARCHAR2(100);
   SQL_STATEMENT VARCHAR2(2000);
   TOTAL_CASES NUMBER(10) := 0;
   LOOP_CASES NUMBER(10) := 0;
   START_DATE DATE := to_date(:st_date,'DD-MM-YYYY');  
    END_DATE DATE:= to_date(:en_date,'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;
/

Pay the special attention to the ' in SQL_STATEMENT.

=====================================================================

Edit:

Using only Explicit conversion (Better Solution):

SET serveroutput ON;
SET echo ON;
DECLARE
  TABLE_NAME    VARCHAR2(100);
  SQL_STATEMENT VARCHAR2(2000);
  TOTAL_CASES   NUMBER(10)   := 0;
  LOOP_CASES    NUMBER(10)   := 0;
  START_DATE    VARCHAR2(20) := TO_CHAR(:st_date);
  END_DATE      VARCHAR2(20) := TO_CHAR(:en_date);
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 ||''',''dd-mm-yyyy'') and to_date('''||END_DATE||''',''dd-mm-yyyy'')';
    EXECUTE IMMEDIATE SQL_STATEMENT INTO LOOP_CASES;
    TOTAL_CASES := TOTAL_CASES + LOOP_CASES;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE(TOTAL_CASES);
END;
/

edited Sep 26, 2017 at 8:55

answered Sep 26, 2017 at 7:58

Shivam Sharma

1,62722 silver badges35 bronze badges

4 Comments

Dmitriy Over a year ago

Here you concatenate the date variable (start_date) with the string (SQL_STATEMENT), what means implicit data conversion and potential problems and bugs. Normally you should avoid this.

Shivam Sharma Over a year ago

Edited my answer for it, now using only explicit conversions.

Dmitriy Over a year ago

This will work, but there are ways to make it simpler.

Shivam Sharma Over a year ago

@Dmitry Yeah, Yours is a little bit simpler(2nd one)... Thanks for your response.

Barbaros Özhan · Accepted Answer · 2017-09-26 09:25:55Z

0

You may try this:

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');
    v_ubs         varchar2(50):='CRM'
    v_ai          pls_integer := 109;
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 = :ai 
                           AND OLD_VALUE IS NULL 
                           AND UPDATED_BY_SCREEN = :ubs 
                           AND TRUNC(UPDATE_DATE) between :START_DATE and :END_DATE ';
        EXECUTE IMMEDIATE SQL_STATEMENT using v_ai, v_ubs, START_DATE, END_DATE;
        TOTAL_CASES := TOTAL_CASES + LOOP_CASES;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE(TOTAL_CASES);
END;

edited Sep 26, 2017 at 9:25

answered Sep 26, 2017 at 6:26

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

2 Comments

Shivam Sharma Over a year ago

Not Working, Giving ERROR in INTO saying

ORA-06550: line 20, column 92: PLS-00103: Encountered the symbol "INTO" when expecting one of the following:     . ( , * @ % & = - + ; < / > at in is mod remainder not rem    return returning <an exponent (**)> <> or != or ~= >= <= <>    and or like like2 like4 likec between || multiset member    submultiset The symbol "return" was substituted for "INTO" to continue. 06550. 00000 -  "line %s, column %s:\n%s" *Cause:    Usually a PL/SQL compilation error. *Action:

Barbaros Özhan Over a year ago

i've edited by removing "INTO LOOP_CASES" part, and took table_name outside. Try again.

Collectives™ on Stack Overflow

How to use Date type bind variable in ORACLE SQL Statement?

3 Answers 3

3 Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related