主页 / user-216231

Andrew Hicks's questions

Martin Hope
Andrew Hicks
Asked: 2020-09-26 14:09:42 +0800 CST
  • -1

我正在尝试在 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
"
oracle jobs