0

In the procedure I want to set up to drop the partition in an hour. To prevent the procedure from falling, there will be no partitions, added IF EXISITS. But after starting the procedure, it displays an error ORA-00905 Missing keyword:

EXECUTE IMMEDIATE 'ALTER TABLE TEST_TABLE DROP IF EXISTS PARTITION FOR (TO_DATE(''' ||
TO_CHAR(trunc(sysdate, 'hh'),
                   'YYYY-MM-DD HH24:MI') ||
                   ''', ''YYYY-MM-DD HH24:MI''))';
Andrey Romanov
  • 79
  • 1
  • 7
  • 2
    Oracle doesn't have an `IF EXISTS` clause - [docs](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877__CJAIDFBB). You can query the data dictionary to see if it exists, or probably more efficiently, attempt to drop it and catch the exception if it doesn't exist? [This question](https://stackoverflow.com/q/1799128/266304) is similar but for `drop table`; the answers there show various approaches, including ways to catch the exception - you just need to know the right number. – Alex Poole Dec 06 '22 at 14:11

0 Answers0