How to schedule a job with dba_scheduler_jobs?

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Thanks for the info. I forgot to mentin if the stored procedure takes two parameters: p1('table_name', date)
How to set it up??

SOLUTION

mrjoltcola

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

SOLUTION

OP_Zaharin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

>> I forgot to mentin if the stored procedure takes two parameters

Check the docs for: SET_JOB_ARGUMENT_VALUE

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sched.htm#i1011390

There are a lot of examples our there if you search...

DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
             job_name => 'MY_PROC'
            ,argument_position => 1
            ,argument_value => 'SOME_TABLE_NAME'
);

Hopefully you can get the second parameter...

ASKER

mrjoltcola:
LINE/COL ERROR
-------- -----------------------------------------------------------------
15/77 PLS-00103: Encountered the symbol "t1" when expecting one
of the following:
) , * & = - + < / > at in is mod remainder not rem
<an exponent (**)> <> or != or ~= >= <= <> and or like like2
like4 likec between || multiset member submultiset
The symbol ", was inserted before "t1" to continue.

where t1 line corresponds to job_action => 'BEGIN p1('table_name', sysdate); END;',
How to deal with ' of ' ? t1=table_name ???

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

ASKER

Yes. I did it. No error. However when I queried the view, I I could not see the job I just created.
SELECT job_name,start_date from all_scheduler_jobs;
Do I miss anything???

ASKER

I logged in and created it as sys.

You should not create things as sys. It's a bad practice.

Try dba_scheduler_jobs.

SOLUTION

OP_Zaharin

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.