Tutorial :Oracle's dbms_metadata.get_ddl for object_type JOB



Question:

I'd like to create ddl scripts for most of my database objects. dbms_metadata.get_ddl works for most of the object types. For instance the following creates the ddl for a view:

select dbms_metadata.get_ddl ( 'VIEW', 'SAMPLE_VIEW') from dual  

On the other hand it's not working for object_type 'JOB'. The following:

select dbms_metadata.get_ddl( 'JOB', 'SAMPLE_JOB' ) from dual  

gives the following error:

ORA-31604: invalid NAME parameter "NAME" for object type JOB in function SET_FILTER  ORA-06512: at "SYS.DBMS_SYS_ERROR", line 116  ORA-06512: at "SYS.DBMS_METADATA_INT", line 4705  ORA-06512: at "SYS.DBMS_METADATA_INT", line 8582  ORA-06512: at "SYS.DBMS_METADATA", line 2882  ORA-06512: at "SYS.DBMS_METADATA", line 2748  ORA-06512: at "SYS.DBMS_METADATA", line 4333  ORA-06512: at line 1  

If I list my jobs using

select * from user_objects where object_type='JOB'  

it shows SAMPLE_JOB (just like it shows SAMPLE_VIEW if filtered for object_type='VIEW').

Why is it working for VIEW (and TABLE, INDEX, TRIGGER, ...) and not for JOB?

I'm using Oracle 10g.


Solution:1

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

PROCOBJ's are procedural objects.


Solution:2

select dbms_metadata.get_ddl('PROCOBJ',['JOB'|'PROGRAM'|'SCHEDULE'],'OWNER') from dual;  

The PROCOBJ can be JOB, PROGRAM and SCHEDULE.


Solution:3

Alternative, get all jobs from the database with their DDL:

select owner, job_name, dbms_metadata.get_ddl('PROCOBJ', job_name, owner) as ddl_output from ALL_SCHEDULER_JOBS  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »