Oracle - PLS-00222

Question

I have the following piece of code which is returning PLS-00222. So I want to compare the dates of an "old" id with a "new" id retrieved from cur_c1.

Here is the cursor where cur_table records come from:

    CURSOR table_cur IS
        SELECT
            NEW_ID,
            OLD_ID    
        FROM
            TABLE_C
        WHERE
            C_ID = in_parameter_id; --This is input for the procedure

    CURSOR cur_c1 (c_in_id NUMBER) IS
        SELECT 
               FIELD_DATE
          FROM
               TABLE_D
          WHERE
               FIELD_ID = c_in_id;

  FOR cur_table IN table_cur LOOP
   ...stuff...;

    FOR c_cur IN cur_c1(cur_table.NEW_ID) LOOP
        IF c_cur.field_date > cur_c1(cur_table.OLD_ID).field_date
        THEN
            v_exist := 'Y';
        END IF;
    END LOOP;
   END LOOP;

How can I achieve my desired result?

Error:

2593/56 PLS-00222: no function with name 'cur_c1' exists in this scope
2593/17 PL/SQL: Statement ignored

Answer 1

I suggest that using cursors here is inefficient. Instead I suggest the following:

FOR cur_table IN table_cur LOOP
  ...stuff...;


  SELECT d_old.FIELD_DATE,
         d_new.FIELD_DATE
    INTO dtOld_field_date,
         dtNew_field_date
    FROM DUAL
    LEFT OUTER JOIN TABLE_D d_old
      ON d_old.FIELD_ID = cur_table.OLD_ID
    LEFT OUTER JOIN TABLE_D d_new
      ON d_new.FIELD_ID = cur_table.NEW_ID;

  IF dtNew_field_date > dtOld_field_date THEN
    v_exist := 'Y';
  END IF;
END LOOP;

Best of luck.