Geek Answers Handbook

How to find if a sequence exists using PL / SQL

I need to find using PL / SQL if a specific sequence, for example, for example. The output is MY_SEQ. If a sequence exists, drop it and create a new one, or simply create a new sequence.

eg. (Pseudocode)

IF EXISTS(MY_SEQ) THEN BEGIN DROP SEQUENCE MY_SEQ; CREATE SEQUENCE MY_SEQ... END; ELSE BEGIN CREATE SEQUENCE MY_SEQ; END;
+6
oracle plsql
source share
2 answers

you can check the look of the ALL_SEQUENCES dictionary (or USER_SEQUENCES if the executable user is the owner), for example:

 BEGIN FOR cc IN (SELECT sequence_name as sequence_exists FROM all_sequences WHERE sequence_owner = :seq_owner AND sequence_name = :seq_name) LOOP -- sequence exists, drop it (at most there will be *one* sequence) EXECUTE IMMEDIATE 'DROP SEQUENCE XXX'; END LOOP; -- create sequence EXECUTE IMMEDIATE 'CREATE SEQUENCE XXX'; END;
+8
source share

I have a few ideas for this (all untested):

1) Oracle usually supports something like CREATE OR REPLACE

2) Use one of the system views from the SYS user to check with SELECT if a sequence exists.

3) Use execute_immediate with a BEGIN .. EXCEPTION ... END block to delete an object. If it does not exist, an error should occur that you can ignore.

0
source share

More articles:


All Articles