主页 / dba / 问题 / 276114
Accepted
Andrew Hicks
Asked: 2020-09-26 14:09:42 +0800 CST

为什么 SQL Developer 中的计划作业会失败？

  • 772

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

2 个回答

  1. Best Answer

    这是reddit用户'beunbehagen'对您的reddit帖子的正确答案

    我相信您不能在 PL\SQL 块中执行 DDL 命令。要做到这一点需要使用“立即执行”

    Execute immediately 'drop table <tableName>';

    另外，请注意，plsql 块通常不会以角色（如 dba）授予的权限运行。

    • 1

  2. 谢谢大家的反馈，原来尝试为 DDL 语句安排作业时存在问题。我试着从这个改变它：

    BEGIN
  DROP TABLE TEST123;
END

    对此：

    BEGIN
  DELETE FROM TEST123;
END

    并遇到了新的问题。原来我需要一个';' 在 END 之后也是如此。所以当我这样做时，它确实有效：

    BEGIN
  DELETE FROM TEST123;
END;
    • 0

相关问题