Connor McDonald wrote a blog post named Cleaner DDL than DBMS_METADATA<\/a>. Back then he asked me if it would be possible to let the formatter remove unnecessary double quotes from quoted identifiers. Yes, of course. Actually, the current version of the PL\/SQL & SQL Formatter Settings<\/a> does exactly that. And no, you cannot do that with dbms_metadata<\/code> in the Oracle Database versions 19c and 21c. Read on if you are interested in the details.<\/p>\n\n\n\nThe Problem with DBMS_METADATA \u2026<\/h2>\n\n\n\nWhen you execute a DDL against the Oracle Database the database stores some metadata in the data dictionary. The DDL statement itself is not stored, at least not completely. This is one reason why it is a good idea to store DDL statements in files and manage these files within a version control system. Nevertheless, the Oracle Database provides an API \u2013 dbms_metadata<\/code><\/a> \u2013 to reconstruct a DDL based on the information available in the data dictionary.<\/p>\n\n\n\nLet\u2019s create a view based on the famous dept<\/code> and emp<\/code> tables:<\/p>\n\n\n\n1) Initial DDL<\/span><\/path><\/path><\/svg><\/span>create or replace<\/span> <\/span>view<\/span> <\/span>deptsal<\/span> <\/span>as<\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nand retrieve the DDL like this:<\/p>\n\n\n\n2) Get DDL<\/span><\/path><\/path><\/svg><\/span>select<\/span> dbms_metadata.get_ddl(<\/span>'VIEW'<\/span>, <\/span>'DEPTSAL'<\/span>, user) <\/span>from<\/span> dual;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nto produce this DDL:<\/p>\n\n\n\n3) DDL by dbms_metadata<\/span><\/path><\/path><\/svg><\/span>CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>FORCE<\/span> EDITIONABLE VIEW <\/span>"REDSTACK"<\/span>.<\/span>"DEPTSAL"<\/span> (<\/span>"DEPTNO"<\/span>, <\/span>"DNAME"<\/span>, <\/span>"SUM_SAL"<\/span>, <\/span>"NUM_EMPS"<\/span>, <\/span>"AVG_SAL"<\/span>) <\/span>DEFAULT<\/span> COLLATION <\/span>"USING_NLS_COMP"<\/span> <\/span>AS<\/span> <\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nWe see that the subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
dbms_metadata<\/code> in the Oracle Database versions 19c and 21c. Read on if you are interested in the details.<\/p>\n\n\n\nThe Problem with DBMS_METADATA \u2026<\/h2>\n\n\n\nWhen you execute a DDL against the Oracle Database the database stores some metadata in the data dictionary. The DDL statement itself is not stored, at least not completely. This is one reason why it is a good idea to store DDL statements in files and manage these files within a version control system. Nevertheless, the Oracle Database provides an API \u2013 dbms_metadata<\/code><\/a> \u2013 to reconstruct a DDL based on the information available in the data dictionary.<\/p>\n\n\n\nLet\u2019s create a view based on the famous dept<\/code> and emp<\/code> tables:<\/p>\n\n\n\n1) Initial DDL<\/span><\/path><\/path><\/svg><\/span>create or replace<\/span> <\/span>view<\/span> <\/span>deptsal<\/span> <\/span>as<\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nand retrieve the DDL like this:<\/p>\n\n\n\n2) Get DDL<\/span><\/path><\/path><\/svg><\/span>select<\/span> dbms_metadata.get_ddl(<\/span>'VIEW'<\/span>, <\/span>'DEPTSAL'<\/span>, user) <\/span>from<\/span> dual;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nto produce this DDL:<\/p>\n\n\n\n3) DDL by dbms_metadata<\/span><\/path><\/path><\/svg><\/span>CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>FORCE<\/span> EDITIONABLE VIEW <\/span>"REDSTACK"<\/span>.<\/span>"DEPTSAL"<\/span> (<\/span>"DEPTNO"<\/span>, <\/span>"DNAME"<\/span>, <\/span>"SUM_SAL"<\/span>, <\/span>"NUM_EMPS"<\/span>, <\/span>"AVG_SAL"<\/span>) <\/span>DEFAULT<\/span> COLLATION <\/span>"USING_NLS_COMP"<\/span> <\/span>AS<\/span> <\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nWe see that the subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
When you execute a DDL against the Oracle Database the database stores some metadata in the data dictionary. The DDL statement itself is not stored, at least not completely. This is one reason why it is a good idea to store DDL statements in files and manage these files within a version control system. Nevertheless, the Oracle Database provides an API \u2013 dbms_metadata<\/code><\/a> \u2013 to reconstruct a DDL based on the information available in the data dictionary.<\/p>\n\n\n\nLet\u2019s create a view based on the famous dept<\/code> and emp<\/code> tables:<\/p>\n\n\n\n1) Initial DDL<\/span><\/path><\/path><\/svg><\/span>create or replace<\/span> <\/span>view<\/span> <\/span>deptsal<\/span> <\/span>as<\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nand retrieve the DDL like this:<\/p>\n\n\n\n2) Get DDL<\/span><\/path><\/path><\/svg><\/span>select<\/span> dbms_metadata.get_ddl(<\/span>'VIEW'<\/span>, <\/span>'DEPTSAL'<\/span>, user) <\/span>from<\/span> dual;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nto produce this DDL:<\/p>\n\n\n\n3) DDL by dbms_metadata<\/span><\/path><\/path><\/svg><\/span>CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>FORCE<\/span> EDITIONABLE VIEW <\/span>"REDSTACK"<\/span>.<\/span>"DEPTSAL"<\/span> (<\/span>"DEPTNO"<\/span>, <\/span>"DNAME"<\/span>, <\/span>"SUM_SAL"<\/span>, <\/span>"NUM_EMPS"<\/span>, <\/span>"AVG_SAL"<\/span>) <\/span>DEFAULT<\/span> COLLATION <\/span>"USING_NLS_COMP"<\/span> <\/span>AS<\/span> <\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nWe see that the subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
dbms_metadata<\/code><\/a> \u2013 to reconstruct a DDL based on the information available in the data dictionary.<\/p>\n\n\n\nLet\u2019s create a view based on the famous dept<\/code> and emp<\/code> tables:<\/p>\n\n\n\n1) Initial DDL<\/span><\/path><\/path><\/svg><\/span>create or replace<\/span> <\/span>view<\/span> <\/span>deptsal<\/span> <\/span>as<\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nand retrieve the DDL like this:<\/p>\n\n\n\n2) Get DDL<\/span><\/path><\/path><\/svg><\/span>select<\/span> dbms_metadata.get_ddl(<\/span>'VIEW'<\/span>, <\/span>'DEPTSAL'<\/span>, user) <\/span>from<\/span> dual;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nto produce this DDL:<\/p>\n\n\n\n3) DDL by dbms_metadata<\/span><\/path><\/path><\/svg><\/span>CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>FORCE<\/span> EDITIONABLE VIEW <\/span>"REDSTACK"<\/span>.<\/span>"DEPTSAL"<\/span> (<\/span>"DEPTNO"<\/span>, <\/span>"DNAME"<\/span>, <\/span>"SUM_SAL"<\/span>, <\/span>"NUM_EMPS"<\/span>, <\/span>"AVG_SAL"<\/span>) <\/span>DEFAULT<\/span> COLLATION <\/span>"USING_NLS_COMP"<\/span> <\/span>AS<\/span> <\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nWe see that the subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
Let\u2019s create a view based on the famous dept<\/code> and emp<\/code> tables:<\/p>\n\n\n\n1) Initial DDL<\/span><\/path><\/path><\/svg><\/span>create or replace<\/span> <\/span>view<\/span> <\/span>deptsal<\/span> <\/span>as<\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nand retrieve the DDL like this:<\/p>\n\n\n\n2) Get DDL<\/span><\/path><\/path><\/svg><\/span>select<\/span> dbms_metadata.get_ddl(<\/span>'VIEW'<\/span>, <\/span>'DEPTSAL'<\/span>, user) <\/span>from<\/span> dual;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nto produce this DDL:<\/p>\n\n\n\n3) DDL by dbms_metadata<\/span><\/path><\/path><\/svg><\/span>CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>FORCE<\/span> EDITIONABLE VIEW <\/span>"REDSTACK"<\/span>.<\/span>"DEPTSAL"<\/span> (<\/span>"DEPTNO"<\/span>, <\/span>"DNAME"<\/span>, <\/span>"SUM_SAL"<\/span>, <\/span>"NUM_EMPS"<\/span>, <\/span>"AVG_SAL"<\/span>) <\/span>DEFAULT<\/span> COLLATION <\/span>"USING_NLS_COMP"<\/span> <\/span>AS<\/span> <\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nWe see that the subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
dept<\/code> and emp<\/code> tables:<\/p>\n\n\n\n1) Initial DDL<\/span><\/path><\/path><\/svg><\/span>create or replace<\/span> <\/span>view<\/span> <\/span>deptsal<\/span> <\/span>as<\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nand retrieve the DDL like this:<\/p>\n\n\n\n2) Get DDL<\/span><\/path><\/path><\/svg><\/span>select<\/span> dbms_metadata.get_ddl(<\/span>'VIEW'<\/span>, <\/span>'DEPTSAL'<\/span>, user) <\/span>from<\/span> dual;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nto produce this DDL:<\/p>\n\n\n\n3) DDL by dbms_metadata<\/span><\/path><\/path><\/svg><\/span>CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>FORCE<\/span> EDITIONABLE VIEW <\/span>"REDSTACK"<\/span>.<\/span>"DEPTSAL"<\/span> (<\/span>"DEPTNO"<\/span>, <\/span>"DNAME"<\/span>, <\/span>"SUM_SAL"<\/span>, <\/span>"NUM_EMPS"<\/span>, <\/span>"AVG_SAL"<\/span>) <\/span>DEFAULT<\/span> COLLATION <\/span>"USING_NLS_COMP"<\/span> <\/span>AS<\/span> <\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nWe see that the subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
emp<\/code> tables:<\/p>\n\n\n\n1) Initial DDL<\/span><\/path><\/path><\/svg><\/span>create or replace<\/span> <\/span>view<\/span> <\/span>deptsal<\/span> <\/span>as<\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nand retrieve the DDL like this:<\/p>\n\n\n\n2) Get DDL<\/span><\/path><\/path><\/svg><\/span>select<\/span> dbms_metadata.get_ddl(<\/span>'VIEW'<\/span>, <\/span>'DEPTSAL'<\/span>, user) <\/span>from<\/span> dual;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nto produce this DDL:<\/p>\n\n\n\n3) DDL by dbms_metadata<\/span><\/path><\/path><\/svg><\/span>CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>FORCE<\/span> EDITIONABLE VIEW <\/span>"REDSTACK"<\/span>.<\/span>"DEPTSAL"<\/span> (<\/span>"DEPTNO"<\/span>, <\/span>"DNAME"<\/span>, <\/span>"SUM_SAL"<\/span>, <\/span>"NUM_EMPS"<\/span>, <\/span>"AVG_SAL"<\/span>) <\/span>DEFAULT<\/span> COLLATION <\/span>"USING_NLS_COMP"<\/span> <\/span>AS<\/span> <\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nWe see that the subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
create or replace<\/span> <\/span>view<\/span> <\/span>deptsal<\/span> <\/span>as<\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nand retrieve the DDL like this:<\/p>\n\n\n\n2) Get DDL<\/span><\/path><\/path><\/svg><\/span>select<\/span> dbms_metadata.get_ddl(<\/span>'VIEW'<\/span>, <\/span>'DEPTSAL'<\/span>, user) <\/span>from<\/span> dual;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nto produce this DDL:<\/p>\n\n\n\n3) DDL by dbms_metadata<\/span><\/path><\/path><\/svg><\/span>CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>FORCE<\/span> EDITIONABLE VIEW <\/span>"REDSTACK"<\/span>.<\/span>"DEPTSAL"<\/span> (<\/span>"DEPTNO"<\/span>, <\/span>"DNAME"<\/span>, <\/span>"SUM_SAL"<\/span>, <\/span>"NUM_EMPS"<\/span>, <\/span>"AVG_SAL"<\/span>) <\/span>DEFAULT<\/span> COLLATION <\/span>"USING_NLS_COMP"<\/span> <\/span>AS<\/span> <\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nWe see that the subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
and retrieve the DDL like this:<\/p>\n\n\n\n
select<\/span> dbms_metadata.get_ddl(<\/span>'VIEW'<\/span>, <\/span>'DEPTSAL'<\/span>, user) <\/span>from<\/span> dual;<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nto produce this DDL:<\/p>\n\n\n\n3) DDL by dbms_metadata<\/span><\/path><\/path><\/svg><\/span>CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>FORCE<\/span> EDITIONABLE VIEW <\/span>"REDSTACK"<\/span>.<\/span>"DEPTSAL"<\/span> (<\/span>"DEPTNO"<\/span>, <\/span>"DNAME"<\/span>, <\/span>"SUM_SAL"<\/span>, <\/span>"NUM_EMPS"<\/span>, <\/span>"AVG_SAL"<\/span>) <\/span>DEFAULT<\/span> COLLATION <\/span>"USING_NLS_COMP"<\/span> <\/span>AS<\/span> <\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nWe see that the subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
to produce this DDL:<\/p>\n\n\n\n
CREATE<\/span> <\/span>OR<\/span> <\/span>REPLACE<\/span> <\/span>FORCE<\/span> EDITIONABLE VIEW <\/span>"REDSTACK"<\/span>.<\/span>"DEPTSAL"<\/span> (<\/span>"DEPTNO"<\/span>, <\/span>"DNAME"<\/span>, <\/span>"SUM_SAL"<\/span>, <\/span>"NUM_EMPS"<\/span>, <\/span>"AVG_SAL"<\/span>) <\/span>DEFAULT<\/span> COLLATION <\/span>"USING_NLS_COMP"<\/span> <\/span>AS<\/span> <\/span><\/span>\n <\/span>select<\/span> d.deptno,<\/span><\/span>\n d.dname,<\/span><\/span>\n nvl(<\/span>sum<\/span>(e.sal), <\/span>0<\/span>) <\/span>as<\/span> sum_sal,<\/span><\/span>\n nvl(<\/span>count<\/span>(e.empno), <\/span>0<\/span>) <\/span>as<\/span> num_emps,<\/span><\/span>\n nvl(<\/span>round<\/span>(<\/span>avg<\/span>(e.sal), <\/span>2<\/span>), <\/span>0<\/span>) <\/span>as<\/span> avg_sal<\/span><\/span>\n <\/span>from<\/span> dept d<\/span><\/span>\n <\/span>left join<\/span> emp e<\/span><\/span>\n <\/span>on<\/span> e.deptno = d.deptno<\/span><\/span>\n <\/span>group by<\/span> d.deptno, d.dname<\/span><\/span><\/code><\/pre><\/div>\n\n\n\nWe see that the subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
We see that the subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
subquery<\/code> part of the view has been preserved, except the first line which has a different indentation. Actually, the indentation of the first line is not stored in the data dictionary (see user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
user_views.text<\/code>). The two spaces are produced by the default pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
pretty<\/code> option of dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
dbms_metadata<\/code>. So far, so good.<\/p>\n\n\n\nIn many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
In many cases, the Oracle Data Dictionary explicitly stores default values. For instance, Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
Y<\/code> for editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
editionable<\/code> or USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
USING_NLS_COMP<\/code> for default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
default_collation<\/code>. This fact alone makes it impossible to reconstruct the original DDL reliably. The database simply does not know whether an optional clause such as editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
editionable<\/code> or default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
default collation<\/code> has been specified or omitted. Moreover, some optional DDL clauses such as or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
or replace<\/code> or force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
force<\/code> are simply not represented in the data dictionary.<\/p>\n\n\n\n\u2026 Especially with Quoted Identifiers<\/h2>\n\n\n\nAnd last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
And last but not least, identifiers such as columns names, table names or view names are stored without double quotes. Therefore, the database knows nothing about the use of double quotes in the original DDL. However, the database knows exactly when double quotes are required. As a result, dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
dbms_metadata<\/code> could emit only necessary double quotes. This would result in a more readable DDL and would probably also be more similar to the original DDL.<\/p>\n\n\n\nThe reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
The reality is that code generators such as dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
dbms_metadata<\/code> often use double quotes for all identifiers. It\u2019s simply easier for them, because this way the generated code works for all kind of strange identifiers.<\/p>\n\n\n\nHowever, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
However, using quoted identifiers is a bad practice<\/a>. It is, in fact, a very bad practice when they are used unnecessarily.<\/p>\n\n\n\nShaping the DDL<\/h2>\n\n\n\nSo what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
So what can we do? We can configure dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
dbms_metadata<\/code> to produce a DDL which is more similar to our original one. In this case we can change the following:<\/p>\n\n\n\n\nremove the schema of the view (owner)<\/li>\n\n\n\nremove the force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
force<\/code> keyword<\/li>\n\n\n\nremove the default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
default collation<\/code> clause<\/li>\n\n\n\nadd the missing SQL terminator (;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
;)<\/code><\/li>\n<\/ul>\n\n\n\nThis query<\/p>\n\n\n\n4) Get customized DDL<\/span><\/path><\/path><\/svg><\/span>with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>
This query<\/p>\n\n\n\n
with<\/span><\/span>\n <\/span>function<\/span> view_ddl(in_name <\/span>in<\/span> <\/span>varchar2<\/span>) <\/span>return<\/span> clob <\/span>is<\/span><\/span>\n l_main_handle <\/span>integer<\/span>;<\/span><\/span>\n l_modify_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl_handle <\/span>integer<\/span>;<\/span><\/span>\n l_ddl clob;<\/span><\/span>\n <\/span>begin<\/span><\/span>\n <\/span>-- initialize dbms_metadata for view based on current schema<\/span><\/span>\n l_main_handle := sys.dbms_metadata.<\/span>open<\/span>(<\/span>'VIEW'<\/span>