Get DDL of DBMS Scheduler packages job in Oracle

Get DDL of DBMS Scheduler packages job in Oracle

Create a package before they get the DDL:

SQL> BEGIN
dbms_scheduler.create_job(
job_name => 'Full_Backup'
,job_type => 'backup_script'
,job_action => 'C:\script\rmanbackup.rman'
---,credential_name => 'Windowuser'
,start_date => SYSDATE +1/24/59
,repeat_interval => 'FREQ=DAILY; BYHOUR=9; BYMINUTE=38;'
,enabled => TRUE
,comments => 'Full DB BACKUP'
);
END;
/
PL/SQL procedure successfully completed.


-- We update the Password later
BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
name => 'Full_Backup',
attribute => 'credential_name',
value => 'Windowuser');
END;
/

Getting the DDL of the above Scheduler Package from Oracle database:
1. Get the owner detail

col owner for a8
col job_action for a30
col job_name for a15
col credential_name for a20
select owner,job_name,job_action,credential_name from dba_scheduler_jobs where job_name like 'FULL%';

OWNER    JOB_NAME        JOB_ACTION                     CREDENTIAL_NAME
-------- --------------- ------------------------------ --------------------
SCOTT    FULL_BACKUP     C:\script\rmanbackup.rman      WINDOWUSER

2.Fetch the DDL of the DBMS Scheduler package with DBMS Metadata package

SELECT DBMS_METADATA.get_ddl('PROCOBJ','FULL_BACKUP', 'SCOTT') AS JOB_DDL FROM dual;


JOB_DDL
-------------------------------------------------------------------------------
BEGIN
dbms_scheduler.create_job('"FULL_BACKUP"',
job_type=>'BACKUP_SCRIPT', job_action=>
'C:\script\rmanbackup.rman'
, number_of_arguments=>0,
start_date=>TO_TIMESTAMP_TZ('15-SEP-2020 08.57.28.000000000 AM +05:30','DD-MON-R
RRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
'FREQ=DAILY; BYHOUR=9; BYMINUTE=38;'
, end_date=>NULL,
job_class=>'"DEFAULT_JOB_CLASS"', enabled=>FALSE, auto_drop=>TRUE,comments=>
'Full DB BACKUP'
);
sys.dbms_scheduler.set_attribute('"FULL_BACKUP"','NLS_ENV','NLS_LANGUAGE=''AMERI
CAN'' NLS_TERRITORY=''AMERICA'' NLS_CURRENCY=''$'' NLS_ISO_CURRENCY=''AMERICA''
NLS_NUMERIC_CHARACTERS=''.,'' NLS_CALENDAR=''GREGORIAN'' NLS_DATE_FORMAT=''DD-MO
N-RR'' NLS_DATE_LANGUAGE=''AMERICAN'' NLS_SORT=''BINARY'' NLS_TIME_FORMAT=''HH.M
I.SSXFF AM'' NLS_TIMESTAMP_FORMAT=''DD-MON-RR HH.MI.SSXFF AM'' NLS_TIME_TZ_FORMA
T=''HH.MI.SSXFF AM TZR'' NLS_TIMESTAMP_TZ_FORMAT=''DD-MON-RR HH.MI.SSXFF AM TZR'
' NLS_DUAL_CURRENCY=''$'' NLS_COMP=''BINARY'' NLS_LENGTH_SEMANTICS=''BYTE'' NLS_
NCHAR_CONV_EXCP=''FALSE''');
dbms_scheduler.set_attribute('"FULL_BACKUP"','credential_name',
'"WINDOWUSER"');
COMMIT;
END;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.