I found this as an option to create an Oracle program where SQL code could be inserted; however, the syntax does not allow me to create this. The SQL code is not seen as valid in the program action section. What am I missing in the syntax or is this possible to do?

DBMS_SCHEDULER_CREATE PROGRAM (
program_name => 'TEST';
program_type => 'PLSQL_BLOCK';
program_action => 'BEGIN

    update my_table SET status = "processed" WHERE process_date = sysdate-1;
    update my_table2 SET status = "notprocessed" WHERE process_date >= sysdate;
END;'
enabled=>TRUE
);

I found this as an option to create an Oracle program where SQL code could be inserted; however, the syntax does not allow me to create this. The SQL code is not seen as valid in the program action section. What am I missing in the syntax or is this possible to do?

DBMS_SCHEDULER_CREATE PROGRAM (
program_name => 'TEST';
program_type => 'PLSQL_BLOCK';
program_action => 'BEGIN

    update my_table SET status = "processed" WHERE process_date = sysdate-1;
    update my_table2 SET status = "notprocessed" WHERE process_date >= sysdate;
END;'
enabled=>TRUE
);

• oracle-database

1 Answer 1

Quite a few mistakes.

• you need PL/SQL to use dbms_scheduler package (i.e. you're missing begin-end)
• package name and procedure name are separated by a dot (dbms_scheduler.create_program)
• separator is a comma, not a semi-colon character
• if you use a string (and you do, processed), then
  • it has to be enclosed into single - not double - quotes
  • as the statement is enclosed into single quotes already, you have to use two consecutive single quotes

Once fixed:

SQL> begin
  2    dbms_scheduler.create_program (
  3      program_name   => 'TEST3',
  4      program_type   => 'PLSQL_BLOCK',
  5      program_action => 'BEGIN
  6                           update my_table  SET
  7                             status = ''processed''
  8                             WHERE process_date = trunc(sysdate)-1;
  9                           update my_table  SET
 10                             status = ''notprocessed''
 11                             WHERE process_date >= sysdate;
 12                         END;',
 13      enabled => TRUE
 14  );
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL>

You didn't say where you found it, but - official DBMS_SCHEDULER documentation is here so it might be a good idea to read it.