RUN_JOB produces ERROR: ORA-00942: table or view does not ex
644442Jun 11 2008 — edited Jun 2 2009Hello,
I have created a job for 10g. But it doesn't run.
I don't know which Table is not granted or expected.
Indeed, something is wrong on line 441 of SYS.DBMS_SCHEDULER Package, but the code is hidden for normal users.
This is the moment I get the Error:
BEGIN
--dbms_scheduler.stop_job('Franks_Job_1');
--DBMS_SCHEDULER.ENABLE ('Franks_Job_1');
DBMS_SCHEDULER.RUN_JOB ('FRANKS_JOB_1');
END;
Error Message is:
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_ISCHED", line 150
ORA-06512: at "SYS.DBMS_SCHEDULER", line 441
ORA-06512: at line 5
*******************
The DBA granted me to the following roles:
GRANT EXECUTE ON SYS.SCHEDULER$_EVENT_INFO to PVMADMIN
GRANT EXECUTE ON SYS.DBMS_AQ to PVMADMIN
GRANT EXECUTE ON SYS.DBMS_AQADM to PVMADMIN
And I can see the Job in the <<user_schedler_jobs>> table, after has been created.
Also, I see the QUEUE, DIRECTORY, Payload_Type and QUEUE_TABLE, too.
My ORACLE-USER is PVMADMIN.
This is my code to create and ENABLE the Job, and it seems to works.
CREATE OR REPLACE TYPE franks_payload_type AS OBJECT (
object_name VARCHAR2 (200),
object_owner VARCHAR2 (32),
event_type VARCHAR2 (50)
);
CREATE OR REPLACE DIRECTORY fratest AS '/PVMPST/home/pvgtbch/frank_testdata';
BEGIN
DBMS_AQADM.stop_queue (queue_name => 'franks_queue_1');
DBMS_AQADM.drop_queue (queue_name => 'franks_queue_1');
DBMS_AQADM.drop_queue_table ('franks_queuetab_1');
-- Queue must be defined like "multiple consumers"
-- Else it will not work ...
DBMS_AQADM.create_queue_table
(queue_table => 'franks_queuetab_1',
queue_payload_type => 'franks_payload_type',
multiple_consumers => TRUE
);
DBMS_AQADM.create_queue (queue_name => 'franks_queue_1',
queue_table => 'franks_queuetab_1'
);
DBMS_AQADM.start_queue ('franks_queue_1');
DBMS_SCHEDULER.drop_job ('Franks_Job_1');
DBMS_SCHEDULER.create_job
(job_name => 'Franks_Job_1',
job_type => 'EXECUTABLE',
job_action => '/PVMPST/home/pvgtbch/frank_testdata/franks_shell.sh',
start_date => SYSTIMESTAMP,
enabled => TRUE,
event_condition => 'tab.user_data.object_owner = ''PVMADMIN''
AND tab.user_data.object_name = ''bekannte_namen.dat''
AND tab.user_data.event_type = ''FILE_ARRIVAL''',
queue_spec => 'franks_queue_1'
);
END;
Thanks in advance for any help,
Frank