5 comments

  1. Can you run DBMS_METADATA to generate the DDL for a SCHEDULE. i.e. dbms_scheduler.create_schedule

  2. SQL> BEGIN
    2 DBMS_SCHEDULER.create_schedule (
    3 schedule_name => ‘BLAH’,
    4 start_date => SYSTIMESTAMP,
    5 repeat_interval => ‘freq=daily; byhour=0; byminute=0’,
    6 end_date => NULL);
    7 END;
    8 /

    PL/SQL procedure successfully completed.

    SQL> select dbms_metadata.get_ddl(‘PROCOBJ’,’BLAH’,user) from dual;

    DBMS_METADATA.GET_DDL(‘PROCOBJ’,’BLAH’,USER)
    ——————————————————————————–

    BEGIN
    dbms_scheduler.disable1_calendar_check();
    dbms_scheduler.create_schedule(‘”BLAH”‘,TO_TIMESTAMP_TZ(’09-MAR-2022 10.28.01.41
    6000000 PM +08:00′,’DD-MON-RRRR HH.MI.SSXFF AM TZR’,’NLS_DATE_LANGUAGE=english’)
    ,
    ‘freq=daily; byhour=0; byminute=0’,
    NULL,
    NULL
    );
    COMMIT;
    END;

  3. More than the formatting, what prevents us from using DBMS_METADATA.GET_DDL is that it seems to return information which looks incorrect. E.g., it seems to generate for every partition of a partitioned table, which has a segment, the clause “SEGMENT CREATION IMMEDIATE” although we do never specify “SEGMENT CREATION” when we create tables or add partitions, so that the value for a new partition is to be taken from database, where the default is now SEGMENT CREATION DEFAULT.
    Meaning, if we copy the code from DBMS_METADATA, the property will be different = not correct. Productive usage not possible. 😦

    1. The challenge here is what people mean by “DDL”. Do you want to replicate the DDL that you used to initially create the object, or you want the DDL that reflects the current state of the object? I’d contend that source code control should look after the former, and DBMS_METADATA to look after the latter.

Got some thoughts? Leave a comment

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 )

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.