Oracle – Configuring dbms_metadata.get_ddl() to Exclude Schema

oracle

Can I configure dbms_metadata.get_ddl() to leave off the schema name of generated objects?

I set these transform parameters

begin
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'PRETTY',true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SEGMENT_ATTRIBUTES',false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'STORAGE',false);
    dbms_metadata.set_transform_param(dbms_metadata.session_transform,'TABLESPACE',false);
end;

and execute this query

select to_char(dbms_metadata.get_ddl('TABLE','FOO')) from dual;

and receive this ddl for my table. I would like to have just "FOO" rather than "MH"."FOO".

CREATE TABLE "MH"."FOO"                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
(   "X" NUMBER                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  
) ;

Best Answer

You'll find a way of doing this in an answer to Generating DDL script for object without schema name baked in using DBMS_METADATA.GET_DDL? on Stack Overflow: use the EMIT_SCHEMA transform:

dbms_metadata.set_transform_param(
      dbms_metadata.session_transform, 'EMIT_SCHEMA', false);

This transform is, as far as I can tell, undocumented. (Probably exists at least down to 9.2.) You can find it mentioned in two support documents:

Related Question