Why would a scheduled job fail in SQL Developer?

Question

I am trying to create a scheduled job in Oracle SQL Developer that would run once a month and I am testing the Scheduler/Jobs feature where I have created a simple SQL query to test the ability to do this. I have a simple table called "TEST123" and I can see that it works when I do "SELECT * FROM TEST123;".

In SQL Developer I have a Job called "TEST1" which has a PL/SQL Block = "DROP TABLE TEST123;" to run immediately (though I have also tested this with running at a specific time).

After I see that I can still select from this test table and that the STATE of the job name is "FAILED". I left all other settings as default. What am I missing here? Why is it failing and is there a way to fix it?

SQL From Job Wizard:

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;

Error from Job Log:

 "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
"

Update: If I use the PLSQL Block syntax of

BEGIN
  DROP TABLE TEST123;
END

I still get an error of:

"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
"

Answer 1

Here is the correct an answer to your reddit post by reddit user 'beunbehagen'

I believe you cannot do DDL commands in a PL\SQL block. To do it would need to use "execute immediately "

Execute immediately 'drop table <tableName>';

Also, be warned, plsql blocks normally don't run with permissions granted by roles (like dba).

Answer 2

Thank you everyone for your feedback, turns out that there is an issue with trying to schedule jobs for DDL statements. I tried changing it from this:

BEGIN
  DROP TABLE TEST123;
END

To this:

BEGIN
  DELETE FROM TEST123;
END

And ran into a new issues. Turns out I need to have a ';' after the END as well. So when I did this it did work:

BEGIN
  DELETE FROM TEST123;
END;