Skip to Main Content

Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

RUN_JOB produces ERROR: ORA-00942: table or view does not ex

644442Jun 11 2008 — edited Jun 2 2009
Hello,
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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 30 2009
Added on Jun 11 2008
7 comments
7,083 views