Using variables in cursor declaration query in oracle

Question

Is there a way to use a variable in cursor declaration sql ? Example-

create or replace PROCEDURE PRD (IN_VAR  IN VARCHAR2)
IS

  V_V1 TABLE.DATE_COL%TYPE;

   /* Cursor decleration */
   CURSOR CUR_DUMMY
   IS
        SELECT COL1,COL2,COL3 
          FROM TABLE 
             WHERE DATE_COL BETWEEN V_V1 
                    AND V_V1+1;

BEGIN
   FOR REC  IN CUR_DUMMY
   LOOP

    SELECT TO_DATE(TO_CHAR(sysdate, 'DD-MON-YY') ||' '||(SELECT TO_CHAR(DAY_BEGIN,'HH24:MI:SS') FROM TABLE2),'DD-MON-YY HH24:MI:SS') INTO  V_V1 from DUAL;   

--  other stuffs
    END LOOP;
END;

Here the cursor doesn't fetch the required records. Can I assign the V_V1 value somewhere before the cursor declaration? Though I can get dates in the cursor sql itself but there will be redundant select query. Is there a better way ?

Solution 1

You need to assign value to V_V1 before looping the cursor. Also, you need to define the variable in the cursor definition (cursor c_dummy (V_V1 date)) and pass it when calling the cursor FOR REC IN CUR_DUMMY(V_V1)

    create or replace PROCEDURE PRD (IN_VAR  IN VARCHAR2)
IS

  V_V1 TABLE.DATE_COL%TYPE;

   /* Cursor decleration */
   CURSOR CUR_DUMMY(V_V1 date)
   IS
        SELECT COL1,COL2,COL3 
          FROM TABLE 
             WHERE DATE_COL BETWEEN V_V1 
                    AND V_V1+1;

BEGIN
   SELECT TO_DATE(TO_CHAR(sysdate, 'DD-MON-YY') ||' '||(SELECT TO_CHAR(DAY_BEGIN,'HH24:MI:SS') FROM TABLE2),'DD-MON-YY HH24:MI:SS') INTO  V_V1 from DUAL;   
   FOR REC  IN CUR_DUMMY(V_V1)
   LOOP

--  do stuffs

    END LOOP;
END;

Solution 2

As someone has already said you need to move your variable V_V1 outside of the FOR loop. Once the cursor is opened the result set is fixed; so changing V_V1 inside the loop won't make any difference.

The code below also gets away from selecting from dual which you don't need to do (and should avoid for simplicity and performance) and names the variables with a bit more meaning (hard to do when I don't know what the code is for - but I'm hoping you re-named them for your post and that they aren't like this in your actual code)

Also, again with no knowledge of you codes purpose, remember that BETWEEN is inclusive so BETWEEN the_date AND the_date +1 is possibly including records you don't want. I'm guessing a bit but >= the_date AND < the_date + 1 is possibly the correct clause.

As best practice recommendations, may I also suggest that if you aren't already putting your procedure in a package you do so, and that comments in code should only be used for why the code is the way it is not what it is. i.e. a comment that says cursor declaration is of no benefit to the readability of your code. Depending on the processing in the loop you should also consider using BULK COLLECT which is well documented elsewhere.

CREATE OR REPLACE PROCEDURE my_procedure (in_var IN VARCHAR2)
IS    
   today_begin TABLE.DATE_COL%TYPE;
   the_day_begin TABLE2.DAY_BEGIN%TYPE;

   CURSOR todays_records( the_date DATE )
   IS
        SELECT COL1,COL2,COL3 
        FROM   TABLE 
        WHERE  DATE_COL BETWEEN the_date AND the_date + 1;

BEGIN

    SELECT DAY_BEGIN 
    INTO the_day_begin
    FROM TABLE2;

    today_begin := TO_DATE(TO_CHAR(sysdate, 'DD-MON-YY') ||' '|| TO_CHAR(the_day_begin,'HH24:MI:SS'),'DD-MON-YY HH24:MI:SS') 

    FOR rec IN todays_records( today_begin )
    LOOP
      --  other stuffs
    END LOOP;

END;

Comments

[Maverick]

thank you for all your suggestions. Yes I have re-named them for this post.