Oracle Get DDL without extra details and sort - to use for version control

Question

I'm trying to get the DDL for tables, views, indexes, sequences and so on in order to use for version control, and do comparisons between different db's which should have identical tables (i.e. dev vs uat).

I would like to be able to sort by table vs. view vs. index...and then sort within those by table name, or view name.
This will help with comparison. Basically taking this post and extending functionality: Problem with getting an Oracle table ddl but with out the extra detail from the current schema

Expected output is -a clob of all the data that I can use for comparison, version control, and usable as a ddl script (create tables once in dev, dump them into UAT after testing)

I've got a function

create or replace FUNCTION F_clean_DDL RETURN VARCHAR2 AS
BEGIN
  dbms_metadata.set_transform_param (
    dbms_metadata.session_transform, 'SQLTERMINATOR', true);

    dbms_metadata.set_transform_param (
        dbms_metadata.session_transform, 'SQLTERMINATOR', true);
    dbms_metadata.set_transform_param (
        dbms_metadata.session_transform, 'PRETTY', true);
    dbms_metadata.set_transform_param (
        dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', false);
    dbms_metadata.set_transform_param (
        dbms_metadata.session_transform, 'CONSTRAINTS_AS_ALTER', false);
    dbms_metadata.set_transform_param (
        dbms_metadata.session_transform, 'STORAGE', false);


  RETURN 'F_clean_DDL.';
END;

And I've got the following view:

SELECT CONCAT('1_', table_name) AS renamed_table_name, DBMS_METADATA.GET_DDL('TABLE', table_name) AS ddl
FROM user_tables
UNION ALL

SELECT CONCAT('2_', view_name) AS renamed_view_name, DBMS_METADATA.GET_DDL('VIEW', view_name) AS ddl
FROM user_views
UNION ALL

SELECT CONCAT('3_', index_name) AS renamed_index_name, DBMS_METADATA.GET_DDL('INDEX', index_name) AS ddl
FROM user_indexes
UNION ALL
SELECT CONCAT('4_', sequence_name) AS renamed_sequence_name, DBMS_METADATA.GET_DDL('SEQUENCE', sequence_name) AS ddl
FROM user_sequences
UNION ALL

SELECT CONCAT('5_', mview_name) AS renamed_mview_name, DBMS_METADATA.GET_DDL('MATERIALIZED_VIEW', mview_name) AS ddl
FROM user_mview
ORDER BY RENAMED_TABLE_NAME DESC

I get the results I want with 'select DDL from v_ddl ORDER BY renamed_table_name ASC'

What is the best way to combine executing the function and then the view into 1 step? I'm sure its a function or stored procedure but I believe my approach may be wrong.

You may place a function call in the `select` list and it will be applied for each output row. — astentx, May 21 '23 at 21:31
What exactly is your question? Your title talks about DDL simplification and sorting, but the only question in your post is about how to call a function. Can you clarify where you're stuck? — Paul W, May 22 '23 at 02:43

score 0 · Answer 1 · answered May 22 '23 at 03:49

I wrote a little utility to take the "reasonably clean" output from DBMS_METADATA and tidy it up into something a human would typically type, for example, if I have done

SQL> begin
  2         dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
  3         dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
  4         dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SEGMENT_ATTRIBUTES', true);
  5         dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'STORAGE', false);
  6  end;
  7  /

then I might get back

CREATE TABLE "MCDONAC"."EMP"
   (    "EMPNO" NUMBER(4,0) NOT NULL ENABLE,
        "ENAME" VARCHAR2(10) COLLATE "USING_NLS_COMP",
        "JOB" VARCHAR2(9) COLLATE "USING_NLS_COMP",
        "MGR" NUMBER(4,0),
        "HIREDATE" DATE,
        "SAL" NUMBER(7,2),
        "COMM" NUMBER(7,2),
        "DEPTNO" NUMBER(2,0),
         CONSTRAINT "EMP_PK" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"  ENABLE,
         CONSTRAINT "EMP_FK" FOREIGN KEY ("DEPTNO")
          REFERENCES "MCDONAC"."DEPT" ("DEPTNO") ENABLE
   )  DEFAULT COLLATION "USING_NLS_COMP" SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "USERS" ;

which I then pass through my utility to get

create table mcdonac.emp
   (    empno number(4,0) not null enable,
        ename varchar2(10),
        job varchar2(9),
        mgr number(4,0),
        hiredate date,
        sal number(7,2),
        comm number(7,2),
        deptno number(2,0),
         constraint emp_fk foreign key (deptno)
          references mcdonac.dept (deptno) enable
   ) ;

I designed it for tables and views, not for every possible DDL you might to extract (I'd imagine PL/SQL might cause some issues).

You can get my tool at https://github.com/connormcd/misc-scripts/blob/master/ddl_cleanup.sql with some background reading at https://connor-mcdonald.com/2022/03/02/cleaner-ddl-than-dbms_metadata/

If you are looking for something more "industry strength" Philipp Salvisberg has a nice blog post on incorporating this into a SQL formatter

https://www.salvis.com/blog/2022/10/11/quoted-identifiers-joelkallmanday/

Oracle Get DDL without extra details and sort - to use for version control

1 Answers1