Error in Oracle (PLS-00103: Encountered the symbol "="...) trying to select into a variable for use in a cursor

Question

I'm creating a procedure. I'm selecting a max date from a table into a var, and then intending to use that var in sql for a cursor. It looks like:

l_max_update_date      DATE;
--Param var
l_max_update_date := NULL;
SELECT max(pivlog.last_update_date) as maxdate into l_max_update_date
FROM piv_verification_log pivlog;

...and then...

--No data in log table?  Create a max_update_date
IF l_max_update_date IS NULL
THEN
l_max_update_date := TO_TIMESTAMP('2014/SEP/1 00:00:00', 'YYYY/MON/DD HH24:MI:SS');
END IF;

Finally...

--Get affected employees (those who have certified since the max(last_update_date) listed in the log table)
CURSOR affected_employees_cursor
IS
SELECT    [columns]
FROM      [tables]
WHERE LAST_UPDATE_DATE > l_max_update_date;

But, whenever I compile, I get this error message:

[Error] PLS-00103 (47: 22): PLS-00103: Encountered the symbol "=" when expecting one of the following: constant exception table long double ref char time timestamp

which points at this line:

l_max_update_date := NULL;

I appreciate your insight. I'm thinking it has to do with the order or location in the procedure where I'm defining the var and cursor(?).

Thank you.

Answer 1

Further to Bob's answer, the cursor will use whatever value l_max_update_date has at the point the cursor is opened, so it doesn't have to be set before the cursor is declared.

If you'd prefer that to be more obvious in your code then you could also pass the date to the cursor as a parameter:

  CURSOR affected_employees_cursor (p_last_update_date DATE) IS
    SELECT    [columns]
    FROM      [tables]
    WHERE LAST_UPDATE_DATE > p_max_update_date;

and then call it with:

  OPEN affected_employees_cursor (l_max_update_date);

Or you could combine the lookup-up into the cursor definition, as long as you only open it once, and skip the separate look-up and check:

CREATE OR REPLACE PROCEDURE SOME_PROC AS
  CURSOR affected_employees_cursor IS
    SELECT    [columns]
    FROM      [tables]
    WHERE LAST_UPDATE_DATE > (
      SELECT COALESCE(MAX(pivlog.last_update_date), DATE '2014-09-01')
      FROM piv_verification_log pivlog
    );

  rowAffected_employee affected_employees_cursor%ROWTYPE;
BEGIN
  OPEN affected_employees_cursor;

  LOOP
    FETCH affected_employees_cursor
      INTO rowAffected_employee;
    EXIT WHEN affected_employees_cursor%NOTFOUND;

    -- do something useful with the data fetched from the cursor
  END LOOP;

  CLOSE affected_employees_cursor;
END SOME_PROC;
/

Or even simpler use an implicit cursor loop:

CREATE OR REPLACE PROCEDURE SOME_PROC AS
BEGIN
  FOR rowAffected_employee In (
    SELECT    [columns]
    FROM      [tables]
    WHERE LAST_UPDATE_DATE > (
      SELECT COALESCE(MAX(pivlog.last_update_date), DATE '2014-09-01')
      FROM piv_verification_log pivlog
    )
  )
  LOOP
    -- do something useful with the data fetched from the cursor
  END LOOP;
END SOME_PROC;
/

Of course, depending on what you're doing with the data fetched form the cursor, this might be something that doesn't need PL/SQL at all and could be done in plain SQL.