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:


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,

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.

  • 2
    The double quotes are there to allow for the possibility that you created the procedure using a reserved word or containing a non-standard character. – BriteSponge May 10 '16 at 10:22
  • Looking at the comments below and here, I think that you may be trying to do something in a slightly backwards fashion. Can you explain what it is you want to achieve, then someone may be able to provide a better way to manage it. – Phil Sumner May 11 '16 at 11:57
  • I am guessing you may not be able to directly get rid of those 3 characters and double-quotes easily. These probably get added when xml to ddl transformation happens (dbms_metadata first generates xml and transforms it to ddl or sxml if requested). Because if you use dbms_metadata.get_xml, you don't see those characters, but you see them for get_ddl(). a substr(...get_ddl(),4) could fix that. You have to be careful about double quotes though. – Raj May 18 '16 at 18:44

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;
| improve this answer | |

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.

        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.

| improve this answer | |
  • I need to get exactly the same source code as it was created from. It will be compared semi-automatically externally for modifications, source versioning etc. I have the same thing for SQL Server and a lot of code. In Oracle, there would work user_source, but I look for solution with dbms_metadata. – LiborStefek May 10 '16 at 11:27
  • You may need to get the first line using the above but wrap it in another REPLACE that replaces q'["]' with NULL. – BriteSponge May 10 '16 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.