Oracle DB: Scheduled job failing with error: ORA-06576: not a valid function or procedure name

Question

I am trying to create a scheduled job in Oracle DB, from which, I need to execute a stored procedure.

I have created the procedure like:

CREATE OR REPLACE PROCEDURE "delete_old_transactions" AS
BEGIN
  DELETE from EVENT_JOURNAL where EVENT_JOURNAL.WRITE_TIMESTAMP < (((SYSDATE-CAST(TO_TIMESTAMP_TZ('01-01-1970 00:00:00+00:00', 'DD-MM-YYYY HH24:MI:SS TZH:TZM') as date)) * 24 * 60 * 60 * 1000) - (4 * 24 * 60 * 60 * 1000));
  DELETE from SNAPSHOT where SNAPSHOT.CREATED < (((SYSDATE-CAST(TO_TIMESTAMP_TZ('01-01-1970 00:00:00+00:00', 'DD-MM-YYYY HH24:MI:SS TZH:TZM') as date)) * 24 * 60 * 60 * 1000) - (4 * 24 * 60 * 60 * 1000));
END;

The stored procedure compiles fine and gets created successfully.

I created the scheduled job like:

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
   job_name           =>  'delete_old_transactions_job',
   job_type           =>  'STORED_PROCEDURE',
   start_date         =>  SYSTIMESTAMP,
   enabled            =>  true,
   job_action         =>  'delete_old_transactions',
   repeat_interval    =>  'FREQ=MINUTELY;INTERVAL=2;',
   job_class          =>  'DEFAULT_JOB_CLASS',
   comments           =>  'Job for deleting old transactions.');
END;

The scheduled job also gets created and is executing after every 2 minutes as desired, but it is failing in the execution and returns an error:

ORA-06576: not a valid function or procedure name.

Answer 1

Perhaps this will help in understanding how oracle treats case-sensitivity of object names. Here I am playing with table names, just for the sake of a simpler demo. But the same applies to all object names - tablespaces, tables, columns, functions, procedures, packages, etc.

Pay close attention to the treatment of the table name:

SQL> conn scott/tiger@pdb01
Connected.
SQL> --
SQL> select banner from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

1 row selected.

SQL> --
SQL> -- Create mixed-case table
SQL> create table "MyDemo"
  2     (col1 varchar2(20))
  3  ;

Table created.

SQL> insert into "MyDemo" values ('mixed case');

1 row created.

SQL> -- create default case table
SQL> create table MyDemo
  2     (col1 varchar2(20));

Table created.

SQL> insert into MyDemo values ('default');

1 row created.

SQL> --
SQL> select table_name
  2  from user_tables
  3  where upper(table_name)='MYDEMO';

TABLE_NAME
--------------------
MyDemo
MYDEMO

2 rows selected.

SQL>
SQL> select * from mydemo;

COL1
--------------------
default

1 row selected.

SQL> select * from MyDemo;

COL1
--------------------
default

1 row selected.

SQL> select * from "MyDemo";

COL1
--------------------
mixed case

1 row selected.

SQL> -- clean up
SQL> drop table mydemo;

Table dropped.

SQL> drop table "MyDemo";

Table dropped.

SQL> --
SQL> spo off
SQL> edit demo.log
SQL> insert into MyDemo values ('default');

1 row created.

SQL> --
SQL> select table_name
  2  from user_tables
  3  where upper(table_name)='MYDEMO';

TABLE_NAME
--------------------
MyDemo
MYDEMO

2 rows selected.

SQL>
SQL> select * from mydemo;

COL1
--------------------
default

1 row selected.

SQL> select * from MyDemo;

COL1
--------------------
default

1 row selected.

SQL> select * from "MyDemo";

COL1
--------------------
mixed case

1 row selected.

SQL> -- clean up
SQL> drop table mydemo;

Table dropped.

SQL> drop table "MyDemo";

Table dropped.

Answer 2

This is wrong:

CREATE OR REPLACE PROCEDURE "delete_old_transactions" 
                            -                       -
                            this                    this

Why did you use double quotes? Remove them. In Oracle, everything is stored into data dictionary in uppercase, but you can reference them any way you want (upper/lower/mixed case).

But, if you use double quotes while creating objects, you have to use the same double quotes and match letter case exactly every time you reference those objects.

Therefore, the simplest option is to recreate the procedure, but this time remove double quotes.

Or, put double quotes into DBMS_SCHEDULER:

job_action         =>  '"delete_old_transactions"',

but that's not particularly good idea.