為什麼 SQL Developer 中的計劃作業會失敗？

我正在嘗試在 Oracle SQL Developer 中創建一個每月執行一次的計劃作業，並且我正在測試 Scheduler/Jobs 功能，我在其中創建了一個簡單的 SQL 查詢來測試執行此操作的能力。我有一個名為“TEST123”的簡單表，當我執行“SELECT * FROM TEST123;”時，我可以看到它有效。

在 SQL Developer 中，我有一個名為“TEST1”的作業，它有一個 PL/SQL 塊 =“DROP TABLE TEST123;” 立即執行（儘管我也測試過在特定時間執行）。

在我看到我仍然可以從此測試表中進行選擇並且作業名稱的狀態為“FAILED”之後。我將所有其他設置保留為預設設置。我在這裡想念什麼？為什麼它失敗了，有沒有辦法解決它？

來自作業嚮導的 SQL：

BEGIN
   DBMS_SCHEDULER.CREATE_JOB (
           job_name => '"TEST1"',
           job_type => 'PLSQL_BLOCK',
           job_action => 'DROP TABLE MKTRD.TEST123;',
           number_of_arguments => 0,
           start_date => NULL,
           repeat_interval => NULL,
           end_date => NULL,
           enabled => FALSE,
           auto_drop => FALSE,
           comments => '');

   DBMS_SCHEDULER.SET_ATTRIBUTE( 
            name => '"TEST1"', 
            attribute => 'logging_level', value => DBMS_SCHEDULER.LOGGING_OFF);
 
   DBMS_SCHEDULER.enable(
            name => '"TEST1"');
END;

作業日誌中的錯誤：

"ORA-06550: line 1, column 757:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

  ( begin case declare exit for goto if loop mod null pragma
  raise return select update while with <an identifier>
  <a double-quoted delimited-identifier> <a bind variable> <<
  continue close current delete fetch lock insert open rollback
  savepoint set sql execute commit forall merge pipe purge
  json_exists json_value json_query json_object json_array
The symbol "lock was inserted before "DROP" to continue.
ORA-06550: line 1, column 781:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

  . , @ in <an identifier>
  <a double-quoted delimited-identifier> partition subpartition
ORA-06550: line 1, column 856:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

  end not pragma final instantiable order overriding static
  member constructor map
"

更新：如果我使用 PLSQL Block 語法

BEGIN
 DROP TABLE TEST123;
END

我仍然收到以下錯誤：

"ORA-06550: line 2, column 3:
PLS-00103: Encountered the symbol "DROP" when expecting one of the following:

  ( begin case declare exit for goto if loop mod null pragma
  raise return select update while with <an identifier>
  <a double-quoted delimited-identifier> <a bind variable> <<
  continue close current delete fetch lock insert open rollback
  savepoint set sql execute commit forall merge pipe purge
  json_exists json_value json_query json_object json_array
The symbol "lock was inserted before "DROP" to continue.
ORA-06550: line 2, column 27:
PLS-00103: Encountered the symbol ";" when expecting one of the following:

  . , @ in <an identifier>
  <a double-quoted delimited-identifier> partition subpartition
"

這是reddit使用者’beunbehagen’對您的reddit文章的正確答案

我相信您不能在 PL\SQL 塊中執行 DDL 命令。要做到這一點需要使用“立即執行”

Execute immediately 'drop table <tableName>';

另外，請注意，plsql 塊通常不會以角色（如 dba）授予的權限執行。

謝謝大家的回饋，原來嘗試為 DDL 語句安排作業時存在問題。我試著從這個改變它：

BEGIN
 DROP TABLE TEST123;
END

對此：

BEGIN
 DELETE FROM TEST123;
END

並遇到了新的問題。原來我需要一個’;’ 在 END 之後也是如此。所以當我這樣做時，它確實有效：

BEGIN
 DELETE FROM TEST123;
END;

引用自：https://dba.stackexchange.com/questions/276114