This discussion is archived
6 Replies Latest reply: Mar 11, 2013 3:01 PM by 978204 RSS

Extracting the DDL for scheduler jobs

568501 Newbie
Currently Being Moderated
Hi All

I am trying to extract the DDL for our scheduler jobs but keep coming up against a brick wall. I've tried doing an expdp/impdp using the SQLFILE option but can't find the jobs here, tried a natty bit of code to pull the info out of the DB using DBMS_METADATA which worked for ordinary DBA_JOBS but not for scheduler based jobs.

Any ideas how I can get hold of this info?

Thanks

Glenn
  • 1. Re: Extracting the DDL for scheduler jobs
    549859 Newbie
    Currently Being Moderated
    This is perhaps not the prettiest way either, but what I have done is to click on the "Create Like" button for the job in EM. Enter a new job name and then click on the "Show SQL" button. That gives you all of the code. I then cut and paste into text file and change all references to the new job name back to existing.
  • 2. Re: Extracting the DDL for scheduler jobs
    RnR Pro
    Currently Being Moderated
    The only other way I can think of is to use an internal routine which does this.

    First select the object_id of the job by selecting from obj$ (where name='JOB_NAME') then use a procedure like

    create or replace procedure export_job(oid number) as
    tmpbuf varchar2(4000) := 'NULL';
    dummy varchar2(10);
    begin
    dbms_output.put_line('BEGIN');
    while tmpbuf is not null loop
    tmpbuf := dbms_sched_job_export.create_exp(oid, '', dummy);
    dbms_output.put_line(tmpbuf);
    end loop;
    dbms_output.put_line('END;');
    end;
    /

    and do
    exec export_job(12345)

    where 12345 is the obj# selected from obj$

    The only disadvantage of this is that job argument values will not be included in the output.

    Hope this helps,
    Ravi.
  • 3. Re: Extracting the DDL for scheduler jobs
    568501 Newbie
    Currently Being Moderated
    Hi

    Thanks for your input ... I actually ended up managin to get the SQLFILE to contain user owned scheduler jobs, but SYS must be obfuscated in some way as I couldnt find them anywhere. I then had to use the 'Create Like' from OEM to get some other info for additional jobs.

    Sheesh, you'd think it would be a bit easier than that!

    Cheers

    Glenn
  • 4. Re: Extracting the DDL for scheduler jobs
    RnR Pro
    Currently Being Moderated
    Reason for that is pretty simple. User-level objects in SYS (like tables, stored procedures, jobs etc) are simply not exported since SYS is intended only for system objects.

    I agree that there should be a simpler way to do this though. It's probably a bug that dbms_metadata doesn't work on Scheduler jobs.

    -Ravi
  • 5. Re: Extracting the DDL for scheduler jobs
    990912 Newbie
    Currently Being Moderated
    Last post on this is from 2007. I'm wondering if there have been any improvements since then, eg. in 11g. I'm still looking for documentation about dbms_sched_job_export.create_exp because I must be doing something wrong with it. I'm trying to call it for a known scheduler job in my system with an object ID of 315289, using this code (based on the code given earlier in this thread):
    declare
    v_dummy varchar2(10);
    v_buff varchar2(4000);
    begin
    v_buff :=  dbms_sched_job_export.create_exp(315289, '', v_dummy);
    dbms_output.put_line(v_buff);
    end;
    I get nothing back. No clue what I'm doing wrong...
  • 6. Re: Extracting the DDL for scheduler jobs
    978204 Newbie
    Currently Being Moderated
    You can get the DDL for a job like this:

    select dbms_metadata.get_ddl('PROCOBJ', 'JOB_NAME', 'OWNER_NAME') from dual;

    You can use this go gather the DDL for a number of the DBMS_SCHEDULER objects, including PROGRAMS and CHAINs [will include the CHAIN and the STEPS, butnot the RULES - these you must get from a *_scheduler_chain_rules VIEW]