CODEWITHSUNDEEP
oracle-databaseplsql
Narandran
Narandran

Reputation: 1

Not Able to create and drop sequence in an anonymous block in PLSQL

Today I have tried to create a sequence inside anonymous block. My requirement is when sequence is reached the maxvalue then it should be dropped automatically. So, I have executed the below code but it shows error like "PL/SQL: ORA-02289: sequence does not exist". 

CODE:

DECLARE
V_NUM NUMBER:=0;
V_QUERY VARCHAR2(2000);
CNT NUMBER := 0;
BEGIN
V_QUERY:= 'CREATE SEQUENCE SEQ_GEN START WITH 100 INCREMENT BY 10 MAXVALUE 200';
EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_GEN';
EXECUTE IMMEDIATE V_QUERY;

FOR I IN 1..11 LOOP
SELECT SEQ_GEN.NEXTVAL INTO V_NUM FROM DUAL;
DBMS_OUTPUT.PUT_LINE(V_NUM);
IF V_NUM >= 200 THEN
EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_GEN';
DBMS_OUTPUT.PUT_LINE('sequence has reached maximum value');
END IF;
END LOOP;

SELECT COUNT(1) INTO CNT FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'SEQ_GEN';
DBMS_OUTPUT.PUT_LINE(CNT);

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Can anyone assist me to solve this issue?

Upvotes: 0

Views: 2514

Answers (2)

Mottor
Mottor

Reputation: 1948

The problem is, that Oracle try to parse your anonymous block, before to execute. If the sequence does not exist, than there will be error ORA-02289 on this line:

SELECT SEQ_GEN.NEXTVAL INTO V_NUM FROM DUAL;

and you cannot catch it, because happens on parse. You should get nextval dynamicly. If the sequence exists, there will be error on this line:

EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_GEN';

but this error will be caught in WHEN OTHERS. Another thing is that you can use direct ORA-08004 when the max value is reached.

DECLARE
   sequence_reached_max_value   EXCEPTION;
   PRAGMA EXCEPTION_INIT (sequence_reached_max_value, -8004);
   sequence_exists              EXCEPTION;
   PRAGMA EXCEPTION_INIT (sequence_exists, -955);
   V_NUM                        NUMBER := 0;
   V_QUERY                      VARCHAR2 (2000);
   CNT                          NUMBER := 0;
BEGIN
   V_QUERY := 'CREATE SEQUENCE SEQ_GEN START WITH 100 INCREMENT BY 10 MAXVALUE 200';

   BEGIN
      EXECUTE IMMEDIATE V_QUERY;
   EXCEPTION
      WHEN sequence_exists THEN
         EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_GEN';
         EXECUTE IMMEDIATE V_QUERY;
   END;

   BEGIN
      FOR I IN 1 .. 21
      LOOP
         EXECUTE IMMEDIATE 'SELECT SEQ_GEN.NEXTVAL FROM DUAL' into V_NUM;

         DBMS_OUTPUT.PUT_LINE (V_NUM);
      END LOOP;
   EXCEPTION
      WHEN sequence_reached_max_value THEN
         EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_GEN';
         DBMS_OUTPUT.PUT_LINE ('sequence has reached maximum value');
   END;

   SELECT COUNT (1)
     INTO CNT
     FROM USER_SEQUENCES
    WHERE SEQUENCE_NAME = 'SEQ_GEN';

   DBMS_OUTPUT.PUT_LINE (CNT);
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;

Upvotes: 2

XING
XING

Reputation: 9886

Try this. 

DECLARE
   V_NUM     NUMBER := 0;
   V_QUERY   VARCHAR2 (2000);
   CNT       NUMBER := 0;
BEGIN
   V_QUERY :='CREATE SEQUENCE SEQ_GEN START WITH 100 INCREMENT BY 10 MAXVALUE 200';

   EXECUTE IMMEDIATE V_QUERY; 

   FOR I IN 1 .. 11
   LOOP      
      V_QUERY :='SELECT SEQ_GEN.NEXTVAL FROM DUAL';

      EXECUTE IMMEDIATE V_QUERY INTO V_NUM;

      DBMS_OUTPUT.PUT_LINE (V_NUM);

      IF V_NUM >= 200
      THEN
         EXECUTE IMMEDIATE 'DROP SEQUENCE SEQ_GEN';

         DBMS_OUTPUT.PUT_LINE ('sequence has reached maximum value');
      END IF;
   END LOOP;

   SELECT COUNT (1)
     INTO CNT
     FROM USER_SEQUENCES
    WHERE SEQUENCE_NAME = 'SEQ_GEN';

   DBMS_OUTPUT.PUT_LINE (CNT);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/

Upvotes: 0

Related Questions