Strange behaviours with oracle nested cursors

Question

Below is stored procedure I have written which used nested cursor.

create or replace
PROCEDURE SP_RUN_EMPLOYEE_UPDATES 
(
  IN_DATE IN VARCHAr2
) 
IS

update_sql varchar2(4000); 

employee_id BI_EMPLOYEE_UPDATE.employee_id%TYPE;   

effective_date date ; 
created_by number;
created_on date;
comments varchar2(4000);

CURSOR 
  employees 
IS
  SELECT distinct(employee_id) FROM BI_EMPLOYEE_UPDATE WHERE EFFECTIVE_DATE = to_date(IN_DATE,'dd-mm-yy') AND EXECUTED = 'N' AND ACTIVITY_ID = '0';

CURSOR 
  e_updates 
IS
  SELECT * FROM BI_EMPLOYEE_UPDATE WHERE EFFECTIVE_DATE = to_date(IN_DATE,'dd-mm-yy') AND EXECUTED = 'N' AND ACTIVITY_ID = '0' and employee_id = employee_id ;

BEGIN

OPEN employees;

    LOOP

      effective_date := '';
      created_by := '';
      created_on := '';
      comments := '';
      employee_id := ''; 

      FETCH employees into employee_id;
      EXIT WHEN employees%NOTFOUND;

        update_sql :=  'UPDATE BI_EMPLOYEE SET ';
        FOR e_update in e_updates
          LOOP

            select comments, effective_date , changed_by, changed_on into  comments, effective_date , created_by, created_on 
            from bi_employee_update where EMPLOYEE_UPDATE_ID = e_update.EMPLOYEE_UPDATE_ID; 

            update_sql := update_sql || e_update.column_name || ' = ''' || e_update.new_value || ''' , ' ; 

            UPDATE BI_EMPLOYEE_UPDATE
            SET 
              EXECUTED = 'Y'
            WHERE 
              EMPLOYEE_UPDATE_ID = e_update.EMPLOYEE_UPDATE_ID ;

          END LOOP;

          update_sql := update_sql || ' comments  = ''' || comments || ''', updated_by  = ''' || created_by  || ''',  updated_on  = ''' || created_on ||  ''',  effective_date = ''' || effective_date  || '''';  
          update_sql := update_sql || ' WHERE emp_id = ' || employee_id ;  

       dbms_output.put_line('KKKK '||update_sql);
        execute immediate update_sql ; 

    END LOOP;
    CLOSE employees;

 END;

The problem is in the second cursor where I get the data of all the previous cursors combined.

e.g. if first iteration shoud return a, second should return b. But in actual first iteration returns a, b and second also returns a,b.

Below is the dynamic query generated which is exactly same.

1st iteration

EXPECTED (CORRECT):

UPDATE BI_EMPLOYEE SET EMPLOYEE_ID = '1111111111111' , PP_NUMBER = '22222222222' ,
    CORPORATE_TITLE_ID = '2' ,  comments  = 'c11', updated_by  = '361',
    updated_on  = '12-SEP-12',  effective_date = '25-SEP-12' WHERE emp_id = 18010

ACTUAL (WRONG):

UPDATE BI_EMPLOYEE SET EMPLOYEE_ID = '1111111111111' , PP_NUMBER = '22222222222' ,
    CORPORATE_TITLE_ID = '2' , LASTNAME = 'Ll22 edited ' , OFFSHORE_ONSHORE = '1' ,
    ONSHORE_REGION = '1' , ONSHORE_DESK_MANAGER = 'henrry ' ,
    comments  = 'cc 33 33', updated_by  = '361',  updated_on  = '12-SEP-12',
    effective_date = '25-SEP-12' WHERE emp_id = 18010

2nd iteration

EXPECTED (CORRECT):

UPDATE BI_EMPLOYEE SET LASTNAME = 'Ll22 edited ' , OFFSHORE_ONSHORE = '1' ,
    ONSHORE_REGION = '1' , ONSHORE_DESK_MANAGER = 'henrry ' ,
    comments  = 'cc 33 33', updated_by  = '361',  updated_on  = '12-SEP-12',
    effective_date = '25-SEP-12' WHERE emp_id = 18009

ACTUAL (WRONG):

UPDATE BI_EMPLOYEE SET EMPLOYEE_ID = '1111111111111' , PP_NUMBER = '22222222222' ,
    CORPORATE_TITLE_ID = '2' , LASTNAME = 'Ll22 edited ' ,
    OFFSHORE_ONSHORE = '1' , ONSHORE_REGION = '1' ,
    ONSHORE_DESK_MANAGER = 'henrry ' ,  comments  = 'cc 33 33',
    updated_by  = '361',  updated_on  = '12-SEP-12',
    effective_date = '25-SEP-12'
    WHERE emp_id = 18009

Why is this happening?

score 1 · Accepted Answer · edited May 23 '17 at 10:25

As mentioned in a comment on your previous question, your second cursor is not restricted to the employee found by the first cursor because you have no link between them. Where you have:

and employee_id = employee_id

... both of those refer to the table column so it doesn't act as a filter at all. You've given your local variable the same name, which confuses things enough, but it's out of scope anyway - this cursor has no visibility of the variable value set in the main body of the procedure.

You need to do something like:

CREATE OR REPLACE PROCEDURE sp_run_employee_updates (p_date IN DATE) IS
    update_sql varchar2(4000);
    first_update boolean;

    CURSOR c_employees IS
        SELECT DISTINCT employee_id
        FROM bi_employee_update
        WHERE effective_date = p_date
        AND executed = 'N' 
        AND activity_id = '0';

    CURSOR c_updates(cp_employee_id bi_employee_update.employee_id%TYPE) IS
        SELECT *
        FROM bi_employee_update
        WHERE effective_date = p_date
        AND executed = 'N' 
        AND activity_id = '0'
        AND employee_id = cp_employee_id
        FOR UPDATE;

BEGIN
    -- loop around all employees with pending records
    FOR r_employee IN c_employees LOOP
        -- reset the update_sql variable to its base
        update_sql :=  'UPDATE BI_EMPLOYEE SET ';
        -- reset the flag so we only add the comments etc. on the first record
        first_update := true;

        -- loop around all pending records for this employee
        FOR r_update IN c_updates(r_employee.employee_id) LOOP
            -- add the comments etc., only for the first update we see
            if first_update then
                update_sql := update_sql
                    || ' comments = ''' || r_update.comments || ''','
                    || ' updated_by = ''' || r_update.changed_by  || ''','
                    || ' updated_on  = ''' || r_update.changed_on ||  ''','
                    || ' effective_date = ''' || r_update.effective_date  || '''';  
                first_update := false;
            end if;

            -- add the field/value from this record to the variable
            update_sql := update_sql || ', '
                || r_update.column_name || ' = ''' || r_update.new_value || '''' ; 

            -- mark this update as executed
            UPDATE bi_employee_update
            SET executed = 'Y'
            WHERE CURRENT OF c_updates;

        END LOOP;

        -- apply this update to the bi_employee record
        update_sql := update_sql || ' WHERE emp_id = ' || r_employee.employee_id;

        DBMS_OUTPUT.PUT_LINE(update_sql);
        EXECUTE IMMEDIATE update_sql; 
    END LOOP;
END sp_run_employee_updates;

The important difference, really, is that the second cursor now has a parameter, and the employee ID from the first cursor is passed as that parameter.

Also, IN_DATE is declared as a date, so you don't need to pass it through TO_DATE(). There are going to be implicit date conversions in other places (effective dates etc.) because you're treating them as strings, but as long as they don't have time components this probably won't break anything as it should be consistent within the procedure.

Strange behaviours with oracle nested cursors

1 Answers1

Linked