Before launching the script, I want to check if there is a sequence in the database, and if there is, drop it. But DROP SEQUENCE does not work in PLSQL, and IF does not work in SQL. How to be? Now at the beginning of the script attributed:

DECLARE V_TEMP_NUM NUMBER(9) := 0; BEGIN SELECT COUNT(*) INTO V_TEMP_NUM FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TMM_TEMP10_SEQ'; IF V_TEMP_NUM > 0 THEN DROP SEQUENCE TMM_TEMP10_SEQ; END IF; CREATE SEQUENCE TMM_TEMP10_SEQ MINVALUE 0 START WITH 10 INCREMENT BY 10 CACHE 20; END;

As I said, swears at DROP.

  • one
    execute immediate 'DROP SEQUENCE TMM_TEMP10_SEQ' in PLSQL works - Viktorov

2 answers 2

Many years ago I wrote a small procedure that implements the logic:

 CREATE/ALTER/DROP <object> IF EXISTS...

You can use it in your case:

 create or replace procedure admin.re_run_ddl (p_sql in varchar2) AUTHID CURRENT_USER as l_line varchar2(500) default rpad('-',20,'-'); l_cr varchar2(2) default chr(10); l_footer varchar2(500) default l_cr||rpad('*',20,'*'); l_ignore_txt varchar2(200) default 'IGNORING --> '; ORA_00955 EXCEPTION; ORA_01430 EXCEPTION; ORA_02260 EXCEPTION; ORA_01408 EXCEPTION; ORA_00942 EXCEPTION; ORA_02275 EXCEPTION; ORA_01418 EXCEPTION; ORA_02443 EXCEPTION; ORA_01442 EXCEPTION; ORA_01434 EXCEPTION; ORA_01543 EXCEPTION; ORA_00904 EXCEPTION; ORA_02261 EXCEPTION; ORA_04043 EXCEPTION; ORA_02289 EXCEPTION; PRAGMA EXCEPTION_INIT(ORA_00955, -00955); --ORA-00955: name is already used by an existing object PRAGMA EXCEPTION_INIT(ORA_01430, -01430); --ORA-01430: column being added already exists in table PRAGMA EXCEPTION_INIT(ORA_02260, -02260); --ORA-02260: table can have only one primary key PRAGMA EXCEPTION_INIT(ORA_01408, -01408); --ORA-01408: such column list already indexed PRAGMA EXCEPTION_INIT(ORA_00942, -00942); --ORA-00942: table or view does not exist PRAGMA EXCEPTION_INIT(ORA_02275, -02275); --ORA-02275: such a referential constraint already exists in the table PRAGMA EXCEPTION_INIT(ORA_01418, -01418); --ORA-01418: specified index does not exist PRAGMA EXCEPTION_INIT(ORA_02443, -02443); --ORA-02443: Cannot drop constraint - nonexistent constraint PRAGMA EXCEPTION_INIT(ORA_01442, -01442); --ORA-01442: column to be modified to NOT NULL is already NOT NULL PRAGMA EXCEPTION_INIT(ORA_01434, -01434); --ORA-01434: private synonym to be dropped does not exist PRAGMA EXCEPTION_INIT(ORA_01543, -01543); --ORA-01543: tablespace '<TBS_NAME>' already exists PRAGMA EXCEPTION_INIT(ORA_00904, -00904); --ORA-00904: "%s: invalid identifier" PRAGMA EXCEPTION_INIT(ORA_02261, -02261); --ORA-02261: "such unique or primary key already exists in the table" PRAGMA EXCEPTION_INIT(ORA_04043, -04043); --ORA-04043: object %s does not exist PRAGMA EXCEPTION_INIT(ORA_02289, -02289); --ORA-02289: sequence does not exist procedure p( p_str in varchar2 ,p_maxlength in int default 120 ) is i int := 1; begin dbms_output.enable( NULL ); while ( (length(substr(p_str,i,p_maxlength))) = p_maxlength ) loop dbms_output.put_line(substr(p_str,i,p_maxlength)); i := i + p_maxlength; end loop; dbms_output.put_line(substr(p_str,i,p_maxlength)); end p; begin p( 'EXEC:'||l_cr||l_line||l_cr||p_sql||l_cr||l_line ); execute immediate p_sql; p( 'done.' ); exception when ORA_00955 or ORA_01430 or ORA_02260 or ORA_01408 or ORA_00942 or ORA_02275 or ORA_01418 or ORA_02443 or ORA_01442 or ORA_01434 or ORA_01543 or ORA_00904 or ORA_02261 or ORA_04043 or ORA_02289 then p( l_ignore_txt || SQLERRM || l_footer ); when OTHERS then p( SQLERRM ); p( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); p( l_footer ); RAISE; end; / show err

Example of use:

 prompt clean-up ... begin admin.re_run_ddl('drop sequence BLA_BLA_BLA'); admin.re_run_ddl('drop procedure BLA_BLA_BLA'); admin.re_run_ddl('drop table BLA_BLA_BLA'); end; /
  • one
    And I was always too lazy to write it. Thank! - Viktorov
  • @Viktorov, glad that someone came in handy ... :) - MaxU
  • My opinion: two identical lines of code are not wrapped in a function, two times in the project - a design error, three times or more - it looks like sabotage. - 0xdb
  • @ 0xdb, if you are admin.re_run_ddl() about repetitions admin.re_run_ddl() then this is exactly what this procedure was created for - it was necessary to execute scripts with DDL, which could contain errors. If the error occurred in the middle of the script, then after correcting the error, it was necessary to comment on all DDLs that were successfully executed before the error. Actually, therefore, the name of the procedure is ... - MaxU
  • one
    @MaxU No, of course, about repetitions of what's inside admin.re_run_ddl() . - 0xdb

And on the drop swears because of this (execute immediate), as Connor reminds us of AskTOM :

DDL - considered a rare event in Oracle (unlike other DBMS)

 DECLARE V_TEMP_NUM NUMBER(9) := 0; BEGIN SELECT COUNT(*) INTO V_TEMP_NUM FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TMM_TEMP10_SEQ'; IF V_TEMP_NUM > 0 THEN execute immediate 'DROP SEQUENCE TMM_TEMP10_SEQ'; END IF; execute immediate ' CREATE SEQUENCE TMM_TEMP10_SEQ MINVALUE 0 START WITH 10 INCREMENT BY 10 CACHE 20'; END;
  • Actually this is Connor noticed. He also added: "I would like to know the purpose for which to execute a bunch of DDL in the pl / sql code." TC with his thesis work here is a living example of such a need. - 0xdb
  • Yes, of course, Connor. Thank you, corrected. About the thesis did not catch. - Mikhail Kazantsev