RE: dbms_job question

Try:

        select * from all_objects where object_name =3D 'DBMS_JOB';

If that returns at least 3 rows, one for a synonym one for a package and the last for a package body all owned by sys, then try:

        select * from all_tab_privs where table_name =3D 'DBMS_JOB';

That should return one row which states that PUBLIC may execute it.

If step one fails then your DBA, or you if you're the DBA, will probably need to rerun catproc.sql
If step t2 only fails then your DBA or you need to connect to the instance as SYS and issue the "grant execute on dbms_job to public;' command.

BTW: This package has an output variable, the job_id. To not have to put it in an anonymous PL/SQL block use a bind variable as follows:

Var job number;
Exec dbms_job(:job, <what I want to do>,.....); Print job

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

=20

-----Original Message-----

From: Jaehne, Richard S [mailto:JaehneRS_at_state.gov]=20 Sent: Friday, April 15, 2005 2:08 PM
To: oracle-l_at_freelists.org
Subject: dbms_job question

All,=20
I need to schedule a job to run weekly at a specific time. I want to use DBMS_JOB to run it but I'm having some issues. The error I get when I try to submit the job in SQL is this: SP2-0734: unknown command beginning "DBMS_JOB.S..." - rest of line ignored.

I've tried it with Oracle 8i and 9i both with the same results. Shouldn't the DBMS_JOB scheduler be loaded by default?

Any help would be appreciated.

Thank you,=20

Richard Jaehne

--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Fri Apr 15 2005 - 14:35:49 CDT