Search code examples
oracledbms-metadata

Generating DDL script for object without schema name baked in using DBMS_METADATA.GET_DDL?

How can I generate the 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)
)

SQL Developer can do that, and I think it's also uses the DBMS_METADATA to achive this goal and generale DDL scripts.

With SQL Developer:

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

  • 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'll need a job handle, though); for a full example, see metadata_api documentation