Creating a DDL script for an object without a schema name baked when using DBMS_METADATA.GET_DDL?

Question

How can I create a DDL script for my object with DBMS_METADATA.GET_DDL without the schema name baked in?

With DBMS_METADATA.GET_DDL :

 CREATE TABLE "MYSCHEMA"."MYTABLE" ( "COL1" NUMBER(10,0) ) 

An SQL developer can do this, and I think he also uses DBMS_METADATA to achieve this goal and common DDL scripts.

With SQL Developer:

 CREATE TABLE "MYTABLE" ( "COL1" NUMBER(10,0) ) 

Answer 1

Use SET_REMAP_PARAM with the REMAP_SCHEMA option:

 DBMS_METADATA.SET_REMAP_PARAM(th,'REMAP_SCHEMA','HR',NULL); 

This will map the HR schema to NULL (you will need a job descriptor); see metadata_api documentation for a complete example

Answer 2

I recently came across the following which allows you to get ddl without a schema name.

This looks a lot simpler than any other method I've seen so far, although it is not included in any Oracle documentation. I found this in the SQL Developer statement log, which generates ddl without a schema name.

 DBMS_METADATA.SET_TRANSFORM_PARAM(dbms_metadata.SESSION_TRANSFORM, 'EMIT_SCHEMA', false); 

You do not need to access descriptors or anything nasty just EXEC above before calling DBMS_METADATA.GET_DDL