APEX

Hi, I'm trying to run this process that calls a stored procedure that returns the oldest ORA_ROWSCN in the switch_messages table that can be undone (can undo within 1 hour time period). I'm getting this error message: ORA-08181: specified number is not a valid system change number.

I think it's not returning this correctly: return_scn := gc_rec.ORA_ROWSCN;

This is the on Submit process:

<b>
DECLARE
   A VARCHAR2(255);
BEGIN
   A := ipfFlashback.perform_cancel(TO_NUMBER(:P0_PROFILE_NAME));
END;</b>

Here's the stored procedure:
[PRE]<b>
FUNCTION perform_cancel ( p_system_id NUMBER )
RETURN VARCHAR2 IS

--DECLARE
CURSOR gc_sw_msgs IS
select ORA_ROWSCN, switch_messages.*
from switch_messages
where system_id = p_system_id
order by switch_messages_id DESC;

return_scn NUMBER(10);

BEGIN

  return_scn :=0 ;

  FOR gc_rec IN gc_sw_msgs LOOP

    --Find the earliest SCN
    IF (SYSTIMESTAMP - SCN_TO_TIMESTAMP(gc_rec.ORA_ROWSCN)) < INTERVAL '60' MINUTE
    THEN
        IF return_scn = 0 THEN
            --first find
            return_scn := gc_rec.ORA_ROWSCN;
        ELSIF gc_rec.ORA_ROWSCN < return_scn THEN
            --subsequent finds
            return_scn := gc_rec.ORA_ROWSCN;
        ELSE
            --here because someone may have changed a service interrupt flag
            EXIT;
        END IF;
    ELSE
        EXIT;
    END IF;

  END LOOP;

  RETURN (cancel_pending (return_scn, p_system_id));

END perform_cancel;

</b>