How to get the DDL for Oracle object without extra characters

Question

When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script. For example, if I create this sample procedure:

CREATE OR REPLACE PROCEDURE TEST
IS 
BEGIN
 NULL;
END;
/ 

then dbms_metadata.get_ddl for this procedure returns extra newline and two space characters at very beginning:

select dbms_lob.substr(dbms_metadata.get_ddl('PROCEDURE','TEST', USER), 37, 1 ) text
, dump(dbms_lob.substr(dbms_metadata.get_ddl('PROCEDURE','TEST', USER), 37, 1 )) dump_text
FROM dual;

Result is:
  CREATE OR REPLACE PROCEDURE "TEST"                                            
Typ=1 Len=37: 10,32,32,67,82,69,65,84,69,32,79,82,32,82,69,80,76,65,67,69,32,80,
82,79,67,69,68,85,82,69,32,34,84,69,83,84,34

As you can see there are 10,32,32,, just before CREATE OR REPLACE. Any idea how to get rid of these whitespaces? Also there are extra " around procedure name.

Answer 1

Please try this:

set long 99999999
set pages 1000
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'PRETTY', true);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform, 'SQLTERMINATOR', true);
select dbms_metadata.get_ddl('<object_type>','<object_name>','<owner>') from dual;

Answer 2

The following will get rid of the LF and 2 spaces using a function though it would probably be better/easier to do it in your text editor.

select 
   REPLACE(dbms_lob.substr(
        dbms_metadata.get_ddl('FUNCTION','REMAP_SCHEMA', 'ETL'), 
      37, 1), CHR(10)||CHR(32)||CHR(32)||'CREATE','CREATE') text
   from dual

Obviously the first word that you are interested in keeping would need to be 'CREATE' for this to work. You could also use one of the REGEXP functions but I don't see that being any better/different to this method. I'm also not sure why you particularly need to get rid of them.

Answer 3

code as is, without any transformations or formatting:

  function getDDL(
      p_object_type varchar2
     ,p_object_owner varchar2
     ,p_object_name varchar2
  ) return clob is
      v_clob clob;
  begin     
      dbms_lob.createtemporary(
          lob_loc => v_clob
         ,cache   => true
         ,dur     => DBMS_LOB.CALL
      );
      for rec in (
          SELECT text
          FROM ALL_SOURCE
          WHERE NAME  = p_object_name
            AND OWNER = p_object_owner
            and type  = p_object_type
          order by line  
      ) loop
          DBMS_LOB.append(v_clob, rec.text);
      end loop;
      return v_clob;
  end getDDL;