EXECUTE IMMEDIATE Only Works Sometimes

Question

I currently have this plsql block to drop and create a sequence:

BEGIN 
  EXECUTE IMMEDIATE 'DROP SEQUENCE my_sequence';

  EXECUTE IMMEDIATE 'CREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 1 ORDER CYCLE MAXVALUE 999';

  EXCEPTION
  WHEN OTHERS THEN
      IF SQLCODE = -2289 THEN
       EXECUTE IMMEDIATE 'CREATE SEQUENCE my_sequence START WITH 1 INCREMENT BY 1 ORDER CYCLE MAXVALUE 999';
      ELSE
        RAISE;
  END IF;

END;
/

After running this, the sequence is created usually. But sometimes it is not.

Would anyone know why?

Thanks

You are swallowing all errors - you should at least re-throw any exception that is not -2289 so that you can see what's going wrong — , Jan 26 '18 at 14:21
There are other ways to reset it, particularly in 12c. But anyway... what happens when it is not created? Do you see an error? [Er, no, because you're swallowing them...!] And could two sessions be running this at the same time? — Alex Poole, Jan 26 '18 at 14:22
@a_horse_with_no_name I have updated to code to throw the error if it is not 2289. It does not appear there is a different exception being raised when I have tested. — , Jan 26 '18 at 14:27
@AlexPoole, can you please elaborate as documentation says *To restart the sequence at a different number, you must drop and re-create it.* — Wernfried Domscheit, Jan 26 '18 at 14:28
@WernfriedDomscheit - ah... I'd [seen it mentioned](https://stackoverflow.com/a/28714053/266304) a few times but hadn't had reason to use it; I hadn't realised [it isn't supported](https://stackoverflow.com/a/19673327/266304). So I take that back. The `alter increment` approach works though, and would stop any dependant objects being invalidated (I think; haven't checked). — Alex Poole, Jan 26 '18 at 14:32

score 0 · Accepted Answer · answered Jan 26 '18 at 14:25

0

I cannot imagine any situation where you need to reset a CYCLE sequence. Anyway, I would do it like this:

BEGIN 
    FOR aSeq IN (SELECT * FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'MY_SEQUENCE') LOOP
        EXECUTE IMMEDIATE 'DROP SEQUENCE '||aSeq.SEQUENCE_NAME;
    END LOOP;

    EXECUTE IMMEDIATE 'CREATE SEQUENCE MY_SEQUENCE START WITH 1 INCREMENT BY 1 ORDER CYCLE MAXVALUE 999';
END;
/

answered Jan 26 '18 at 14:25

Wernfried Domscheit

54,457
9
76
110

Thanks, this works. I bet it was something with the exception handling I was doing. – Jan 26 '18 at 14:41

EXECUTE IMMEDIATE Only Works Sometimes

1 Answers1