6 Replies Latest reply: Mar 11, 2013 5:01 PM by 978204 RSS

    Extracting the DDL for scheduler jobs

    568501
      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
          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
            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
              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
                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
                  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
                    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]